Forum Discussion
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
- JKPieterseSilver ContributorIt 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.
- Tarek3Copper 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!
Your code already appends the path separator to both fPath and sPath.
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