Access VBA Strategy for Testing Existence of Excel Worksheet
Posted on 2014-01-01
Hello ~ Importing data from an Excel worksheet to an Access table using:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tblRealOne", sFile, True, "Worksheet1$"
NOTE: sFile is a string containing the path and file name of the workbook containing data.
However, IF "Worksheet1" is not present, Error #3125 results and the function stops.
Because contents of tblRealOne are deleted BEFORE running the TransferSpreadsheet statement, I need a way to determine if the TransferSpreadsheet will work BEFORE deleting tblRealOne contents.
Right now, I create a tblTest w/ the same TransferSpreadsheet statement to see if it runs, BEFORE deleting existing data and running TransferSpreadsheet on tblRealOne....... (deleting tblTest afterwards).
This strategy works but seems extremely clumsy.
My question: Is there a way to determine if "Worksheet1" worksheet exists prior to running the TransferSpreadsheet statement? - Without opening the spreadsheet file?
Is there a way of testing for an error in the TransferSpreadsheet statement, without actually moving data or creating a table???
I'll be offline shortly but will resume tomorrow.
Thank You! Jacob