Avatar of Chi Is Current
Chi Is Current
Flag for United States of America

asked on 

Access VBA Strategy for Testing Existence of Excel Worksheet

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.

Works perfectly!

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?
OR
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
Microsoft Access

Avatar of undefined
Last Comment
Chi Is Current

8/22/2022 - Mon