Retrieve value from closed file (xl2000)

Topic

New Reply

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 VBA function to get a value from a closed file 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

Reply | Quote

Replies

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

Reply | Quote

Wassim,

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

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

' 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
        .Open cnct$

I am not too very familiar wtih ADO and am at a loss. 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:

Reply | Quote

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 here – we all have a learning curve and you’re not alone!

Reply | Quote

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…

Reply | Quote

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…….

Reply | Quote

YEHAW!!!

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. Then I tweaked a bunch of different examples that I found and came up with this:

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

It may not be perdy but it WORKS!

Reply | Quote