I was asking because I was going to try and convert the Excel process to an Access dB. But if the manager can change the layout of the spreadsheet that he/she used to report the Job/musicians, it gets more difficult.
I haven't done a lot of Access automation to control Excel for the past few years and I haven't had to write code in Excel for about 20 years. But after 3.5 hrs (more or less) I finally have a sub in Excel that uses ADO to write the Excel data to an Access dB.
However I think in your first post, you asked how to write data from Excel to Access so you could search for job data easier.
When you open the IDE in Excel, you will see "Module1". Open the Module and change line 18
Code:
'EDIT the path to your path where the Access dB is located *******************************************
dBPath = "D:\Documents\Orchestra\Musician Payments\FY ending 2021\Access_PaymentFileTEST.accdb;"
to the path to the Access dB. Also change the name of the Access dB if you change the dB name.
I added a button on the worksheet (next to the Run Discrepancy Checker button).
Check the References - the ones I have checked are:
Microsoft Access xx.0 Object Library
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 6.1 Library
plus the Libraries you need -
Outlook,
Forms 2.0,
Microsoft Office xx.0 Object Library .
Also, I did make a few changes to the code in the "ThisWorkbook" module.
Anyway, if you have questions/problems, post back.
This has been interesting. Make me think about Excel programming. Took a while for it to start coming back - but I learned a lot.
In the Access table, I did not create fields for all of the columns in Excel. You should use a query to do the calculations like "Total net", "VAT" and "Total" (Columns Z, AA and AB)