xlSaveAsdialog changing save as file type

[Fritz]

Hi

As usual the Excel VBA help is less than helpful!

I have a macro that opens a txt file and formats it and I want to save it as an Excel file.

The help in VBA says there is an argument type_num:

xlDialogSaveAs document_text, type_num, prot_pwd, backup, write_res_pwd, read_only_rec

but I cannot find out the values that relate to this argument.

What value do I need for this argument to change it to "Microsoft Excel Workbook (*.xls)"?

Where can I find the list of values for these arguments?

Thanks

 

[P Sitaram]

xlDialogSaveAs is used as an argument to bring up the Save as Dialog box. This may not be relevant to what you are trying to do. Copy the text file to a new workbook and use the Save or SaveAs method to save the file with the desired name.

 

[Fritz]

It seems to be working nearly how I want it to work. The macro reads in a file, always from the same FTP folder then formats it and opens the save as dialog box with a filename based on a the date. The only problem is that because the file opened was a txt file, when the save as dialog opens it stays as a txt file type. What I am trying to do is force the dialog to can the type to xls so it is saved as a workbook.

I can't use save as because different users will want to save the finished workbook in different places - it can't be saved in the orginal directory.

 

[P Sitaram]

Try this link:

http://support.microsoft.com/default.aspx?scid=kb;en-us;213371

 

[Fritz]

To help anyone who searches this board in the future with this problem, the solution was:

Application.Dialogs(xlDialogSaveAs).Show sFilename, xlWorkbook

I also found that I could force the change with:

xlCSV
xlExcel5
xlTemplate
xlText

Why Microsoft don't list these in their VBA help I don't know!

 

[jasmith4]

I found some Excel-2007-specific documentation for the type_num argument of the xlDialogSaveAs dialog, but here's an easy way to see what's what: just run this code:

With Application.Dialogs(xlDialogSaveAs)

For I& = 0 To 50000

On Error Resume Next
.Show "type_num =" & Str$(I&), I&
On Error GoTo 0
​

Next
​

End With

NOTE: You'll find in 2003 that there's no type_num for an add-in! But Excel MVP Andy Pope (www.andypope.com) gets credit for showing me how to save a workbook as an add-in in 2003:

Sub MakeAddInOfThisFile()

Const AddInExt As String = "xla" ' Change this to "xlam" for 2007

Dim objAddIn As AddIn
Dim strAddIn As String
Dim AddInInstalled As Boolean

' Construct path and name for the add-in
strAddIn = Application.UserLibraryPath _
& Application.PathSeparator _
& Left(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, ".")) _
& AddInExt

AddInInstalled = False ' Uninstall it if it's already installed
For Each objAddIn In Application.AddIns : With objAddIn

If .FullName = strAddIn Then

AddInInstalled = .Installed ' Track whether it's installed
.Installed = False ' Uninstall it
Exit For
​

End If
​

End With : Next

ThisWorkbook.IsAddin = True ' Make this workbook an add-in
ThisWorkbook.SaveCopyAs strAddIn ' Save copy of it as an add-in
ThisWorkbook.IsAddin = False ' Revert this workbook to normal

' Restore updated add-in and installation status
Application.AddIns.Add(strAddIn).Installed = AddInInstalled
​

End Sub

 

[evilbrent]

Thanks, this helped a lot.

To anyone searching the board again - use:

Application.Dialogs(xlDialogSaveAs).Show , xlTextWindows

if saving to a text file.

There is no filetype such as xlText

To help anyone who searches this board in the future with this problem, the solution was:

Application.Dialogs(xlDialogSaveAs).Show sFilename, xlWorkbook

I also found that I could force the change with:

xlCSV
xlExcel5
xlTemplate
xlText

Why Microsoft don't list these in their VBA help I don't know!

 

[mikerickson]

I don't know why MS doesn't list them. But I just use trial and error to find the file type that I want.
Warning! there are breaks in the list

Application.Dialogs(xlDialogSaveAs).Show "test", 3 works (.txt)
Application.Dialogs(xlDialogSaveAs).Show "test", 6 works (/csv)
Application.Dialogs(xlDialogSaveAs).Show "test", 6 doesn't
Application.Dialogs(xlDialogSaveAs).Show "test", 6 doesn't

 

[evilbrent]

XlFileFormat Enumeration

 

[drsthlm]

Hi all! Sorry for bringing this thread alive again - but I couldnt help myself when I discovered how easy it was to find out current Workbook format - if you just write this code in the direct-window you will recieve a number corresponding to active workbooks format - which can be used as formatspecification, instead of xlExcel5. This helped me to find out the xls-format for Office 2003 (56).

?ThisWorkbook.FileFormat

gave me the answer 56. Wich I then used in my actual code:

Application.Dialogs(xlDialogSaveAs).Show ThisWorkbook.Name, 56

Cheers!
/drsthlm