Works in 2000 not in 2003?

[Question Boy]

Hello,

The following code is extracted from a larger routine which purpor is to
print/convert an xls into a pdf.

sFileFullname = Mid(strFilename, InStrRev(strFilename, "\") + 1, 255)
'get full filename
sFileName = Left(sFileFullname, Len(sFileFullname) - 4) 'File Name
sExtension = Right(sFileFullname, 3) 'File Extension
sPath = Left(strFilename, InStrRev(strFilename, "\")) 'get path

PSFileName = sPath & sFileName & ".ps"
PDFFileName = sPath & sFileName & ".pdf"

Set objApp = CreateObject("Excel.Application")
With objApp
Application.Cursor = xlWait
.Visible = True
Set sSheet = Workbooks.Open(Sheets("parameters").Range("A250"))
End With

Dim MySheet As Worksheet
Set MySheet = ActiveSheet

'Select the PDF printer
MsgBox "Please select your Acrobat Printer from the available list of
printers", , "Which printer is your PDF Printer?"
bPrntSel = Application.Dialogs(xlDialogPrinterSetup).Show
' Application.Visible = False

'Create the PostScript File
If bPrntSel Then
MySheet.PrintOut ActivePrinter:=ActivePrinter, PrintToFile:=True,
PrToFileName:=PSFileName
'...continues on

It works beautifully in Excel 2000, but always generate a 1004 error in 2003
at the MySheet.PrintOut ..... line (Your file could not be printed due to an
error on ...: There are several possible reasons (not enough memory, network
printer etc…))?! Any advice would be greatly appreciated.

Thank you,

QB

 

[Joel]

It looks like the probem is a missing line continuation character

MySheet.PrintOut ActivePrinter:=ActivePrinter, _
PrintToFile:=True, _
PrToFileName:=PSFileName

 

[Question Boy]

Joel,

It is actually all on 1 line, it is simply the line wrapping from the forum.

I can't get it to print to a file? It doesn't even get to the pdf
conversion part of my code. Are there any changes between 2000 and 2003 that
would break the PrintOut Method?

Any other advice?

QB

 

[Francois via OfficeKB.com]

Hello,

The following code is extracted from a larger routine which purpor is to
print/convert an xls into a pdf.

sFileFullname = Mid(strFilename, InStrRev(strFilename, "\") + 1, 255)
'get full filename
sFileName = Left(sFileFullname, Len(sFileFullname) - 4) 'File Name
sExtension = Right(sFileFullname, 3) 'File Extension
sPath = Left(strFilename, InStrRev(strFilename, "\")) 'get path

PSFileName = sPath & sFileName & ".ps"
PDFFileName = sPath & sFileName & ".pdf"

Set objApp = CreateObject("Excel.Application")
With objApp
Application.Cursor = xlWait
.Visible = True
Set sSheet = Workbooks.Open(Sheets("parameters").Range("A250"))
End With

Dim MySheet As Worksheet
Set MySheet = ActiveSheet

'Select the PDF printer
MsgBox "Please select your Acrobat Printer from the available list of
printers", , "Which printer is your PDF Printer?"
bPrntSel = Application.Dialogs(xlDialogPrinterSetup).Show
' Application.Visible = False

'Create the PostScript File
If bPrntSel Then
MySheet.PrintOut ActivePrinter:=ActivePrinter, PrintToFile:=True,
PrToFileName:=PSFileName
'...continues on

It works beautifully in Excel 2000, but always generate a 1004 error in 2003
at the MySheet.PrintOut ..... line (Your file could not be printed due to an
error on ...: There are several possible reasons (not enough memory, network
printer etc…))?! Any advice would be greatly appreciated.

Thank you,

QB

I think that I would record a test Macro for just the bit that prints to PDF,
and then compare the recorded Macro to your existing one

 

[Joel]

The following code works without errors

PSFileName = "abc.txt"


Dim MySheet As Worksheet
Set MySheet = ActiveSheet

'Select the PDF printer
MsgBox "Please select your Acrobat Printer from the available list of
printers", , _
"Which printer is your PDF Printer?"
bPrntSel = Application.Dialogs(xlDialogPrinterSetup).Show
' Application.Visible = False

MySheet.PrintOut ActivePrinter:=ActivePrinter, PrintToFile:=True,
PrToFileName:=PSFileName

Maybe the printer you are selecting doesn't have a printtofile option?