Extract data from closed workbook using VBA with data range unknown at start!

[agarwaldvk]

HI Everybody

I am trying to retrieve data from closed workbook.

This is what I normally do. In all these cases, the data to be extracted is known - in the sense that the number of records and number of fields to be extracted are known before the extraction is carried out. In this situation, the following lines of code (with help from various sources on the net) does just that.


sheetAndRangeAddress = "[" & sourceBookSheetName & "$" & thisRangeAddress & "];"
sFilename = Left(currentDir, Len(currentDir) - 3) & foundFilename
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFilename & ";" & _
"Extended Properties='Excel 8.0;HDR=No'"
Set rs = New ADODB.Recordset
sSQL = "SELECT * FROM " & sheetAndRangeAddress
rs.Open sSQL, sConnect, adOpenStatic, adLockReadOnly, adCmdText


This works fine in the above situation.

However, when the total number of records (rows) and the total number of columns (fields) in the worksheet in this closed workbook is not known at the start of this process, is there a way to extract 'ALL' the data that is on that workbook - without opening the workbook.

It may be assumed that this workbook will have only one worksheet in it or in case there are more than one, the name of the worksheet which has the required data is known.


Best regards



Deepak Agarwal

[lucas]

Do you wish to extract the contents of the data in the closed workbook and put it in an existing sheet or do you just wish to import the sheet in whole? I think you are opening the workbook to do this it just closes it after extraction.