how to create PDF of files in a folder that match range

Question

I am trying to ExportAsFixedFormat any excel files from a specific folder if they match from my range B3 to lr. (range is the filename minus the extension)

This is what i have so far. Currently it tries to open each file (not all are excel files, some are autocad) and export the .xlsx as a pdf. It PDFs the current page (which i do not want) and it PDFs one of the .xlsx files from my list.

I do not want to print all excel files from that folder only the ones from the range.

Thanks for any feedback.

Sub ExcelPrint()
Dim myFile As String, myFolder As String
Dim LoginName As String, destFolder As String
myFolder = Range("B1").Value
lr = Cells(Rows.Count, "B").End(xlUp).Row
LoginName = UCase(GetUserID)
destFolder = "C:\Users\" & LoginName & "\Desktop\_PDF\_Temp\"

If Len(Dir("C:\Users\" & LoginName & "\Desktop\_PDF\", vbDirectory)) = 0 Then
    MkDir "C:\Users\" & LoginName & "\Desktop\_PDF\"
End If

If Len(Dir("C:\Users\" & LoginName & "\Desktop\_PDF\_Temp\", vbDirectory)) = 0 Then
    MkDir "C:\Users\" & LoginName & "\Desktop\_PDF\_Temp\"
End If

For i = 3 To lr
    myFile = Cells(i, 2).Value & ".xlsx"
    On Error Resume Next
    Workbooks.Open _
            FileName:=myFolder & myFile, _
            ReadOnly:=True

    With ActiveSheet.PageSetup 'How to print only the .xlsx files from 3 to lr?
        .Orientation = xlLandscape
        .FitToPagesTall = 1
        .FitToPagesWide = 1
    End With

        ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        FileName:=destFolder & ActiveSheet.Name, _ 'How to save as Workbook name?
        IgnorePrintAreas:=False, _
        From:=1, _
        To:=1, _
        OpenAfterPublish:=False
        ActiveSheet.Close
Next i
End Sub

How would it open a file which is autocad when you are trying to open it with xlsx extension? doesn't that throw an error? You could use either FileSystemObject or DIR to check file type before attempting to open the file — Zac
it wouldn't open the autocad file it would just pass by it with on error resume next — Matt Taylor
I didn't see the Resume Next statement! Not something I use often. You don't need that. Like I said, you can use something like FSO to check if file exists prior to opening it — Zac
Just a hint (not an answer to your question): Don't rely on C:\Users\<username> to be the home of the user docs. Use environ("Userprofile") instead. And personally, I don't like it to have anything stored on the desktop... — FunThomas
@FunThomas thanks for the environ tip. Desktop is a temporary location that the user will then print from and move the files to the final location inside of ProjectWise. — Matt Taylor

Tate Garringer Tate Garringer · Accepted Answer · 2019-02-01T16:56:14

Your problem is with On Error Resume Next. For instance, let's say it fails on the first iteration. On Error Resume Next will allow it to continue without opening anything, your current workbook would be ActiveWorkbook and then it would carry out the rest of your listed operations. Also, ActiveSheet does not support .Close. I imagine you were looking for ActiveWorkbook.Close.

I would suggest using a FileSystemObject and checking for the file extension, but if that's not an option, use a legitimate error handler to handle trying to open a non-existant workbook.

On Error Goto eHandler
For i = 3 to lr
    'Do Stuff
nextLine:
Next i
Exit Sub
eHandler:
If Err.Number = 1004 Then 'Use whatever error number it raises without `On Error Resume Next`
    Resume nextLine
End If
Msgbox Err.Number & vbcrlf & Err.Description
End Sub

Additionally, you could add a variable to store the opened workbook so you don't have to use ActiveSheet. This would also take care of your, technically second, question in your commented out line 'How to save as Workbook name? as you could just use .Name prefixed with the variable your workbook is stored in.

how to create PDF of files in a folder that match range

1 Answers