Excel Import - Check if Spreadsheet Exists

[kagoodwin13]

I have a database that is importing tables from an Excel form to Access. I've had the programming in place to support the current form for almost a year now, and it works great.

strPathFile = Me.txtFILE_PATH

strTable = "tblEXCEL_TAB1"
Sheet_Name = "EXCEL_TAB1" & "!"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable, strPathFile, blnHasFieldNames, Sheet_Name

strTable = "tblEXCEL_TAB2"
Sheet_Name = "EXCEL_TAB2" & "!"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable, strPathFile, blnHasFieldNames, Sheet_Name

etc.

The Excel form has changed though, so I am stuck with a transition inconsistency. The new form will have one more worksheet, and thus one more Access table to store the data, than the old form. The old form is already in widespread distribution among hundreds of people, both inside and outside my company, so I must be able to have the database support both the old form and the new form. (I can't wait to get this form into an online system - no more of this stuff!)

The method for handling this change seems to be:

• Keep the existing code (nothing about it is changing)
• Add an if statement to check if the new worksheet is in the file
• If yes, import the new worksheet, then run code in current state
• Else, run code in current state

All of the worksheet names in Excel will stay constant, so I can pinpoint Access to look for the "EXCEL_TABn" worksheet.

I've seen lots of code that can support checking for a worksheet within Excel itself, but not much of anything of implementing this code from Access.

Any ideas?

[rpeare]

a very cheap way to get around this without having to perform the check is to do something like:

On Error Goto ERRHANDLER
.... existing code...
strTable = "tblEXCEL_TAB3" 
Sheet_Name = "EXCEL_TAB3" & "!" 
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable, strPathFile, blnHasFieldNames, Sheet_Name

... existing code...
exit sub
ERRHANDLER:
resume.next
end sub

Then if it encounters an error it just keeps going with the code
If you trap the error number you can also just include that specific error
you can capture the error in the ERRHANDLER section with

debug.print err.number & err.description

then use something like:

On Error Goto ERRHANDLER
.... existing code...
strTable = "tblEXCEL_TAB3" 
Sheet_Name = "EXCEL_TAB3" & "!" 
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable, strPathFile, blnHasFieldNames, Sheet_Name

... existing code...
exit sub
ERRHANDLER:
if err.number = <insert your error number for a failed link> then
    resume.next
else
    debug.print err.number & " " &  err.description
endif
end sub

[lfpm062010]

You can also check to see if the file exists and decide what to do.

http://allenbrowne.com/func-11.html

[kagoodwin13]

rpeare, your code worked, thank you very much!

strPathFile = Me.txtFILE_PATH

strTable = "tblEXCEL_TAB1"
Sheet_Name = "EXCEL_TAB1" & "!"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable, strPathFile, blnHasFieldNames, Sheet_Name


strTable = "tblEXCEL_TAB2"
Sheet_Name = "EXCEL_TAB2" & "!"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable, strPathFile, blnHasFieldNames, Sheet_Name

On Error Goto ERRHANDLER
strTable = "tblEXCEL_TAB3"
Sheet_Name = "EXCEL_TAB3" & "!"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable, strPathFile, blnHasFieldNames, Sheet_Name

Exit Sub

ERRHANDLER:
If Err.Number = 3125 Then
     Resume Next
Else
     Debug.Print Err.Number & " " & Err.Description
End If

End Sub