I am working on an Excel file to bypass scripting restrictions to get some automation for a mass printing process. I have the open and close working fine for each product type and I am assuming I will get the printing process done once I start to write it. The problem I am having is when the code gets to a non existing file name. I am sure I am doing this the wrong way but it has been near a decade since I touched VB in any way. File name is structured like so: Dock 1 file -MM-DD-YYYY.xlsm. I use the counter as the DD variable and not every day of the month has a file associated with it.
Private Sub CommandButton1_Click()
'Open all files of certain type, print the summary page, and close files in a folder automatically
'set initial variables for dropdowns
'year and month determine folders and file determines what item type
Dim file As String
Dim year As String
Dim month As String
file = Range("A2").Text
year = Range("B2").Text
month = Range("C2").Text
'check to ensure all dropdowns are selected
If file = "" Or year = "" Or month = "" Then
MsgBox "Please select a File Type, Year, and Month", vbCritical + vbOKOnly
Exit Sub
End If
'variables for loop and file
Dim counter As Integer
Dim fname As String
Dim countString As String
Dim wbOpen As Workbook
'loop for entire month <currently reduced for testing with delay to make sure correct files are opening>
For counter = 1 To 3
countString = Format(CStr(counter), "00")
fname = "F:\Order Scan Sheets\" _
& year & "\Warehouse\Finished\" & month & "\Dock 1" _
& file & " -07-" & countString & "-2021.xlsm"
Workbooks.Open fname
Set wbOpen = Workbooks.Open(fname)
Application.Wait (Now + TimeValue("0:00:05")) 'delay for testing only
wbOpen.Close SaveChanges:=False
Next counter
End Sub
