Excel 2003 - Save as dialog box

[VBA Noob]

Hi,

Using code I would like to open the save as dialog box, change the
path to which it saves and ideally change the save as type to PDF and
use the ThisWorkbook.Name

So far I've managed to open the dialog with the below but not sure how
to do the changing the path and file ext or if even possible for 2003.

Application.Dialogs(xlDialogSaveAs).Show

Any help would be appreciated

Danny

 

[Jim Cone]

As far as I know, Excel 2003 has no ability to save files as a pdf.
The following saves the file in the specified folder, as a template (.xlt), under the name of
Danny...

Application.Dialogs(xlDialogSaveAs).Show Arg1:="D:\Icons\Arrows\Danny", Arg2:=17 'template
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(Data Rows add-in: Custom Shading, Deleting, Inserting)




"VBA Noob" <[email protected]>
wrote in message
news:fc6b158b-2ef9-46bd-a0a3-5306aac62762@bl1g2000vbb.googlegroups.com...

 

[VBA Noob]

As far as I know, Excel 2003 has no ability to save files as a pdf.
The following saves the file  in the specified folder, as a template (.xlt), under the name of
Danny...

Application.Dialogs(xlDialogSaveAs).Show Arg1:="D:\Icons\Arrows\Danny",Arg2:=17 'template

Thanks for the reply Jim,

Maybe I've asked the wrong question here. My main goal is for the
excel workbook to be converted to PDF and saving would be a bonus.
Could I call the print dialog box and change printer name to their
version of PDF with something like Application.ActivePrinter = "?"

Do I need to known the version?

Danny
Danny

 

[GS]

VBA Noob presented the following explanation :

Thanks for the reply Jim,

Maybe I've asked the wrong question here. My main goal is for the
excel workbook to be converted to PDF and saving would be a bonus.
Could I call the print dialog box and change printer name to their
version of PDF with something like Application.ActivePrinter = "?"

Do I need to known the version?

Danny
Danny

You need to know the name and port. It might be easier to prompt the
user to set the active printer to the PDF printer the first time. This
will let you store the value for this user in the Registry via
SaveSetting, and retrieve it later via GetSetting. Have GetSetting
return a 'default' dummy string if the registry key is empty (or
doesn't exist). Testing for this will let your code determine if you
can set ActivePrinter or prompt the user to set it. Normally, I'd load
the ActivePrinter into a variable (sCurrentPrinter) at startup so I can
reset it after my code runs the PrintToPDF procedure.

HTH

 

[VBA Noob]

VBA Noob presented the following explanation :










You need to know the name and port. It might be easier to prompt the
user to set the active printer to the PDF printer the first time. This
will let you store the value for this user in the Registry via
SaveSetting, and retrieve it later via GetSetting. Have GetSetting
return a 'default' dummy string if the registry key is empty (or
doesn't exist). Testing for this will let your code determine if you
can set ActivePrinter or prompt the user to set it. Normally, I'd load
the ActivePrinter into a variable (sCurrentPrinter) at startup so I can
reset it after my code runs the PrintToPDF procedure.

HTH

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Thanks Garry,

Will try it tomorrow at work

Danny

 

[Jim Cone]

Another option is to display a list of available printers and have the user choose the printer to
use.
Selecting an item changes the active printer...
'---
Sub PrinterSelectionDemo()
Dim bChoice As Boolean
bChoice = Application.Dialogs(xlDialogPrinterSetup).Show(ActivePrinter)
If Not bChoice Then
MsgBox "User cancelled"
Else
MsgBox ActivePrinter
End If
End Sub

 

[GS]

Jim Cone laid this down on his screen :

Another option is to display a list of available printers and have the user
choose the printer to use.
Selecting an item changes the active printer...
'---
Sub PrinterSelectionDemo()
Dim bChoice As Boolean
bChoice = Application.Dialogs(xlDialogPrinterSetup).Show(ActivePrinter)
If Not bChoice Then
MsgBox "User cancelled"
Else
MsgBox ActivePrinter
End If
End Sub

Nice solution, Jim! Never thought of it myself but this clearly is the
best solution in the end. Thanks for sharing this...

 

[VBA Noob]

Jim Cone laid this down on his screen :


Nice solution, Jim! Never thought of it myself but this clearly is the
best solution in the end. Thanks for sharing this...

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Thanks Jim,

I came up with this idea but user then wanted the tick whole workbook
option but can tweak you're code to do that.
If Application.ActivePrinter Like "*pdf*" Then
ActiveWorkbook.PrintOut Copies:=1
Else
Application.Dialogs(xlDialogPrint).Show
End If

Thanks again to both of you.

Danny

 

[GS]

VBA Noob formulated on Tuesday :

Thanks Jim,

I came up with this idea but user then wanted the tick whole workbook
option but can tweak you're code to do that.
If Application.ActivePrinter Like "*pdf*" Then
ActiveWorkbook.PrintOut Copies:=1
Else
Application.Dialogs(xlDialogPrint).Show
End If

Thanks again to both of you.

Danny

Why not just show the dialog at all times? Then the user can choose
printer AND what to print all in one place!

 

[VBA Noob]

VBA Noob formulated on Tuesday :










Why not just show the dialog at all times? Then the user can choose
printer AND what to print all in one place!

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Garry,

The whole workbook needs to be printed as a PDF. Ideally I would
automatically want it to select the users PDF printer but different
users may have different versions so not sure if this is possible?
Jim's solution is close enough as whatever printer they select I will
code it so it will automatically priint the entire workbook so save
the user one last click.

Danny

Danny