wildcat89
wildcat89

Reputation: 1285

Workbooks.Open Not Actually Opening Documents Until The End of My Loop?

I have noticed a few others having this issue, but can't seem to find a solution. Windows 10 and Excel 2016.

I'm trying to have a VBA macro loop through all .xls documents in a folder, by actually OPENING the document (not just "opening in the background/invisibly") because I have some AutoHotKey scripts that require the document to be physically open in order for them to work. As it stands right now, the VBA carries out the entirety of its code in the background and I can't have that because the AHK scripts aren't editing the document with it not being open.

I want to physically open the first document, once opened then use the SendKeys function to trigger the AHK scripts to run (which edit the document the way I want, don't ask), then print the document, save and close it, then move on to the next document in the folder.

The code looks like this:

Sub Button4_Click()

'
' Button2_Click Macro
' Bulk preparation of FE pages for printing
'

' Variables
Dim folderPath As String
Dim Filename As String
Dim wb As Workbook
Dim Masterwb  As Workbook
Dim sh As Worksheet
Dim NewSht As Worksheet
Dim FindRng As Range
Dim PasteRow As Long
Dim SrchRng As Range, cel As Range

Application.ScreenUpdating = False
Application.DisplayAlerts = False

' set master workbook
Set Masterwb = Workbooks("MattsTools.xlsm")

' Directory of where the copies of the files to be unchecked are located
folderPath = "C:\Users\Employee\Desktop\...\For_Printing"

' A conditional for finding the directory
If Right(folderPath, 1) <> "\" Then folderPath = folderPath & "\"

' Do this for each workbook in the folder, as long as it's not MattsTools.xlsm which is this workbook
Filename = Dir(folderPath & "*.xls*")
Do While Filename <> "" And Filename <> "MattsTools.xlsm"

    ' Open work book? WHY WON'T IT ACTUALLY OPEN!?
    Set wb = Workbooks.Open(folderPath & Filename)

    ' Wait for the workbook to open?
    Application.Wait (Now + TimeValue("0:00:05"))

    ' Always start on the first worksheet
    wb.Worksheets("FIRE EXT.").Activate

    ' Hit the AHK Script Hotkey to upgrade the template
    SendKeys "^%+u"

    ' Sleep for 7 seconds to wait for upgrade to finish
    Application.Wait (Now + TimeValue("0:00:07"))

...'The Rest of my VBA Omitted...

    ' Now print the sheet
    ActiveSheet.PrintOut

    ' Close and save the workbook
    wb.Close True



Exit_Loop:
    Set wb = Nothing
    Filename = Dir

' Next workbook
Loop

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

Any ideas on how to open this thing? I tried setting the Editable:=True but that didn't do anything. This is for a work project, so would love to get some input. Thanks!

Upvotes: 0

Views: 129

Answers (1)

Tim Williams
Tim Williams

Reputation: 166126

When will this loop exit?

Do While Filename <> "" And Filename <> "MattsTools.xlsm" 

If the first file found is MattsTools then that's the end of your looping.

You likely want something more like this:

Do While Filename <> "" 
    If Filename <> ThisWorkbook.Name Then
         'Process this file
    End If
    FileName = Dir()
Loop

Upvotes: 2

Related Questions