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:UsersEmployeeDesktop...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!