Originally Posted by MajP
I am not familiar with SAP so no idea if there is a smarter direct way. From what I understan you download a report into Excel. I am going to assume that this excel is not always in the same location once downloaed. The report needs some level of cleaning before the records can be appended to existing table/s. Currently you are actually pasting rows from the report into an existing table/s. I am going to assume that all data from the excel goes into a single table and you do not have to create information into other related tables. You would like to users to be able to do this with some automation.
My guess on how I would do this.
1) Provide a command button to launch the file browser to find your SAP Excel Report
2) This will import into either a linked table or Temp Table. Preferably just a link is created.
3) Then run some queries to do the house cleaning or if needed do with a recordset. Rename columns if necessary; remove redundancy, clean-up names, etc.
4) Run the append query on the cleaned up data or if needed add records through a recordset.
5) Show some confirmation of what has been appended and allow the user to review those records in case any final cleanup is needed.
So all of this should happen from a button click. As long as there are good primary keys and or indices there should be no problem if records have already been updated you cannot append them again.
Yes you are exactly correct. Though, now that I see it laid out like that, it is still probably easiest for the user to just paste the table in there. Otherwise, when they download the file, they would then have to navigate to even figure out where the file is and then we have all of the cleaning of the spreadsheet anyways. At this point, it may just be fastest to paste the data in. Thanks again.