save the file in Excel format

[Busbait]

Hi,

The below code will allow the user to open the Save As dialog box,

Now my question is : after the user specify the file name and the path, how can I modify the below code in order to save the file in Excel format when the user click on the Save button

What I need is to use the file name which was specified by the user in the dialog box , and then create an Excel file using the same name & path and the new excel file should not contain any data just empty worksheet

Private Sub Command16_Click()
 
    Dim fd As FileDialog
    Dim vrtSelectedItem As Variant
 
    Set fd = Application.FileDialog(msoFileDialogSaveAs)
 
      With fd
 
        If .Show = -1 Then
 
            For Each vrtSelectedItem In .SelectedItems
 
                MsgBox "The path is: " & vrtSelectedItem
 
            Next vrtSelectedItem
 
        Else
        End If
    End With
 
    Set fd = Nothing
 
End Sub

[Stewart Ross]

Hi, and Welcome to Bytes!

It is not clear from your question what you are doing with your file selections. If you are using the Office file dialog in multi-select mode you must be expecting users to select one or more existing file names from a file directory in a folder somewhere. If that is the case, saving these files in Excel format will depend on what the type of the file is that your users have selected - and you don't tell us that at all.

If what you really mean is that you are asking users to specify a filename to save some existing data from Access, say, in Excel format then that is quite a different matter - you would not need the file dialog in multi-select mode for that. The file dialog would be selecting the destination folder, not multiple instances of files I would have thought.

Without clarification of what you really want to happen on what data it is difficult to advise you further. In Access there is the DoCmd.TransferS preadsheet method which can output Access queries in Excel format to a given path and filename but I can't tell from your question if this is what you need or not.

If you are working in Excel itself you can add a new blank workbook using the .Add method of the Workbooks collection, then use the .SaveAs method to save the blank workbook under whatever name is required. If you want to have multiple blank files you would do this in some kind of loop, but the multi-select filedialog options don't seem to me to be the right source as these apply to existing files in a directory.

-Stewart

[ADezii]

I too, like Stewart, am confused by your request. My interpretation is that you wish to simply create a Blank Excel Spreadsheet from a predetermined Path. If this is True, I'll post a general template that you can use, if not then you must provide additional, more specific, information.

'Must Set a Reference to the Microsoft Excel XX.X Object Library
Dim appExcel As Excel.Application
Dim wBook As Workbook
 
If IsNull(Me![txtFilePath]) Then Exit Sub
 
Set appExcel = New Excel.Application
Set wBook = appExcel.Workbooks.Add
 
wBook.SaveAs Me![txtFilePath]
 
appExcel.Quit
Set appExcel = Nothing

[Busbait]

ADezii

“Create a Blank Excel Spreadsheet from a predetermined Path” ………. This is exactly what I was looking for

Thanks ADezii and Stewart

[ADezii]

Just out of curiosity, Busbait, what exactly are you trying to accomplish?