ExecuteExcel4Macro not working now for closed .xlsm files under Windows 7

[Edurd]

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


Thanks for any hints you might have.

 

[Edurd]

One more piece of info to add to my original post.

If I first open any of my .xlsm files, then run the VBA code that reads the Excel files contained in a single folder, the VBA code successfully reads the data from the opened .xlsm file without any errors, along with getting data from all of the other closed .xls files in the same folder. Again, I am running under Windows 7. So, the "state" of my .xlsm file somehow determines whether my VBA code works on an .xlsm file: When a .xlsm file is closed, I cannot extract data from it. When a .xlsm file is open, I can extract data from it.

 

[Razorman]

I have this exact same problem. However, I arrived here slightly differently. I successfully used an Excel4Macro to retrieve data from closed .xlsx workbooks. Today I tried to use the same procedure on .xls workbooks - it gives me a REF error - but only for those workbooks that were NOT opened (without using a macro).

Workbooks that were previously opened will display the correct result.

Did you ever find a solution? The whole reason I want to use the procedure is to avoid wasting time from opening and closing workbooks.