prompt for opening a save dialog for excel file using vb.net

Question

Iam using visual studio 2012,

i would like to open "save dialog" to choose where to save my file instead of using fixed path, the following code is a sample of what i would like to use it in:

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
    Dim xlsWorkBook As Microsoft.Office.Interop.Excel.Workbook
    Dim xlsWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
    Dim xls As New Microsoft.Office.Interop.Excel.Application

    Dim resourcesFolder = IO.Path.GetFullPath(Application.StartupPath & "\Resources\")
    Dim fileName = "book1.xlsx"

    xlsWorkBook = xls.Workbooks.Open(resourcesFolder & fileName)
    xlsWorkSheet = xlsWorkBook.Sheets("a")

    xlsWorkSheet.Cells(1, 1) = TextBox1.Text

    xlsWorkBook.SaveAs("C:\output\book1.xlsx")

    xlsWorkBook.Close()
    xls.Quit()


End Sub

i would like to change this path "C:\output\book1.xlsx" to save dialog, so i can choose where to save it manually.

thanks alot..

OneFineDay · Accepted Answer · 2014-01-18 21:29:01Z

3

Like this, don't forget to dispose of com objects with the Marshal class like I added.

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
 Dim xls As New Microsoft.Office.Interop.Excel.Application
 Dim xlsWorkBook As Microsoft.Office.Interop.Excel.Workbook
 Dim xlsWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
 Dim resourcesFolder = IO.Path.GetFullPath(Application.StartupPath & "\Resources\")
 Dim fileName = "book1.xlsx"
 xlsWorkBook = xls.Workbooks.Open(resourcesFolder & fileName)
 xlsWorkSheet = xlsWorkBook.Sheets("a")
 xlsWorkSheet.Cells(1, 1) = TextBox1.Text
 Using sfd As New SaveFileDialog
  If sfd.ShowDialog() = DialogResult.OK Then
   xlsWorkBook.SaveAs(sfd.FileName)
   MessageBox.Show(sfd.Filename)
  End If
 End Using
 xlsWorkBook.Close()
 xls.Quit()
 Marshal.FinalReleaseComObject(xlsWorkSheet)
 Marshal.FinalReleaseComObject(xlsWorkBook)
 Marshal.FinalReleaseComObject(xls)
End Sub

edited Jan 18, 2014 at 21:29

answered Jan 18, 2014 at 0:24

OneFineDay

9,0344 gold badges28 silver badges37 bronze badges

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

12 Comments

user3151946 Over a year ago

thanks alot, it's working .. I added the " Using sfd As New SaveFileDialog sfd.ShowDialog() xlsWorkBook.SaveAs(sfd.FileName) End Using " part only. please if you can clarify the marshal part, because i don't get it., also another simple thing, is there a way i can show a message box with the location i chose to save the file to.., thanks alot for your time

user3151946 Over a year ago

., any suggestion regarding the message box part (show a message box with the location i chose to save the file to)., thanks

OneFineDay Over a year ago

If you don't use the Marshal.FinalReleaseComObject you can find your excel still open as a process in the Task Manager.

user3151946 Over a year ago

., thanks for your clarification, i got your point & read about it & understand it totally..thanks, but the problem is it works with me only if i close the application first, but if i kept my application running the excel process is still there, so it will be great if you any method to get rid of this.

OneFineDay Over a year ago

Are you holding any instances of an Excel object anywhere - like class level?

|

Wayne · Accepted Answer · 2014-11-04 21:54:19Z

1

A little more comprehensive way to open the Save As Dialog than OneFineDay's answer (using the same method).
This opens the Save As dialog using a designated directory, filename, extension type, and window title; it also prompts before overwritting any existing files.

Dim dir as String = "C:\output\"
Dim fName As String = "Book1"

Using sfd As New SaveFileDialog
    sfd.InitialDirectory = dir
    sfd.Title = "Save As"
    sfd.OverwritePrompt = True
    sfd.FileName = fName
    sfd.DefaultExt = ".xlsx"
    sfd.Filter = "Excel Workbook(*.xlsx)|"
    sfd.AddExtension = True
    If sfd.ShowDialog() = DialogResult.OK Then
        xlsWorkBook.SaveAs(sfd.FileName)
    End If
End Using

answered Nov 4, 2014 at 21:54

Wayne

49710 silver badges17 bronze badges

Comments

Rob · Accepted Answer · 2014-01-18 00:23:53Z

0

Add an openfiledialog to your form and then ...

  With OpenFileDialog1
        .Title = " whatever"
        .InitialDirectory = "c:\"
        .Multiselect = False
        If .ShowDialog() = DialogResult.OK Then
            xlsWorkBook.SaveAs(.FileName)
        End If

End With

answered Jan 18, 2014 at 0:23

Rob

3,9533 gold badges37 silver badges29 bronze badges

Comments

Uli Köhler · Accepted Answer · 2014-02-17 03:11:29Z

0

You can use this :

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
    Dim xlsWorkBook As Microsoft.Office.Interop.Excel.Workbook
    Dim xlsWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
    Dim xls As New Microsoft.Office.Interop.Excel.Application

    Dim resourcesFolder = IO.Path.GetFullPath(Application.StartupPath & "\Resources\")
    Dim fileName = "book1.xlsx"

    xlsWorkBook = xls.Workbooks.Open(resourcesFolder & fileName)
    xlsWorkSheet = xlsWorkBook.Sheets("a")

    xlsWorkSheet.Cells(1, 1) = TextBox1.Text

    xlsWorkBook.SaveAs("C:\output\book1.xlsx")
    xls.Application.DisplayAlerts = False
    xlsWorkBook.Close()
    xls.Quit()


End Sub

edited Feb 17, 2014 at 3:11

Uli Köhler

13.9k17 gold badges75 silver badges127 bronze badges

answered Feb 17, 2014 at 2:52

user3317496

1

Collectives™ on Stack Overflow

prompt for opening a save dialog for excel file using vb.net

4 Answers 4

12 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

12 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related