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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RogerKMillerAuthor Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.