Excel Macro

[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

 

[Nigel]

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

 

[bstruchen]

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