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

 

[jasmith4]

Several things:

• You can search either the Help (F1) or the Object Browser (F2) for "XLFileFormat" and you'll get a list.
• ActiveWorkbook.FileFormat and ActiveWorkbook.IsAddin will tell you about the active workbook.
• 56 is xlExcel8, which I believe is Excel v97-2003 (XLS extension). Use the symbol, not the constant!

 

[Cdmassey987]

What about file location with this procedure, can you set a default save as location such as a network folder...

I am using this process. The below code forces whatever the current filename is to populate in the filename box and then the file type to macro enabled workbook which is what I want.

If Application.Dialogs(xlDialogSaveAs).Show(ThisWorkbook.Name, 52) Then

But what about location?

I have been having issues with default location and Network Folders as the default save as location. The code doesn't seem to work right and usually gives me an error or just fails miserably and saves to my user profile, so I just abandoned it and allowed it to use whatever default location it wanted. That works well once you have an existing file and location; however, I want to build this into a template, so when I want to create a new file, the file I want to save, it tends to attempt to save on my user profile, but I don't want that. I want it to default to a specific network location where the user can then drill down into another folder if they want.

 

[MARK858]

One way.. obviously change the path to suit

Sub Test()
    Application.Dialogs(xlDialogSaveAs).Show ("C:\Users\MARK858\Desktop\" & ThisWorkbook.Name), Arg2:=52
End Sub

 

[Cdmassey987]

Works perfectly...not sure what my issue was before. Thank you very much Mark858!

 

[MARK858]

You're welcome