Situation:
My client gets as receipt Excel files with data in 2 columns, 1 headers and 1 detail. I wish to load the detail into 1 Access table. I created a macro to flip the data so that I have 2 rows. The user can easily run this macro and save it before importing the spreadsheet.
Problem:
The Import Spreadsheet Wizard does not like some of the column headings so I get the "The search key was not found in any record." when trying to import the data. I can't have the user changing the headers before importing these files each time. Is there a way to build an import procedure without the Wizard?
Problem2:
Still using the Import Spreadsheet Wizard I opt to not use the header in row 1. I successfully import 2 rows which I can work with. So I'm now trying to import a second file and want to append to the first file. The Import Spreadsheet Wizard is auto-selecting the first row to contain column headings. This does not work. Is there a way to build an import procedure to append without the Wizard?
Problem3:
So I created tblClaim to temporarily hold the 2 rows from the first Excel sheet. I saved the Import so the user can import new claims as they come in. I try to run the import task with the same file and I get "The specification failed to execute. Try re-creating the specification."
I deleted the table but I still get the same message. I can't think of a work-around for this one. The user would rather not manually enter this information.
Current Details:
Data entered into system saved as receipts in Excel sheets named LastNameDate
Excel Spreadsheet with all claims is maintained using most of data from receipt files.
Creating multi-purpose app that includes the all claims spreadsheet.
Need to load the receipt files into tblClaims.
Need to transfer tblClaims data into WorkSafeClaim table.
I am willing to manually build a macro to import this data. I just need a skeleton or the URL to some instructions.
Thanks
My client gets as receipt Excel files with data in 2 columns, 1 headers and 1 detail. I wish to load the detail into 1 Access table. I created a macro to flip the data so that I have 2 rows. The user can easily run this macro and save it before importing the spreadsheet.
Problem:
The Import Spreadsheet Wizard does not like some of the column headings so I get the "The search key was not found in any record." when trying to import the data. I can't have the user changing the headers before importing these files each time. Is there a way to build an import procedure without the Wizard?
Problem2:
Still using the Import Spreadsheet Wizard I opt to not use the header in row 1. I successfully import 2 rows which I can work with. So I'm now trying to import a second file and want to append to the first file. The Import Spreadsheet Wizard is auto-selecting the first row to contain column headings. This does not work. Is there a way to build an import procedure to append without the Wizard?
Problem3:
So I created tblClaim to temporarily hold the 2 rows from the first Excel sheet. I saved the Import so the user can import new claims as they come in. I try to run the import task with the same file and I get "The specification failed to execute. Try re-creating the specification."
I deleted the table but I still get the same message. I can't think of a work-around for this one. The user would rather not manually enter this information.
Current Details:
Data entered into system saved as receipts in Excel sheets named LastNameDate
Excel Spreadsheet with all claims is maintained using most of data from receipt files.
Creating multi-purpose app that includes the all claims spreadsheet.
Need to load the receipt files into tblClaims.
Need to transfer tblClaims data into WorkSafeClaim table.
I am willing to manually build a macro to import this data. I just need a skeleton or the URL to some instructions.
Thanks
