Import Excel to Access (used range only)

  • Thread starter Thread starter Andy
  • Start date Start date
A

Andy

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
 
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.
 
Yes, the code is within Access.

Having a massive amount of blanks isn't an issue as I intend to append
the data to another table based on a unique ID. The main problem is
the speed at which it uploads, especially when we need to upload many
files at once.

You've given me an idea though - I may see if I can get rid of any
blank cells under the data in Excel that access may be wrongly
perceiving as non blank, before I hit the import button on Access.

I'll keep on testing - thanks.
 
Back
Top