ExportasfixedFormat office 2010

%3CLINGO-SUB%20id%3D%22lingo-sub-324441%22%20slang%3D%22en-US%22%3EExportasfixedFormat%20office%202010%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-324441%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20the%20following%20macro%20which%20produces%20the%20desired%20effect%20but%20then%20errors%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20appreciated%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E'This%20code%20will%20save%20each%20worsheet%20as%20a%20separate%20PDF%3CBR%20%2F%3ESub%20SaveWorkshetAsPDF()%3CBR%20%2F%3EApplication.ScreenUpdating%20%3D%20False%3C%2FP%3E%3CP%3EDim%20ws%20As%20Worksheet%3CBR%20%2F%3EDim%20path%20As%20String%3CBR%20%2F%3EDim%20fol%20As%20String%3CBR%20%2F%3EDim%20name%20As%20String%3CBR%20%2F%3EDim%20concat%20As%20String%3CBR%20%2F%3EDim%20fdObj%20As%20Object%3C%2FP%3E%3CP%3E%3CBR%20%2F%3Epath%20%3D%20ActiveWorkbook.path%3CBR%20%2F%3Efol%20%3D%20%22%5CPDFs%5C%22%3CBR%20%2F%3ESet%20fdObj%20%3D%20CreateObject(%22Scripting.FileSystemObject%22)%3CBR%20%2F%3EIf%20fdObj.FolderExists(path%20%26amp%3B%20fol)%20Then%3CBR%20%2F%3EMsgBox%20%22Found%20it%22%2C%20vbInformation%3CBR%20%2F%3EElse%3CBR%20%2F%3EfdObj.createfolder%20(path%20%26amp%3B%20fol)%3CBR%20%2F%3EMsgBox%20%22folder%20created%22%2C%20vbInformation%3CBR%20%2F%3EEnd%20If%3C%2FP%3E%3CP%3EFor%20Each%20ws%20In%20Worksheets%3C%2FP%3E%3CP%3Ename%20%3D%20ws.name%3CBR%20%2F%3EMsgBox%20name%3CBR%20%2F%3E%3CBR%20%2F%3Econcat%20%3D%20path%20%26amp%3B%20fol%20%26amp%3B%20name%3CBR%20%2F%3EMsgBox%20concat%3CBR%20%2F%3EWith%20ws.PageSetup%3CBR%20%2F%3E.Orientation%20%3D%20xlLandscape%3CBR%20%2F%3E.FitToPagesWide%20%3D%201%3CBR%20%2F%3E.FitToPagesTall%20%3D%20False%3CBR%20%2F%3E.PaperSize%20%3D%20xlPaperA3%3CBR%20%2F%3EEnd%20With%3CBR%20%2F%3Ews.ExportAsFixedFormat%20xlTypePDF%2C%20fileName%3A%3Dconcat%2C%20OpenAfterPublish%3A%3DTrue%3CBR%20%2F%3E%3CBR%20%2F%3ENext%20ws%3C%2FP%3E%3CP%3EApplication.ScreenUpdating%20%3D%20True%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-324441%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

I have the following macro which produces the desired effect but then errors

 

Any help appreciated

 

'This code will save each worsheet as a separate PDF
Sub SaveWorkshetAsPDF()
Application.ScreenUpdating = False

Dim ws As Worksheet
Dim path As String
Dim fol As String
Dim name As String
Dim concat As String
Dim fdObj As Object


path = ActiveWorkbook.path
fol = "\PDFs\"
Set fdObj = CreateObject("Scripting.FileSystemObject")
If fdObj.FolderExists(path & fol) Then
MsgBox "Found it", vbInformation
Else
fdObj.createfolder (path & fol)
MsgBox "folder created", vbInformation
End If

For Each ws In Worksheets

name = ws.name
MsgBox name

concat = path & fol & name
MsgBox concat
With ws.PageSetup
.Orientation = xlLandscape
.FitToPagesWide = 1
.FitToPagesTall = False
.PaperSize = xlPaperA3
End With
ws.ExportAsFixedFormat xlTypePDF, fileName:=concat, OpenAfterPublish:=True

Next ws

Application.ScreenUpdating = True

End Sub

 

0 Replies