+ Reply to Thread
Results 1 to 14 of 14

Open and edit an excel file from access vba

  1. #1
    Registered User
    Join Date
    03-14-2013
    Location
    Milan, Italy
    MS-Off Ver
    Excel 2010
    Posts
    17

    Open and edit an excel file from access vba

    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

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,168

    Re: Open and edit an excel file from access vba

    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
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  3. #3
    Registered User
    Join Date
    03-14-2013
    Location
    Milan, Italy
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Open and edit an excel file from access vba

    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?

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,168

    Re: Open and edit an excel file from access vba

    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.

  5. #5
    Registered User
    Join Date
    03-14-2013
    Location
    Milan, Italy
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Open and edit an excel file from access vba

    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

  6. #6
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    10,750

    Re: Open and edit an excel file from access vba

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

    Missing colon?
    Set xlBook = xlapp.Workbooks.Open(ExcelPath & "LibroSoci.xlsm", ReadOnly:=False)
    Last edited by protonLeah; 02-07-2020 at 04:33 PM.
    Ben Van Johnson

  7. #7
    Registered User
    Join Date
    03-14-2013
    Location
    Milan, Italy
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Open and edit an excel file from access vba

    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.

  8. #8
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    UK
    MS-Off Ver
    2016 primarily
    Posts
    4,108

    Re: Open and edit an excel file from access vba

    If you open the workbook manually on the same machine, does it open in read-only mode?
    Rory
    Sue, you're shouting at tea

  9. #9
    Registered User
    Join Date
    03-14-2013
    Location
    Milan, Italy
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Open and edit an excel file from access vba

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

  10. #10
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,168

    Re: Open and edit an excel file from access vba

    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.

  11. #11
    Registered User
    Join Date
    03-14-2013
    Location
    Milan, Italy
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Open and edit an excel file from access vba

    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.
    Last edited by roberto21; 02-10-2020 at 02:09 PM.

  12. #12
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,168

    Re: Open and edit an excel file from access vba

    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.

  13. #13
    Registered User
    Join Date
    03-14-2013
    Location
    Milan, Italy
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Open and edit an excel file from access vba

    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...

  14. #14
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,168

    Re: Open and edit an excel file from access vba

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1