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

Question

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.

Barney · Accepted Answer · 2016-06-30 19:50:24Z

1

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

answered Jun 30, 2016 at 19:50

Barney

1,8952 gold badges23 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

equalizer Over a year ago

May want to set xlsApp.Visible = False right before show dialog so Excel is minimized because dialog may be under Excel window.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related