Hello All,

I have a macro that imports a text file, does some manipulations and, in the
end, displays the SaveAs dialog box. The default "Save as type" is "Text(Tab
Delimited)". However, the user is always going to want this changed to
"Microsoft Excel Workbook".

Does anyone know how to change the "Save as type" to be "Microsoft Excel
Workbook" using VBA?

Any help would be much appreciated.

Thanks,

Trevor

Trevor,

SaveAs has a FileFormat property. However, the help says ... For an existing
file, the default format is the last file format specified; for a new file,
the default is the format of the version of Excel being used. ... so it
should default to Excel file. If you want to force it, set it to
xlWorkbookNormal.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jesterhoz" wrote in message
...
Hello All,

I have a macro that imports a text file, does some manipulations and, in
the
end, displays the SaveAs dialog box. The default "Save as type" is
"Text(Tab
Delimited)". However, the user is always going to want this changed to
"Microsoft Excel Workbook".

Does anyone know how to change the "Save as type" to be "Microsoft Excel
Workbook" using VBA?

Any help would be much appreciated.

Thanks,

Trevor

Thanks for responding, Bob.

I only have one concern, though. I am using the following:

Application.Dialogs(xlDialogSaveAs).Show

This opens the SaveAs Dialog box and allows the user to navigate to a folder
of choice and save the file with whatever name they choose. How can I
incorporate the FileFormat property into this call?

Thanks,

Trevor

"Bob Phillips" wrote:

Trevor,

SaveAs has a FileFormat property. However, the help says ... For an existing
file, the default format is the last file format specified; for a new file,
the default is the format of the version of Excel being used. ... so it
should default to Excel file. If you want to force it, set it to
xlWorkbookNormal.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jesterhoz" wrote in message
...
Hello All,

I have a macro that imports a text file, does some manipulations and, in
the
end, displays the SaveAs dialog box. The default "Save as type" is
"Text(Tab
Delimited)". However, the user is always going to want this changed to
"Microsoft Excel Workbook".

Does anyone know how to change the "Save as type" to be "Microsoft Excel
Workbook" using VBA?

Any help would be much appreciated.

Thanks,

Trevor

Jesterhoz,
Unless anyone can help you control Excel's dialog, you could use the API
route and only give .xls as a possible format.
Check out the GetSaveFileName api.

NickHK

"Jesterhoz" wrote in message
...
Thanks for responding, Bob.

I only have one concern, though. I am using the following:

Application.Dialogs(xlDialogSaveAs).Show

This opens the SaveAs Dialog box and allows the user to navigate to a
folder
of choice and save the file with whatever name they choose. How can I
incorporate the FileFormat property into this call?

Thanks,

Trevor

"Bob Phillips" wrote:

Trevor,

SaveAs has a FileFormat property. However, the help says ... For an
existing
file, the default format is the last file format specified; for a new
file,
the default is the format of the version of Excel being used. ... so it
should default to Excel file. If you want to force it, set it to
xlWorkbookNormal.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jesterhoz" wrote in message
...
Hello All,

I have a macro that imports a text file, does some manipulations and,
in
the
end, displays the SaveAs dialog box. The default "Save as type" is
"Text(Tab
Delimited)". However, the user is always going to want this changed
to
"Microsoft Excel Workbook".

Does anyone know how to change the "Save as type" to be "Microsoft
Excel
Workbook" using VBA?

Any help would be much appreciated.

Thanks,

Trevor

GetSaveAsFileName (not GetSaveFileName) is a method not an API. There is
equivalent APIs, but they are much more complex to implement.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"NickHK" wrote in message
...
Jesterhoz,
Unless anyone can help you control Excel's dialog, you could use the API
route and only give .xls as a possible format.
Check out the GetSaveFileName api.

NickHK

"Jesterhoz" wrote in message
...
Thanks for responding, Bob.

I only have one concern, though. I am using the following:

Application.Dialogs(xlDialogSaveAs).Show

This opens the SaveAs Dialog box and allows the user to navigate to a
folder
of choice and save the file with whatever name they choose. How can I
incorporate the FileFormat property into this call?

Thanks,

Trevor

"Bob Phillips" wrote:

Trevor,

SaveAs has a FileFormat property. However, the help says ... For an
existing
file, the default format is the last file format specified; for a new
file,
the default is the format of the version of Excel being used. ... so
it
should default to Excel file. If you want to force it, set it to
xlWorkbookNormal.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jesterhoz" wrote in message
...
Hello All,

I have a macro that imports a text file, does some manipulations
and,
in
the
end, displays the SaveAs dialog box. The default "Save as type" is
"Text(Tab
Delimited)". However, the user is always going to want this changed
to
"Microsoft Excel Workbook".

Does anyone know how to change the "Save as type" to be "Microsoft
Excel
Workbook" using VBA?

Any help would be much appreciated.

Thanks,

Trevor

Bob,
I did intend the API route rather than the built-in version, as it's what I
am used to.
Granted the built-in may be less involved.

NickHK

