Open and edit an excel file from access vba

[roberto21]

I need to edit a couple of fields of a record in excel sheet, following certain operations in access.The code I am using follows (VBA access).

Please Login or Register  to view this content.

This code does not work: I get a compile error, "variable not defined", pointing to LookIn:=xlValues.
Note that:
1) The excel table is linked to the access app, but I cannot edit the linked file directly: Starting from version 2003, Microsoft removed the possibility of editing linked excel files from access due to "legal reasons". So I have to work on the excel file itself.
2) I could import the file, make the changes and re-export the file: but I cannot re-export to the same file, only to a new one, so the process becomes cumbersome
3) The Microsoft Excel 16.0 Object library is already included

I am using Access and Excel in Office 2016.

What am I missing in trying to operate on excel file from Access VBA? Thank you for any suggestion.

NOTE: this post is also open in ExcelFox.com, http://www.excelfox.com/forum/showth...rom-access-vba

[CK76]

The error is due to using Excel constant from Access side. Which likely does not know what xlValues stand for.

Whenever these enumeration is used, instead of the name, value should be supplied when used from external code.

Replace xlValues with -4163 or define xlValues as -4163 at top of your sub/module.

See link for bit more detail.
https://docs.microsoft.com/en-us/off...l.xlfindlookin

[roberto21]

Thanks for your help. What you sugges is exactly what I tried in order to have the code executed and check whether the excel file was actually opened. So I added
const xlValues as integer = -4163
and also xlApp.visible =true
With these additions, and a breakpoint at the instruction rowNo = etc., the code run, and I was able to check that the excel file was indeed opened, but in ReadOnly mode (this seems to go against what I have read, that workbook.open default is not readonly). However, at this point, the obvious correction seems:

Set xlBook = xlapp.Workbooks.Open(ExcelPath & "LibroSoci.xlsm", ReadOnly = False)

Unfortunately, with this correction I get Compile error, variable not defined for ReadOnly keyword. So I am stuck again.

What seems strange to me is that I have another piece of code in this access project that opens a Word application to print something with mailmerge.

Please Login or Register  to view this content.

and this code works fine, so access knows perfectly keywords and enumeration values of the word application.Why not for excel?

[CK76]

No idea. As that's more to do with your terminal environment and IT policies etc.
In most cases, Access should know Excel (or other Office application) constants. But the error you mentioned in the initial post indicated that was the issue.

As for your new issue, if you supply name of the argument for one, you must supply to all.

Ex:

Please Login or Register  to view this content.

[roberto21]

Just for your information, even declaring ReadOnly as boolean and setting it to false, first an empty excel screen appears (no sheet, just empty) and then the correct workbook and sheet appears, but still in readonly mode.
Thanks for your attention

[protonLeah]

Set xlBook = xlapp.Workbooks.Open(ExcelPath & "LibroSoci.xlsm", ReadOnly = False)

Missing colon?
Set xlBook = xlapp.Workbooks.Open(ExcelPath & "LibroSoci.xlsm", ReadOnly:=False)

[roberto21]

Set xlBook = xlapp.Workbooks.Open(ExcelPath & "LibroSoci.xlsm", ReadOnly:=False)


You are right, missing colon. Code corrected, declaration of ReadOnly not needed any more, but no change in behavior:

first, excel opens without anything (empty screen, no workbook name)
then, requested sheet appears in read only mode
rowNo gets correct value, so FIND works; cells are modified as requested
"save" results in message : there is already a file with same name in location, do you want to replace it?
Answering yes causes code to finish without other messages and the workbook closes, but the empty excel screen remains there and must be closed manually. The workbook is NOT updated, so has not been replaced.

[romperstomper]

If you open the workbook manually on the same machine, does it open in read-only mode?

[roberto21]

Definitely NO, the file opens without restrictions. The security properties, by the way, are set to Full control by Everyone.

[CK76]

Hmm, ran test code on my end via Access. It opened workbook in read-write mode.

Please Login or Register  to view this content.

My guess, is that the issue isn't with the code itself. But with your system/terminal. Try restarting your terminal and then running the code. That may fix your issue.
If that doesn't fix, try running installation fix on Office via "Add or remove Programs" menu.

[roberto21]

Thank you. I ran a test myself, on a copy of the excel file (that is .xlsm file) from which I removed all macros and saved as a .xlsx file. This worked: the file .xlsx was opened in read/write.
This does not solve my problem, since I have to update the .xlsm file, which is used by another group, but at least, if this is true, I would know that the update cannot be done....
I have to assume that .xlsm (macro enabled files) are opened in read only mode, and "regular" xslx files can be opened read/write? If so, I was not able to find anything in the documentation explaining this peculiarity. Maybe I did not search enough.
Assuming (?) that this is the case, I am still stuck with the variable not declared error for xlValues. The strange thing is that xlValues is listed in the object browser, so I don't understand why it is not recognized by the compiler.

I did already a Office repair. Also the PC where the code runs has been shutdown and restarted many times. I may try to uninstall office and reinstall from scratch, but this is not something I can do easily.

[CK76]

If the issue is specific to xlsm file. Try checking your trust center settings.
Trust Center -> Macro Settings
Other than that, I'm out of ideas.

Using same code as my previous post. I've got no issues opening macro enabled workbook with read/write access.

[roberto21]

I found the solution to the problem. rt was obvious, as are many solutions AFTER you have found them. It was also written in my first post: the table is linked to the access app, therefore the workbook is to be considered open. When I try to open it again with workbooks.open, excel can only open it in read-only mode. One may complain "why didn't excel say so, that the file was already open"? This I don't know, and would like to know.
Anyhow, I replaced

DoCmd.TransferSpreadsheet acLink,...
with
DoCmd.TransferSpreadsheet acImport, ...

and now the .xlsm file opens correctly in read/write. Thank you everybody for your attention and interest. Now, if I or you could find the reason why xlValues is not recognized by the compiler...

[CK76]

Thanks for the update and sharing your solution. Glad you got that solved