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


Thanks for any hints you might have.

[Edurd]

One additional note to my original post: Under Windows 7, if I first open any .xlsm file in my folder of all the Excel files that I want to extract data from, and then run the macro with the ExecuteExcel4Macro function, I can get all the data on the open .xlsm file without any error. So, when a .xlsm file is open, I can extract data from it with my ExecuteExcel4Macro function. When a .xlsm file is closed, I can't extract data from it.

[Edurd]

Can anyone help me this this issue? ExecuteExcel4Macro routines I've used for years have worked flawlessly on closed .xls files. This year, need to also extract data from closed .xlsm files, but the routine is not able to extract data from closed .xlsm files when I run the routine on Windows 7 machine. The same routine works perfectly on a Windows 10 machine.

Any ideas why it does not work on Win7 machine (i.e., routine won't extract data from a closed .xlsm file, only from: (1) closed .xls files, and (2) open .xlsm files.

Thanks.