Access import from Excel , DoCmd.TransferSpreadsheet acImport

0
votes

I create some data from SAP-BI server and it send me an Excel-2013 file, I then import this excelfile into Access-2013 for further processing.

I located the string in VBA code which import the data, it looks like this:

DoCmd.TransferSpreadsheet acImport, , "Ttable", fileName, True, "abclist$"

The table in access is named Ttable The excel spreadsheet is named abclist

So far the current process has been working good. However lately the amount of data have increased to more than 1.04 million raws (which seems to be the max number of raws excel can handle in one spreadsheet) and SAP-BI is then exporting the remaining data in a second spreadsheet which is named abclist(1)

How do I change the VBA code to dynamically allow both spreadsheets if there are two spreadsheets, and it will continue to work if there is only one spreadsheet in the excelfile?

2
vbams-access-2013excel-2013
Does this answer your question? How to import one excel file that contain multiple worksheets into an access table by vba – Storax

2 Answers

1
votes

Open the excel file, import the sheet name as an array and execute the command.

Dim App As Object
Dim vFn() As Variant, v As Variant
Dim Wb As Object, Ws As Object
Dim n As Integer

Set App = CreateObject("Excel.Application")
Set Wb = App.workbooks.Open(FileName)
For Each Ws In Wb.worksheets
    n = n + 1
    ReDim Preserve vFn(1 To n)
    vFn(n) = Ws.Name & "$"
Next Ws
Wb.Close (0)
Set Wb = Nothing
App.Quit
Set App = Nothing

For Each v In vFn
    DoCmd.TransferSpreadsheet acImport, , "Ttable", FileName, True, v
Next v
0
votes

I want to import excel data into access. Normally there are two spreadsheet "AAA" and BBB" These spreadsheets will be inported in two tables in Access (name tAAA and tBBB).

But but sometimes if data amount is big, there will be a 3:rd spreadsheet name "AAA(1)"

How to ignore importing 3:rd spreadsheet name AAA(1) if it does not exist? If it is missing, I get error message.

DoCmd.TransferSpreadsheet acImport, , "tAAA", fileName, True, "AAA$"
DoCmd.TransferSpreadsheet acImport, , "tAAA", fileName, True, "AAA(1)$"
DoCmd.TransferSpreadsheet acImport, , "tBBBs", fileName, True, "BBB$"