Inport excel file with several different tabs 1

[puforee]

I would like to import an Excel Workbook containing multiple dissimilar tabs. I am using office 2013. I have imported excel sheets before using a saved import wizard. But, only for an individual sheet. I could build multiple saved imports but can't because the makeup of the excel file and tabs is dynamic. Imports will happen several times a month.

Situation:
Excel document with multiple tabs (number of tabs not constant)
Each Tab can have different headers and data.
Each tab has a name (Not Sheet1, sheet2, etc)

The excel file name is constant.


Desire:
MS Access searches the excel file and imports data into separate tables based on the excel tab names.

All the tables, if present in Access, will be over written every time the spreadsheet is imported. Of course, some new tables may be created if more tabs are added.

In advance....thanks,

After the data is imported to "x" number of tables I will build code to analyze each and disburse the data.

 

[puforee]

I found a way but it is not elegant. Is there something better I hope?

Dim xlApp As Object
Dim S As Integer
Dim strFileName As String

strFileName = "\\nw\data\Maint_Train\MT Administration\DB\2017_BinaryKey_Log_Microsim_Master.xlsx"
Set xlApp = CreateObject("Excel.Application")

With xlApp
    .Workbooks.Open FileName:=strFileName
    '.Visible = False  'Default is False, isn't it?
    For S = 1 To .sheets.Count
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
              .sheets(S).Name & "_Import", strFileName, True, _
              .sheets(S).Name & "!"
        DoCmd.DeleteObject acTable, .sheets(S).Name & "_Import"
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
              .sheets(S).Name & "_Import", strFileName, True, _
              .sheets(S).Name & "!"
      
    Next S
    '.Close
    .Quit
End With
Set xlApp = Nothing

 

[Andrzejek]

Try something like this:

With xlApp
    .Workbooks.Open FileName:= strFileName
    For S = 1 To .Sheets.Count[blue]
        DoCmd.RunSQL  "Delete from " & .Sheets(S).Name & "_Import"
[/blue]
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
              .Sheets(S).Name & "_Import", strFileName, True, _
              .Sheets(S).Name & "!"
    Next S
End With

You may want to wrap all of this in some kind of Error Handler in case a table does not exist that you want to delete the data from.

Or just get rid of first:[tt]
DoCmd.TransferSpreadsheet [/tt] line

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[puforee]

Won't this still throw an error if the SQL does not find the table?

 

[Andrzejek]

Yes, it will error, that's why I said: "You may want to wrap all of this in some kind of Error Handler in case a table does not exist that you want to delete the data from."

Have fun.

---- Andy

There is a great need for a sarcasm font.