Transfer Spreadsheet Testing Available Excel Worksheets

[Michael]

I have 2 Excel Files that are emailed daily that will begin with 1 Worksheet
at the beggining of the month and as more data piles on, the Excel file will
increase to as much as 3 worksheets per file by the end of the month.

So, I can have 2 Excel Files with as much as 3 tabs per file. The question
is that I need to import these files into a table but I get an obvious error
when Sheet2 does not exist, How can import the data on the tabs that do exist
without actually opening the file and changing the macro?

Thanks in advance!

 

[Dirk Goldgar]

I have 2 Excel Files that are emailed daily that will begin with 1
Worksheet
at the beggining of the month and as more data piles on, the Excel file
will
increase to as much as 3 worksheets per file by the end of the month.

So, I can have 2 Excel Files with as much as 3 tabs per file. The
question
is that I need to import these files into a table but I get an obvious
error
when Sheet2 does not exist, How can import the data on the tabs that do
exist
without actually opening the file and changing the macro?

One way would be to just import each sheet, and trap and ignore the error
that is raised when a sheet doesn't exist. For example:

'----- start of example code -----
Sub ImportExcel()

On Error GoTo Err_Handler

Const conErrInvalidSheet = 3125
Dim strImportFile As String
Dim strTargetTable As String

strImportFile = "C:\Your Folder\YourFile.xls"
strTargetTable = "tblYourTable"

DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, _
strTargetTable, strImportFile, True, "Sheet1$"

DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, _
strTargetTable, strImportFile, True, "Sheet2$"

DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, _
strTargetTable, strImportFile, True, "Sheet2$"

Exit_Point:
Exit Sub

Err_Handler:
If Err.Number = conErrInvalidSheet Then
Resume Next
Else
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point
End If

End Sub
'----- end of example code -----

 

[Dirk Goldgar]

I have 2 Excel Files that are emailed daily that will begin with 1
Worksheet
at the beggining of the month and as more data piles on, the Excel file
will
increase to as much as 3 worksheets per file by the end of the month.

So, I can have 2 Excel Files with as much as 3 tabs per file. The
question
is that I need to import these files into a table but I get an obvious
error
when Sheet2 does not exist, How can import the data on the tabs that do
exist
without actually opening the file and changing the macro?

One way would be to just import each sheet, and trap and ignore the error
that is raised when a sheet doesn't exist. For example:

'----- start of example code -----
Sub ImportExcel()

On Error GoTo Err_Handler

Const conErrInvalidSheet = 3125
Dim strImportFile As String
Dim strTargetTable As String

strImportFile = "C:\Your Folder\YourFile.xls"
strTargetTable = "tblYourTable"

DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, _
strTargetTable, strImportFile, True, "Sheet1$"

DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, _
strTargetTable, strImportFile, True, "Sheet2$"

DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, _
strTargetTable, strImportFile, True, "Sheet2$"

Exit_Point:
Exit Sub

Err_Handler:
If Err.Number = conErrInvalidSheet Then
Resume Next
Else
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point
End If

End Sub
'----- end of example code -----

 

[Michael]

Dirk...

So far so good. I did run into a problem. I do not want the first row of
the excel file to be translated as the header when transfered into the
database. What happens is that the value on row 1 of the spreadsheet differs
from tab to tab on the excel file thus generating an error now that can not
append the second tab due to header conflict. Does that make sense? If not,
please let me know!

Thank You!

 

[Michael]

Dirk...

So far so good. I did run into a problem. I do not want the first row of
the excel file to be translated as the header when transfered into the
database. What happens is that the value on row 1 of the spreadsheet differs
from tab to tab on the excel file thus generating an error now that can not
append the second tab due to header conflict. Does that make sense? If not,
please let me know!

Thank You!

 

[Michael]

Dirk,

Never Mind..... Changed the 'True' to 'False' to avoid loading the 1st row.
Your suggestion overall was a success. Thank You!

 

[Michael]

Dirk,

Never Mind..... Changed the 'True' to 'False' to avoid loading the 1st row.
Your suggestion overall was a success. Thank You!

 

[Ken Snell MVP]

Additional info:

Import Data from Specific Worksheets in All EXCEL Files in a single Folder
via TransferSpreadsheet
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#ImpFldWrkFiles


Import Data from All Worksheets in a single EXCEL File into One Table via
TransferSpreadsheet
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#ImpAllWkshts


Import Data from All Worksheets in a single EXCEL File into Separate Tables
via TransferSpreadsheet
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#ImpAllWktsSepTbl

 

[Ken Snell MVP]

Additional info:

Import Data from Specific Worksheets in All EXCEL Files in a single Folder
via TransferSpreadsheet
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#ImpFldWrkFiles


Import Data from All Worksheets in a single EXCEL File into One Table via
TransferSpreadsheet
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#ImpAllWkshts


Import Data from All Worksheets in a single EXCEL File into Separate Tables
via TransferSpreadsheet
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#ImpAllWktsSepTbl