When Macros Fail
A little quick note on that issue I had where the vendor’s exported Excel files were not cooperating with Microsoft: my macro no longer could “see” the downloaded files, even when “open”. The security warning message never showed up to say that the file was in Protected View mode. I even created another macro to ask itself what did it see and the answer was the file in which that macro resided and one of the downloads. Everything else was invisible or did not exist.
There are two things you can do when faced with this situation (macros not seeing files):
- Make sure you open up the downloaded files one by one rather than select all downloads and open them at once.
- If #1 does not work, then open the first download file and then from Excel, open the other files.
The second option seems to really work.
In the future, when doing another macro type file, I will probably have the macro search for the files sitting in the directory rather than search for files already opened in Excel. The user using the macro would just type the directory of where the files are located
and I would have instructions on how to determine the directory path. Then the user would have to go through the open files to check to see if they are in Protected View.
But, a better option would be the vendor getting their software to be compatible with Microsoft. Right now, their software is not able to work with Microsoft Edge so we are forced to work with IE 11. That’s fine except the vendor can’t keep up with the security fixes that Microsoft often has to do and I think Microsoft did a security fix that created this problem. Because before, we were able to see those downloads. That was the whole raison d’etre for the macro: to see those downloaded files and copy the information from those files and put them into the report file.