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

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. 

Thanks for any hints you might have.

Answer 1

Hi,

We suggest posting your concern as well to our Microsoft Developer Network forum to get more Community members chance to share their suggestions. When you get to the website, select Community > Forums > click View All > Type in "Excel" then select appropriate category.

Kindest regards.

Answer 2

No answer from Microsoft community to this problem.  Is there no one that can help me?

Thanks.

Answer 3

Will do, thanks.