'Workbooks.Activate not working on some computers
I ran into a strange Excel 2013 VBA behavior today and felt posting the solution to the problem may help someone else in the future. The following command worked fine on my computer and most others but one user would get an error message. I'm using Windows 7.
Workbooks("Book1").Activate
Solution 1:[1]
What I found out was that the other user has 'extensions for known file types' shown. By default Windows wants to hide those. In order to get the code to work properly on all machines, I had to include the file extension in the code as shown below. When a new workbook is created and has not yet been saved, it does not need a file extension included in this command as it is not yet determined.
Workbooks("Book1.xlsm").Activate
Solution 2:[2]
Just guessing, but this may be related to a setting in "Folder options" of Windows Explorer.
On the View tab there is an option "Hide extensions for known file types" - this will affect how you can refer to workbooks by name in VBA.
EDIT: seems you got there already!
Solution 3:[3]
I've already had the file extensions enabled but it didn't work. I fixed by forcing the file and sheet to activate.
Example (while the files are still open):
Windows("file2.xlsm").Activate
ThisWorkbook.Worksheets("sheet2").Activate
I know that Activate and Select are inneficient, but if it works..
Solution 4:[4]
Hi in your code use two dim for workbooks like dim currentwb,newwb as workbook
set currentwb = ActiveWorkbook
and once you create your new workbook set
set newwb = ActiveWorkbook
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Russ Verbrugge |
| Solution 2 | Tim Williams |
| Solution 3 | CCM |
| Solution 4 | Shrikant |