Need VBA help on saving a spreadsheet

[fred]

Hi all,


How do I design a saving macro with these requirements?


1. cell A1: Client Name (such as ACME, Inc.)

2. cell A2: Contract Date (such as Nov 15, 2010)

3. cell A3: Initial of the sales rep (such as AJ, or BK)

4. the spreadsheet is to be saved as a pdf file.


I'd like to click on a button (macro enabled) so that the file name would be like "(client name) quote as of (date) by (sales rep).pdf". E.g. "ACME, Inc quote as of Nov 15, 2010 by AJ.pdf".


The following was from MS 2003 and I'm using MS 2007. It always gives me error warning

or not working at all.


FileSaved = True

Enter_Prospect_Name = ActiveSheet.Range("b11") & "_Quote_" & ActiveSheet.Range("g1")

fileSaveName = Application.GetSaveAsFilename(Enter_Prospect_Name, fileFilter:="Excel Files (*.xls), *.xls")

If fileSaveName <> "False" Then

ActiveWorkbook.SaveAs Filename:=fileSaveName, FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False

End If

End Sub


Please help.

 

[xld]

Have you got Excel 2007, or some application for saving as PDF?

 

[fred]

I havae the application to save as a pdf. but I need a button to click on to save the file as a pdf without having to type everything.

 

[kchiba]

Hi


If you are using Excel 2007 replace the Save statement with the following.


You will have to put the proper Filename, where it say FileName.pdf.


You can build the name up into a vaiable and use the variable as the file name


ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _

"FileName.pdf", Quality _

:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _

OpenAfterPublish:=False

 

[fred]

I copied yours but it's not working. I don't know what i'm missing. I have tried tweaking it around but still no use.

 

[kchiba]

Hi,


You can either build the filename in the worksheet or in the VBA and then use the following macro:


Note I have embedded the file name in the macro, you must change that


Sub test()


Dim myFileName As String


myFileName = "ABCD.pdf"


FileSaved = True

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=myFileName, _

Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _

OpenAfterPublish:=False


End Sub

 

[xld]

What application to save as pdf do you have?


WHat version of Excel?