How to import an excel file and add new fields if they don't currently exist?
Posted on 2014-01-20
Okay, here's the situation. I have a database application that will eventually be copied and distributed to end users to run a set of reports. The database requires 2 files to be imported on a regular basis. One of which is from an excel file that will be managed by one person and imported by that one person since it will be maintain in a secure folder on the network. I setup some VBA code in the excel sheet to copy and format the relevant data into an export tab in the excel file. Then I built a form with a button to import the excel sheet buy browsing to the file (file paths could change without my knowledge, so I can't use any static paths in the programming) and importing it using the TransferSpreadsheet function. This works great, however, I run into a potential problem when the spreadsheet manger needs to add a new column for a new project. The user will receive run-time error 2391 if someone doesn't go in and manually add the new column to the table. I don't want anyone to have to manually add anything to the tables. I need access to create a new field for any fields that don't already exists when the file is imported dynamically. I've read a couple different methods for doing this, including creating a temp table to import the file to and then appending it to the existing table, but I haven't been able to get that to work. I would appreciate any help.