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

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"
wrote in message
...
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

On Aug 15, 7:42*pm, "Jim Cone" wrote:
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 USAhttp://www.mediafire.com/PrimitiveSoftware
(Data Rows add-in: Custom Shading, Deleting,

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

VBA Noob presented the following explanation :
On Aug 15, 7:42*pm, "Jim Cone" wrote:
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 USAhttp://www.mediafire.com/PrimitiveSoftware
(Data Rows add-in: Custom Shading, Deleting,

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

--
Garry

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

On Aug 15, 9:39*pm, GS wrote:
VBA Noob presented the following explanation :





On Aug 15, 7:42 pm, "Jim Cone" wrote:
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 USAhttp://www.mediafire.com/PrimitiveSoftware
(Data Rows add-in: Custom Shading, Deleting,

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

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

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(Act ivePrinter)
If Not bChoice Then
MsgBox "User cancelled"
Else
MsgBox ActivePrinter
End If
End Sub

--
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
(free and commercial excel programs)