Forum Discussion

Tarek3's avatar
Tarek3
Copper Contributor
Oct 21, 2022

Help with macro to export ONLY the first sheet of multiple excel workbooks to PDFs

Hi All! I have been working to clean up this macro code to allow it to run better on my computer. To preface, I have really little knowledge about all this, and have had a variety of people contribute to this, and my trouble shooting ability is quite weak. First I'll explain what I'm looking for this code to do.

I have a folder saved locally on my 'C' drive, with ~100 xlsx workbooks. Each workbook contains 2 sheets, the second sheet contains raw data, and the first page a cleaned summary of the data. As such, I would like this macro/vba code to export ONLY the first sheet (left-most) in each '.xlsx' workbook in this folder as as PDF, and for those PDFs to be saved into a separate folder on my 'C' drive.

 

Currently, when I run the code I will list below, excel half-crashes (for lack of a more technical term), shown https://postimg.cc/hfpQNBXD and https://postimg.cc/JGnzDBh8(I'm aware these are quite bland images, but this is how excel / vba looks when I run the code as it currently is). Excel locks up, the spreadsheet screen goes grey, and the cells disappear. There is not however any error message. In the VBA screen, it looses all functionality except the ability to close VBA. 2 (sometimes 1) PDFs are produced into the desired folder, so it does *something*, but certainly not the ~100 PDFs I'm looking for.

I would really appreciate any help in determining the issues with this code, and possible solutions to allow me achieve my desired result, stated above.

Below is the code in question;

Sub Printer()
    Dim sFile       As String
    Dim sPath       As String
    Dim wks         As Worksheet
    Dim wbSource    As Workbook
    Dim fPath       As String
    Dim fName       As String
    
    
    'What folder to search?
    fPath = "C:\Users\User1\Desktop\PDF_Test"
    'Where will we save to?
    sPath = "C:\Users\User1\Desktop\PDF_Test\To_Save"
    
    'Error check
    If Right(fPath, 1) <> Application.PathSeparator Then
        fPath = fPath & Application.PathSeparator
    End If
    If Right(sPath, 1) <> Application.PathSeparator Then
        sPath = sPath & Application.PathSeparator
    End If
    
    MsgBox "We are looking in: " & fPath
    MsgBox "Files will save to: " & sPath
    
    'Find files
    fName = Dir(fPath & "*.xls*")
    
    MsgBox "Attempt at first file name: " & fName
    
    'Run faster
    Application.ScreenUpdating = False
    
    'Loop until all found
    Do Until fName = ""
        Set wbSource = Workbooks.Open(fPath & fName)
        
        
        'This is the part that may need to be changed
        With wbSource
            sFile = .Worksheets(1).Name & ".pdf"
            .Worksheets(1).ExportAsFixedFormat Type:=xlTypePDF, _
                                        Filename:=sPath & sFile, _
                                        Quality:=xlQualityStandard, _
                                        IncludeDocProperties:=False, _
                                        IgnorePrintAreas:=False, _
                                        OpenAfterPublish:=False
        End With
        
        wbSource.Close savechanges:=False
        
        'Get name of next file
        fName = Dir()
    Loop
    
    Application.ScreenUpdating = True
    
End Sub


Thank you in advance to any and all who help!

9 Replies

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    It looks like it is taking the name of the first worksheet as file name. Are you sure each file has a differently named first worksheet? Also, you need to append the path separator to the save path.
    • Tarek3's avatar
      Tarek3
      Copper Contributor

      JKPieterse thank you for your reply!

      I can confirm that the first sheet of each xlsx file has a different name, unique to each location that the file refers to.

       

      Can you explain to me how to append the path separator? I do not know how to do that.

       

      Thank you for your help!

  • Tarek3 

    Perhaps it's a timing problem. Does it work if you add a couple of DoEvents lines in the loop?

        'Loop until all found
        Do Until fName = ""
            Set wbSource = Workbooks.Open(fPath & fName)
            DoEvents
            'This is the part that may need to be changed
            With wbSource
                sFile = .Worksheets(1).Name & ".pdf"
                .Worksheets(1).ExportAsFixedFormat Type:=xlTypePDF, _
                                            Filename:=sPath & sFile, _
                                            Quality:=xlQualityStandard, _
                                            IncludeDocProperties:=False, _
                                            IgnorePrintAreas:=False, _
                                            OpenAfterPublish:=False
            End With
            DoEvents
            wbSource.Close SaveChanges:=False
            
            'Get name of next file
            fName = Dir()
        Loop
    • Tarek3's avatar
      Tarek3
      Copper Contributor
      Thank you for your response! Where would it be effective to add DoEvents? I'm unfamiliar with that.

      Thank you!

Resources