VBA Excel - .Window doesnt recognise open excel windows

Question

The code below loops through the open excel files if the file name is what the naming convention is close to it will then run the code in the if statment so that the file can be saved later on

This code works in excel 2003 but not in excel 2010, looking at each part of the code the myWindow.Caption only appears to get 1 file name instead of the 5 that there hould be. What am I missing for it to loop though all of the files on 2010?

FYI - There are multple instances of the For loop but since it is all the same code I havent pasted it here. Let me know if you want it but it is near enough identical

Sub File_Saver()
    Dim iFileCount As Integer
    Dim myWindow As Window
    Dim r As Integer

    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With

    For Each myWindow In Application.Windows
        If LCase(myWindow.Caption) Like LCase("CHL?ISS*") Then

            iFileCount = 1
            r = 21

            myWindow.Activate
            'Set Column
            c = B

            GoTo Continue
            Exit For
        End If
    Next myWindow

to work properly, you would need to run multiple instances of the application... just open up all files normally will "merge" it to 1 window (with caption of the actual active workbook)... you should run for each item in workbooks then (to get all open workbook names) — Dirk Reichel, Jun 21 '16 at 15:34

score 3 · Accepted Answer · edited May 23 '17 at 11:52

3

For multiple Excel instances take a look at this answer: Can VBA Reach Across Instances of Excel?

This works for one instance of Excel:

Dim workBooks as Workbooks    
Dim book As Workbook
Set workBooks = Application.Workbooks
For Each book In workbooks
    If LCase(book.Name) Like LCase("CHL?ISS*") Then
        iFileCount = 1
        r = 21
        book.Activate
        c = B
        ' Not sure where the Continue: statement is
        GoTo Continue
Exit For
    End If
Next

edited May 23 '17 at 11:52

Community

1
1

answered Jun 21 '16 at 15:42

ghg565

422
6
15

Beat me to the punch! This is what I would do. – deasa Jun 21 '16 at 15:43
@ghg565 Thought you had it but it does the same thing as as before, it only gets 1 file name and moves onto the next iteration of the loop. The `For` loop dosnt even loop it just runs strait past it – Mr.Burns Jun 21 '16 at 15:53
@ghg565 it is, it gets the name of theexcel document I am running the code from but then goes to the `end if` > `next` and then onto the line below – Mr.Burns Jun 21 '16 at 16:02
In your VBA editor, does the project explorer window `Ctrl-R` show more than one project? If not, then ghg565 's code is working fine. – deasa Jun 21 '16 at 16:02
If it only shows one project, then you'll need to reference http://stackoverflow.com/questions/2971473/can-vba-reach-across-instances-of-excel for more help. – deasa Jun 21 '16 at 16:10
I don't know where the `Continue:` statement lives but I can say that if I remove that and the `Exit For` it should work. – ghg565 Jun 21 '16 at 16:15
@ghg565 , I got your code working, it seems the issue was it was opening excel with someone called AppX, some sort of excel work around we some times use at work. Still your code works with when used with the proper excel, Thanks – Mr.Burns Jun 22 '16 at 08:40

VBA Excel - .Window doesnt recognise open excel windows

1 Answers1