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.
Code: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?