Excel Macro

  • Thread starter Thread starter bstruchen
  • Start date Start date
B

bstruchen

I need to create a macro in excel that prints out multiple reports in
pdf format. Is there a way to hardcode the pdf file name so the save
as message box doen't appear??

Thanks
 
The following will save the file as you specify, not sure about the .pdf
format your require, but the message box will be suppressed

Application.DisplayAlerts = False
Workbook.SaveAs Filename:="path\filename"
Application.DisplayAlerts = True
 
Thanks for your help.

I wrote a macro see below. The problem is it only prints in black and
white and I need color.


Any ideas !!!

Sub Brian()

'
' PrintPDF2 Macro
' Macro recorded 12/16/2003 by bjstruch
'
Dim PSFileName As String
Dim PDFFileName As String
Dim DistillerCall As String

Dim ReturnValue As Variant

'Local folder to hold PSFileName and PDFFileName
PSFileName = "C:\temp\Brian.PS"

PDFFileName = "C:\temp\Brian.PDF"

'If exist then delete
If Dir(PSFileName) <> "" Then Kill (PSFileName)

If Dir(PDFFileName) <> "" Then Kill (PDFFileName)


SendKeys PSFileName & "{ENTER}", False


'This is where you set the active area

Application.Goto Reference:="TBLBDGR"

ActiveSheet.PageSetup.PrintArea = "$M$75:$S$131"

ActiveSheet.PrintOut , printtofile:=True


'Add " " to file names [Chr(34) = "]
PSFileName = Chr(34) & PSFileName & Chr(34)

PDFFileName = Chr(34) & PDFFileName & Chr(34)


'Call .exe to writ PS file to PDF format
DistillerCall = "C:\Program Files\Adobe\Acrobat
5.0\Distillr\Acrodist.exe" & _
" /n /q /c" & PDFFileName & " " & PSFileName

'" /n /q /o"

'If the application doesn't open correctly let us know about it
ReturnValue = Shell(DistillerCall, vbNormalFocus)

If ReturnValue = 0 Then MsgBox "Creation of" & PDFFileName & "failed."

'
End Sub
 
Back
Top