From inside Access, I need to import an Excel spreadsheet to Access daily. There are reasons that I cannot use the TransferSpreadsheet in a macro. I would like to do it in VBA code. Once the data is in Access in the temp table, I will use queries to manipulate it into a final format and move some of the data to a different table.
This is what I need help figuring out how to do:
1. I need to ask the user for the spreadsheet file name and location (ie I need to browse for the location and file). The name is different every day. It will always import the first sheet although the name of that sheet changes.
2. I need to import specific non-contiguous columns. If this is not possible, and its looks like it might not be, then I need to import the entire sheet and then delete the fields in the Access table that I don't need.
3. While importing, I need to specify the data format of each field coming in. If possible, I do not want to build a temp database that I use everyday and empty, although I can do this if it is best.
4. While importing, Access should assign a unique id number for each record imported.
I would appreciate all the help I can get. I have been programming for years but I have limited VBA experience, mostly just data validation and manipulation. I have never tried to import with VBA before.