CODEWITHSUNDEEP
excelvbams-word
Pri Santos
Pri Santos

Reputation: 409

How to save a excel file from a word file and choose path using VBA?

I have a macro that get data from a word file and writes it into an excel file and saves it to a specific location. I want the user to be able to choose where to save the file.

This is my current code:

 Sub createExcelFile()
        Dim mPathSave As String
        Dim xlsApp As Excel.Application
        Dim xlsWB As Workbook

        Set xlsApp = CreateObject("Excel.Application")
        Set xlsWB = xlsApp.Workbooks.Add

        'Want to make it dynamic'
        mPathSave = "C:\temp"

        callFunc = createExcel.createExcel(xlsApp, xlsWB)

        'Save the excel file
        xlsWB.SaveAs mPathSave & "\" & "teste" & ".xls", FileFormat:=56
        xlsWB.Close
        xlsApp.Quit
        MsgBox "Novo arquivo salvo em: " & mPathSave & "\" & "teste" & ".xls", vbInformation
    End Sub

I tried to use Application.FileDialog to open the dialog to choose the place, but I can't make it save a excel, it opens to save a word file.

Upvotes: 0

Views: 363

Answers (1)

Barney
Barney

Reputation: 1895

Here is a simple example

Sub createExcelFile()
    Dim mPathSave As String
    Dim xlsApp As Excel.Application
    Dim xlsWB As Workbook

    Set xlsApp = CreateObject("Excel.Application")
    xlsApp.Visible = True
    Set xlsWB = xlsApp.Workbooks.Add

    'Want to make it dynamic'
    Application.FileDialog(msoFileDialogFolderPicker).Show
    mPathSave = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1)

    'Save the excel file
    xlsWB.SaveAs mPathSave & "\" & "teste" & ".xls", FileFormat:=56
    xlsWB.Close
    xlsApp.Quit
End Sub

Upvotes: 1

Related Questions