"Bob Phillips" wrote in message
...
GetSaveAsFileName (not GetSaveFileName) is a method not an API. There is
equivalent APIs, but they are much more complex to implement.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"NickHK" wrote in message
...
Jesterhoz,
Unless anyone can help you control Excel's dialog, you could use the API
route and only give .xls as a possible format.
Check out the GetSaveFileName api.

NickHK

"Jesterhoz" wrote in message
...
Thanks for responding, Bob.

I only have one concern, though. I am using the following:

Application.Dialogs(xlDialogSaveAs).Show

This opens the SaveAs Dialog box and allows the user to navigate to a
folder
of choice and save the file with whatever name they choose. How can I
incorporate the FileFormat property into this call?

Thanks,

Trevor

"Bob Phillips" wrote:

Trevor,

SaveAs has a FileFormat property. However, the help says ... For an
existing
file, the default format is the last file format specified; for a
new
file,
the default is the format of the version of Excel being used. ...
so
it
should default to Excel file. If you want to force it, set it to
xlWorkbookNormal.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jesterhoz" wrote in message
...
Hello All,

I have a macro that imports a text file, does some manipulations
and,
in
the
end, displays the SaveAs dialog box. The default "Save as type"
is
"Text(Tab
Delimited)". However, the user is always going to want this
changed
to
"Microsoft Excel Workbook".

Does anyone know how to change the "Save as type" to be "Microsoft
Excel
Workbook" using VBA?

Any help would be much appreciated.

Thanks,

Trevor

Use
Dim sName as String, fName as String
sName = activeworkbook.name
sName = left(sName,len(sName)-4) & ".xls"
fName = Application.GetSaveAsFilename(InitialFilename:=sNa me, _
filefilter:="xls Files (*.xls), *.xls")
if fName < "False" then
activeworkbook.SaveAs fName, xlWorkbookNormal
else
msgbox "You clicked cancel"
exit sub
End if

GetSaveAsFilename puts up the same dialog, but only returns the fully
qualified filename selected. You then do the save, having full control of
the format.

--
Regards,
Tom Ogilvy




"Jesterhoz" wrote in message
...
Thanks for responding, Bob.

I only have one concern, though. I am using the following:

Application.Dialogs(xlDialogSaveAs).Show

This opens the SaveAs Dialog box and allows the user to navigate to a
folder
of choice and save the file with whatever name they choose. How can I
incorporate the FileFormat property into this call?

Thanks,

Trevor

"Bob Phillips" wrote:

Trevor,

SaveAs has a FileFormat property. However, the help says ... For an
existing
file, the default format is the last file format specified; for a new
file,
the default is the format of the version of Excel being used. ... so it
should default to Excel file. If you want to force it, set it to
xlWorkbookNormal.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jesterhoz" wrote in message
...
Hello All,

I have a macro that imports a text file, does some manipulations and,
in
the
end, displays the SaveAs dialog box. The default "Save as type" is
"Text(Tab
Delimited)". However, the user is always going to want this changed
to
"Microsoft Excel Workbook".

Does anyone know how to change the "Save as type" to be "Microsoft
Excel
Workbook" using VBA?

Any help would be much appreciated.

Thanks,

Trevor

Much obliged, gentlemen. Your suggestions worked like a charm.

Thanks.

"Tom Ogilvy" wrote:

Use
Dim sName as String, fName as String
sName = activeworkbook.name
sName = left(sName,len(sName)-4) & ".xls"
fName = Application.GetSaveAsFilename(InitialFilename:=sNa me, _
filefilter:="xls Files (*.xls), *.xls")
if fName < "False" then
activeworkbook.SaveAs fName, xlWorkbookNormal
else
msgbox "You clicked cancel"
exit sub
End if

GetSaveAsFilename puts up the same dialog, but only returns the fully
qualified filename selected. You then do the save, having full control of
the format.

--
Regards,
Tom Ogilvy




"Jesterhoz" wrote in message
...
Thanks for responding, Bob.

I only have one concern, though. I am using the following:

Application.Dialogs(xlDialogSaveAs).Show

This opens the SaveAs Dialog box and allows the user to navigate to a
folder
of choice and save the file with whatever name they choose. How can I
incorporate the FileFormat property into this call?

Thanks,

Trevor

"Bob Phillips" wrote:

Trevor,

SaveAs has a FileFormat property. However, the help says ... For an
existing
file, the default format is the last file format specified; for a new
file,
the default is the format of the version of Excel being used. ... so it
should default to Excel file. If you want to force it, set it to
xlWorkbookNormal.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jesterhoz" wrote in message
...
Hello All,

I have a macro that imports a text file, does some manipulations and,
in
the
end, displays the SaveAs dialog box. The default "Save as type" is
"Text(Tab
Delimited)". However, the user is always going to want this changed
to
"Microsoft Excel Workbook".

Does anyone know how to change the "Save as type" to be "Microsoft
Excel
Workbook" using VBA?

Any help would be much appreciated.

Thanks,

Trevor

Hello,

See the VBE file for GetSaveAsFilename Method.

http://msdn.microsoft.com/library/en...HV05201563.asp

Regards,
Nate Oliver