I am having a bit of trouble importing an Excel file. The file has dates which are converted into a string of numbers when they are imported into my table. Whenever I tried to change the field type to Date/Time I would lose all the data within that column. I also tried saving the spreadsheet into a CSV file before importing into Access which seems to work out, but I need to use code to make the process less operator dependent. This is what I would like to do:
1. from Access call up an API dialog box to allow for the user to navigate to the spreadsheet location. (This can vary)
2. Import the spreadsheet data deleting all empty rows and columns. Currently I'm doing this manually in the table.
3. Covert the spreadsheet to a CSV format
4. Import into an Access fixed table name (deleting any existing data within the table).
NOTE: the only reason for the CSV conversion is so that the date data will come into the table as a date and not a number. If the importing process can perform the input without the CSV conversion then this would be one less step to perform.
Attached is an example of the spreadsheet that I have been working with.
Any ideas?
1. from Access call up an API dialog box to allow for the user to navigate to the spreadsheet location. (This can vary)
2. Import the spreadsheet data deleting all empty rows and columns. Currently I'm doing this manually in the table.
3. Covert the spreadsheet to a CSV format
4. Import into an Access fixed table name (deleting any existing data within the table).
NOTE: the only reason for the CSV conversion is so that the date data will come into the table as a date and not a number. If the importing process can perform the input without the CSV conversion then this would be one less step to perform.
Attached is an example of the spreadsheet that I have been working with.
Any ideas?