I'm guessin you're running this from within access. To use the:
..Cells(.Rows.Count, "A").End(xlUp)
you'd need to automate excel from wthin access (can be done without
any issues) but would running a delete (WHERE MyField IS NULL) query
after the import directly in access too much of an inefficiency?
i.e., the way i see it you can still pull everything in and then get
rid of what you don't need (the blanks).
Perhaps it'd be more efficient not to import the blanks to begin with
but then I wouldn't know how to do that without opening up excel and
automating Excel from within Access. Perhaps you'd connect to the
excel as a recordset as oppose to via acImport.

On Oct 6, 12:30*pm, Andy wrote:
Hiya,

I've modified the code below to import data from a pre-defined folder.
The data is split into different new tables depending on the sheet
name.

The code works fine as it is but with one problem: it also uploads a
lot of blank cells from the excel files, bumping what should be a 1000
odd row import up to well over 10000.

I have attempted to amend the code to select only the used cells but
with no success.
I realise it is probably using .Cells(.Rows.Count, "A").End(xlUp) in
some format but I cannot seem to fit it all in effectively.

Any help is appreciated - Thanks!

Sub Import()
Dim strPathFile As String, strFile As String, strPath As String
Dim blnHasFieldNames As Boolean
Dim intWorksheets As Integer

Dim strWorksheets(1 To 2) As String

Dim strTables(1 To 2) As String

strWorksheets(1) = "Airport"
strWorksheets(2) = "Maritime"

strTables(1) = "tblTempAirport"
strTables(2) = "tblTempMaritime"

blnHasFieldNames = True

strPath = "F:\APRD SHARED FOLDER\Performance\"

For intWorksheets = 1 To 2

* * * strFile = Dir(strPath & "*.xls")
* * * Do While Len(strFile) 0
* * * * * * strPathFile = strPath & strFile
* * * * * * DoCmd.TransferSpreadsheet acImport, _
* * * * * * * * * acSpreadsheetTypeExcel9, strTables(intWorksheets), _
* * * * * * * * * strPathFile, blnHasFieldNames, _
* * * * * * * * * strWorksheets(intWorksheets) & "$"
* * * * * * strFile = Dir()
* * * Loop

Next intWorksheets
End Sub