Importing Only Excel Cells With Data

[DALIEN51]

Can anyone help with this?

I will routinely be sent a Excel 2000 spreadsheet with multiple worksheets and each worksheets data is to be imported into a related named table in a MS Access 2000 database. No of course I have been using Transfer Spreadsheet to perform the imports but to make sure I get all of the data I use the range A1:AZ65536 (65536 of course being the maximum number of rows available in a Excel 2000 spreadsheet).

What I would like to do however, is just import the necessary number of rows not all 65536!!! Is there a way in code of working out how many rows in the A-AZ column range contain data?

It occured to me that if I linked a table to each worksheet then this would only display the necessary rows and I could count them however, once you have specified the spreadsheet location the code doesnt let me repoint the individual worksheet unless someone knows how to do this???

Any help most appreciated.

DALIEN51

 

[Bat17]

Assumming that column A contains data and has no blank cells before the end of the data then you could do something like this to find the number of rows used.

Function GetXlRows(filepath, Filename, sheetname As String)
Dim xlApp As excel.Application
Set xlApp = New excel.Application
GetXlRows = xlApp.ExecuteExcel4Macro("counta('" & filepath & "\[" & Filename & "]" & sheetname & "'!r1c1:r65536c1)")
Set xlApp = Nothing
End Function

Private Sub getX()
Dim filepath As String
Dim Filename As String
Dim sheetname As String
Dim lngRows As Long

filepath = "C:"
Filename = "myFile.xls"  '<==this could change in a loop
sheetname = "sheet1"    '<==this could change in a loop
lngRows = GetXlRows(filepath, Filename, sheetname)
Debug.Print lngRows
End Sub

HTH

Peter

Edit to correct max pos rows.

 

[neileg]

If you link the sheets and run an append query, it will only append the non-null records. Surely this is easier?