VBA code to create PDFs causing Excel to crash immediately without any hang

[DaveyD]

Hi, I have a VBA procedure that loops through each file in a directory and does the following:
1. Opens the file
2. Selects 2 sheets
3. Exports those sheets to a PDF

Excel is crashing during this process at random. I.e., one time it can crash after 6 PDF files created, and another after 10 files etc.
The interesting thing is that Excel is crashing without hanging at all! The window just immediately closes and excel is closed! (Never saw this before)

Another thing to note, if I run the code in debug mode, and step through each file one at a time, then it works perfectly.
I.e., I put a break point right before the line to export to PDF. Each time it hit the break point, I just pressed F5 to continue to the next file. Excel got through all the files without crashing.
Can it be a speed issue? I.e., creating too many files to export at the same time?

If anyone has any idea what this can be, please let me know
Thanks,
David


This is the line of code that I use to export:

        ActiveSheet.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            fileName:=fileName, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=False

 

[ranman256]

Instead of Export command, have you tried the Print command to the PDF printer?

 

[Hjemmet]

I Have this Code

Sub TurnTabIntoPDF1()
'
' TurnTabIntoPDF Macro
'
Range("A1:T22").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$T$22"
With ActiveSheet.PageSetup
.Orientation = xlLandscape
.PaperSize = xlPaperA5
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
With ActiveSheet
.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
ActiveWorkbook.Path & "" & .Range("B6").Value, Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=True

End With
End Sub

It make a PDF file

 

[DaveyD]

@ranman256, I began to try that but then realized that I won't be able to name the file which is very important, so I didn't continue down that path
@Hjemmet, thank you, but that seems to be the same code. I also have other code to make pdf that works fine and even more so, this code used to work fine!
But now, for some reason, it is crashing without any error messages


Any other suggestions?

 

[ranman256]

It IS possible to print to printer AND name the file.

 

[DaveyD]

It IS possible to print to printer AND name the file.

I did not know that - can you show an example?

 

[Hjemmet]

Click on "File" and then "Save as" here you can make your choise

or press"CTRL and P" then you can choise Print a PDF file under Printer choise

 

[DaveyD]

Click on "File" and then "Save as" here you can make your choise

or press"CTRL and P" then you can choise Print a PDF file under Printer choise

@Hjemmet, thanks but we are talking about doing it through VBA code