none
ExecuteExcel4Macro function does not work on closed .xlsm files under Windows 7 RRS feed

  • Question

  • I have used the ExecuteExcel4Macro function for years inside VBA code of my Excel file to extract values from closed .xls files. The routine has worked flawlessly for years to read values from each closed .xls file, All of the closed .xls files, of course, have identical layouts (same worksheet names, same cell locations for the data I want to extract).

    I use the ExecuteExcel4Macro function to do so, in VBA code inside my primary Excel file. I pass as arguments the: path name, Excel file name, worksheet name, and cell reference. This routine has worked flawlessly for years to extract values from closed .xls files, allowing me to place the extracted values in my open Excel file. (Of course, there are lots more details than this on how I'm able to choose the cells I want from each closed files, and how I write the values extracted from the closed Excel files to a table containing the values extracted.)

    This year, for the first time, I also need to extract data from the same worksheets and cell locations from inside closed .xlsm files, in addition to closed .xls files. I'm able to get values from every closed .xls file, but I get a type mismatch error when extracting data from any closed .xlsm file. I'm running Windows 7. (I trap the VBA error which happens to be "type mismatch", place the string "UNABLE TO READ EXCEL FILE" in the table I populate with values from all the closed Excel files to notify me what happened, then move to the next Excel file to continue extracting values from the next closed file.) 

    HERE'S THE CATCH: When I use my same Excel file with the same VBA code and run it under Windows 10, it works perfectly. I can get values from all my closed .xls files AND from all my closed .xlsm files. In other words, my VBAcode works flawlessly under Windows 10 for every closed .xls and .xlsm file.

    To summarize: Running under Win 7, the VBA code successfully extracts values from closed .xls files, but it cannot extract values from closed .xlsm files. Running under Win 10, the same Excel file and VBA code extracts values from every closed .xls files and from every closed .xlsm files, flawlessly. 

    One more note:  Under Win 7, if I first open a .xlsm file before running my VBA code, then my VBA code does extract values from that open .xlsm file, along extracting values correctly from all of the closed .xls files.  

    Thanks for any hints you might have.

    Wednesday, May 17, 2017 5:58 PM

All replies

  • Hello,

    What is your Office version in the these computer?

    Could you please share us detail steps to reproduce your issue? 

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 18, 2017 9:05 AM
    Moderator