PDA

View Full Version : Print to PDF file



GhostofDoom
12-06-2019, 09:55 AM
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
12-06-2019, 10:01 AM
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
12-06-2019, 10:31 AM
Hello DavidBowman,

Thank you that did the job :thumb

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

DavidBowman
12-06-2019, 11:09 AM
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
12-06-2019, 11:29 AM
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
12-06-2019, 12:23 PM
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
12-06-2019, 03:11 PM
Whoops, okay got it working thanks :thumb



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 = ""