Updating Values in Excel File from Access After Query

[Breezer23]

Good morning,
I appreciate all the help you guys (and gals) have provided. This forum has been a huge help for me. However, I keep running into issues due to my lack of knowledge/experience in Access (which again, getting better with this forums help).



Anyway, the issue: I have a linked table in Access (Excel Database - working file). Once someone has been deemed a record as "final" (In the Excel working file) I run an append query which will append anything with a "final" status in Excel to a final table within Access. Afterwords, I run a quick check to ensure the data was transferred correctly. What I need help with is figuring out how to then, assuming the data transferred correctly, go back into the Excel file via Access (I would assume using VBA), change all of those fields marked as "Final" to "Uploaded". The reason for this is to ensure that the append query is only hitting the newly finalized records each time we run this query. Also, on the rare occasion, those marked as "Uploaded" might need to be revised at which point I would run an update query on anything marked revised.

Clearly, the easiest way to do this would be to create a macro in the actual excel file itself to do this. However, I'd prefer to have this all self contained within Access. Is this possible? Is the fact that the Excel file is password protected make this nearly impossible?

Again, appreciate any feedback, tips, advice you can give.
Regards,
Travis

[ItsMe]

I am going to guess that the act of linking to the Excel file will cause said file to be in a "Read Only" state not accept or save any changes. Real time updates to the Excel file does not seem possible. The Excel file would have to be in an editable state to receive and save changes.

[Breezer23]

I am going to guess that the act of linking to the Excel file will cause said file to be in a "Read Only" state not accept or save any changes. Real time updates to the Excel file does not seem possible. The Excel file would have to be in an editable state to receive and save changes.

Correct - the linked table is read only. I guess I was under the assumpion VBA Script or Macro would actually be able to go into the live Excel file or push the changes to it. Maybe that isn't the case.

[ItsMe]

You could give it a try. There should be examples with a search of Transferspreadsheet
https://www.accessforums.net/program...tml#post210391

[ipisors]

The linked table is read-only (PS - never used linked Excel tables in Access, my opinion. Causes a plethora of datatype/junk data problems...better to custom code a routine to go through the excel file and carefully read each line, inserting (execute sql statement) where necessary, outputting exception back to excel where necessary, etc. etc. )

But there is no reason you can't update the Excel file.

Some sample aircode code to run from Access:

dim newapp as object
dim wb as object
dim ws as object
dim rng as object
dim lastrow as long
set newapp=createobject("excel.application")
set wb=newapp.workbooks.open("path to workbook")
set ws=wb.worksheets("name of worksheet")
lastrow=ws.range("A" & ws.rows.count).end(-4162).row
for each rng in ws.range("A2:A" & lastrow)
    if rng.value="Final" then
          rng.Value="Uploaded"
     End If
next rng
wb.save
newapp.quit
set newapp=nothing

As you can see various assumptions are being made in that code, you'd need to change to suit - (i.e. exactly what cells, in what location on what worksheet need to be updated, is it all of them that say Final, or.....?)

That should get you started - HTH

If possible, get rid of the Excel file and have your users doing data entry in your database, so there isn't all this going-the-long-way-around updating each from the other and then back to the other....

[Breezer23]

The linked table is read-only (PS - never used linked Excel tables in Access, my opinion. Causes a plethora of datatype/junk data problems...better to custom code a routine to go through the excel file and carefully read each line, inserting (execute sql statement) where necessary, outputting exception back to excel where necessary, etc. etc. )

But there is no reason you can't update the Excel file.

Some sample aircode code to run from Access:

dim newapp as object
dim wb as object
dim ws as object
dim rng as object
dim lastrow as long
set newapp=createobject("excel.application")
set wb=newapp.workbooks.open("path to workbook")
set ws=wb.worksheets("name of worksheet")
lastrow=ws.range("A" & ws.rows.count).end(-4162).row
for each rng in ws.range("A2:A" & lastrow)
    if rng.value="Final" then
          rng.Value="Uploaded"
     End If
next rng
wb.save
newapp.quit
set newapp=nothing

As you can see various assumptions are being made in that code, you'd need to change to suit - (i.e. exactly what cells, in what location on what worksheet need to be updated, is it all of them that say Final, or.....?)

That should get you started - HTH

If possible, get rid of the Excel file and have your users doing data entry in your database, so there isn't all this going-the-long-way-around updating each from the other and then back to the other....

Thank you sir! That should be helpful. I may change the method for importing data based on what you mentioned. I noticed the data type errors that it can cause when I first started.
Regards,
Travis