Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Retrieve value from closed file (xl2000)

    We have to import data from several workbooks into one for calculation purposes. This was done manually by opening multiple workbooks and coping & pasting into one "master" workbook. These files are very large and if too many were open, Excel would lock up. Using VBA the various files are opened one at a time and the data is copied from them into the "master" file.

    I found a <A target="_blank" HREF=http://j-walk.com/ss/excel/tips/tip82.htm>VBA function to get a value from a closed file </A> on John Walkenbachs' site. This is a pretty slick function for small amounts of data. It works like a charm, but it is fairly slow when importing test data (12 columns, 100 rows). It would take days to populate the "master" file with all our required data (14 different sheets - 1 column of 4400 rows from each sheet). Have any of you any ideas on how to increase the speed of this function or to accomplish the same thing in VBA?

    The current procedure we are using works just fine. This just seems like a great idea, importing data without having to open a workbook. According to Mr. Walkenbach, "VBA does not include a method to retrieve a value from a closed file." Attention Microsoft - Why was this function possible in an ancient version of Excel and not in VBA?


    I appreciate all the assistance, information and direction that this board and all the individuals who participate give.

    Thanks in advance,
    Brent

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Retrieve value from closed file (xl2000)

    Brent

    Have your looked into what ODBC, DAO, RDO, ADO can do for you?

    I think you can have a workbook with a whole bunch of formulas like this one:

    =[Book1]Sheet1!$A$1

    Now Book1 can have the path to its location as well, and this will mean that it is closed.

    Now you will, via VBA, replace all the = (equal) signs in these formulas to a strange string that you will not anticipate to be used in real life situations. Something like zzzXXXzzz.

    This will render the formulas in the cells as string, and calculation will stop.

    Again via VBA, you start systematically changing that strange string to an equal sign and calculate the range you just converted into a formula, save the results as values, to speed up the system, and surely you get all your values from all your closed workbooks into the new master workbook.

    This may not be the fastest way or the easiest way, but it is quite accurate.

    You have to worry about the workbooks being in the same location all the time, otherwise Excel will give you errors if it could not find what it needs.

    You also may have some issues if you have dependencies, you need to calculate one cell before the other that type of situation.

    Good luck, let us know how it works and may the Force be with you.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieve value from closed file (xl2000)

    Wassim,

    I have been working on this in between meetings and have not made too much progress. This is the code I am running:

    <pre>Sub TestADORetrieval()
    GetData "C:WINDOWSDesktopWorkADO test.xls", "Missed Sch Adj", & _
    "AL4missADJ", Sheet1.Cells(1, 4), False
    End Sub
    </pre>

    <pre>' reference set to Microsoft ActiveX Data Objects 2.1 Library
    Private Sub GetData(SrcFile$, SrcSheet$, SrcRange$, rTgt As Range, fHdr As Boolean)
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim a&
    Dim cnct$


    ' Initialize a variable for the connection string
    cnct$ = "Driver={Microsoft Excel Driver(*.xls)};DBQ=" & SrcFile$

    ' Initialize connection object
    Set cn = New ADODB.Connection

    With cn
    ' Open the database connection
    <font color=red>.Open cnct$</font color=red>
    </pre>


    I am not too very familiar wtih ADO and am at a loss. <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23> I have tried inserting ".Provider=MSDASQL;" in the connection string ahead of the driver arg, tried to create a DSN and have accomplished nothing. When the line in red runs I get this error message:
    Attached Images Attached Images

  4. #4
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieve value from closed file (xl2000)

    In the abscence of better advice, can you reset the reference to Microsoft ActiveX Data Objects 2.5 Library? That is what I'm using on 2k. If you can, what happens then?

    Also, try looking <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=xl&Number=83025&page=&view= &sb=&o=&vc=1>here</A> - we all have a learning curve and you're not alone!

  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieve value from closed file (xl2000)

    Thanks for the response and the link. I don't have 2.5 on my rig though. I have found some information on a couple of sites that I am going to review...
    <img src=/S/read.gif border=0 alt=read width=19 height=33>

  6. #6
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieve value from closed file (xl2000)

    I have tried at least 5 different routines that I found on the net and in books and I keep getting the same error.
    I searched for and located the odbcjt.32.dll on my machine.
    I have the driver and the file name / path was copied from the properties window.

    ayyeeeeeeeeeee I cant take it anymore....... <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

  7. #7
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieve value from closed file (xl2000)

    YEHAW!!! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    Thanks to Wassim and Brook for your suggestions. I hacked and searched and hacked some more. Ripped hair out and searched and read and hacked till I was sure there was no way out (damn newbies). Then I tweaked a bunch of different examples that I found and came up with this:

    <pre>Sub SampleADO()
    Dim cnConn As ADODB.Connection
    Dim rsRecd As ADODB.Recordset
    Dim rgTargetCell As Range
    Dim stConn As String
    Dim sADOCopyTest As String

    sADOCopyTest = "C:WINDOWSDesktopWorkADO test.xls"

    stConn = "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=" & sADOCopyTest
    Set cnConn = New ADODB.Connection

    cnConn.Open stConn
    Set rsRecd = cnConn.Execute("[TestRange]")
    Set rgTargetCell = ActiveCell

    rgTargetCell.CopyFromRecordset rsRecd

    rsRecd.Close
    cnConn.Close
    Set rsRecd = Nothing
    Set cnConn = Nothing
    End Sub
    </pre>


    It may not be perdy but it WORKS!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •