Print to PDF file

[GhostofDoom]

Hello,

i was wondering if someone can helpme out
i use this code to make an pdf file from the selected cells

Private Sub Workbook_Open()


  On Error GoTo Print_Error
Sheet3.Select


mySheets = Array("sheetlist")
Application.PrintCommunication = False
For Each sh In mySheets
    Sheets(sh).PageSetup.Orientation = xlLandscape
    Sheets(sh).PageSetup.FitToPagesTall = 0
    
Next
Application.PrintCommunication = True


'Selection.CurrentRegion.Select
Range(ActiveCell, ActiveCell.End(xlDown).End(xlToRight)).Select


  
Selection.ExportAsFixedFormat xlTypePDF, fileName:= _
"C:\Page1", Openafterpublish:=True


Print_Error:


MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure PrintFile of " & m_ProjectName
Exit Sub
End Sub

but when i run it from workbook_open()
it gives me an empty page?

any solutions please.

Thanks

[DavidBowman]

Try putting a command to activate the sheet you want to select on before selecting the current range. Because you are iterating through sheets before it is likely loosing it's place.

[GhostofDoom]

Hello DavidBowman,

Thank you that did the job

one more question
is there a way to show a message like please wait...
and when pdf is ready the message go away?

[DavidBowman]

The best way to do that would be to create a do loop that checks to see if the file exists yet. In general like this:

FileExists = False
Do
    'Check to see if the file then FileExists = true
Loop Until FileExists

[GhostofDoom]

hmmm not sure what we do wrong
but it keeps making the file

 FileExists = False
Do
 Selection.ExportAsFixedFormat xlTypePDF, fileName:= _
"C:\Page1", Openafterpublish:=True
   'Check to see if the file then FileExists = true
Loop Until FileExists


If FileExists = True Then
MsgBox "file available", vbOKOnly, ""
End If

[DavidBowman]

That is because the ExportAs function needs to come before the Do. You write the file in the ExportAs step. Then in the Do Loop part you test to see if the file is written to disk.

[GhostofDoom]

Whoops, okay got it working thanks

 Selection.ExportAsFixedFormat xlTypePDF, fileName:= _
"C:\Page1", Openafterpublish:=True





Dim File As String
Dim FileExists As String
File = "C:\Page1.pdf"


FileExists = Dir(File)
Do
     If FileExists = "" Then
     Else
MsgBox "File exists"
Exit Sub
End If
Loop Until FileExists = ""