using VBA in Excel, I'd like to be able to add an additional name to the file when saving as Cute PDF

I'm using Excel macro to give the choice to print with an actual printer or with Cute PDF writer.  When PDF is selected a dialog box pops up to change the name of the file and the location to save the pdf file.  The name pops up as the name of the workbook.  I'd like to have the macro specify the date and day of week as the file name, not the workbook name, and to have it automatically save into a given location without the operator having to decide this.
RogerKMillerAsked:
Who is Participating?
 
dlmilleCommented:
quick path - put a wait in for a few seconds then use VBA's statement SendKeys to send the characters to the PDF app to hit the keys to complete the save operation.

Dave
0
 
SmittyProCommented:
Can you post the code that you've got now?

This might be a start:

ActiveWorkbook.SaveAs Filename:="Drive:\Path\" & Format(Date, "DDDD, MM-DD-YYYY")

HTH,
0
 
RogerKMillerAuthor Commented:
SmittyPro:
Here's that part of the code:
LinePDF:
Range(Cells(1, 1), Cells(DFND, 78)).Select
With Selection
    Application.ActivePrinter = "CutePDF Writer on CPW2:"
    Selection.PrintOut Copies:=1, Collate:=True
    ActiveSheet.PageSetup.PrintArea = ""
End With
'
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
SmittyProCommented:
Check out Malcom's code on setting printer selections:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=528

If I understand you, if the user selects the PDF option, they should be able to browse to where to save the document; if they go the workbook route, it should be saved where you specify?

Here's some code that covers the GetSaveAsFileName method, which will let you set the default file path and name, but still let the user change it.  You can tie that to the PDF portion.  If it's not the PDF selection you can just use the code I posted earlier to directly save it without user input.

Sub GetSaveAsFileName()
    Dim FileName As Variant
    Dim Filt As String, Title As String
    Dim FilterIndex As Long, Response As Long
    '   Set to Specified Path\Folder
        ChDir "C:\Documents and Settings\All Users\Desktop\"
    '   Set File Filter
        Filt = "Excel Files (*.xls), *.xls"
    '   Set *.* to Default
        FilterIndex = 5
    '   Set Dialogue Box Caption
        Title = "Please select a different File"
    '   Get FileName
        FileName = Application.GetSaveAsFileName(InitialFileName:=Range("A1"), FileFilter:=Filt, _
            FilterIndex:=FilterIndex, Title:=Title)
    '   Exit if Dialogue box cancelled
        If FileName = False Then
            Response = MsgBox("No File was selected", vbOKOnly & vbCritical, "Selection Error")
            Exit Sub
        End If
    '   Display Full Path & File Name
        Response = MsgBox("You selected " & FileName, vbInformation, "Proceed")
    '   Save & Close Workbook
        With ActiveWorkbook
            .SaveAs FileName
            .Close
        End With
            
End Sub

Open in new window

0
 
ltlbearand3Commented:
The  problem is that cute PDF is what is displaying the save as dialog box to save the PDF file name and not Excel.  It defaults to the name of the excel spreadsheet.  Excel VBA cannot really control that save as box at all.  One option is to save the workbook as a new file with the information you want (See SmittyPro's first post).  Another option is to upgrade your version of office as office 2007 and office 2010 both have built in PDF converters.  

-Bear
0
 
SmittyProCommented:
You might want to check out Ken Puls' PDF article, although it doesn't apply to CutePDF, it might give you some good ideas on the naming part:

http://www.excelguru.ca/node/21
0
 
RogerKMillerAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.