Solved

Save Dialog Box & Writing Dataset to Excel

Posted on 2004-04-01
2
1,125 Views
Last Modified: 2008-01-09
Hello,

I found two pieces of code:

1) opens a "Save as" dialog box.
2) The other copies a dataset to excel.

I am trying to combine both of these codes so that I will get a prompt to tell the app where to save the file then have the dataset copied into Excel  properly, then open the file I just saved in Excel.

To open dialog box:

            Dim objSaveDialog As New System.Windows.Forms.SaveFileDialog()

            'If the user omits Extension, add for them. Specify
            'the default extension to use as well.
            objSaveDialog.AddExtension = True
            objSaveDialog.DefaultExt = ".xls"

            'Prompt the user to create a file if it doesn't exist or overwrite if it does exist.
            objSaveDialog.CreatePrompt = False
            objSaveDialog.OverwritePrompt = True

            'Show the help button
            objSaveDialog.ShowHelp = True

            'Set the Initial Directory
            objSaveDialog.InitialDirectory = "C:\"

            'Set the Filter of File types to be opened
            objSaveDialog.Filter = "xls files (*.xls)|*.xml|" & _
                "All files (*.*)|*.*"

            'Set the Default Filter Index to the first item in the .Filter
            objSaveDialog.FilterIndex = 1

            'Verify the file and path exist
            objSaveDialog.CheckPathExists = True

            'Return the path of the file a shortcut references if it is a shortcut .lnk file the dialog found.
            'Set to False to return the actual location of the
            '.lnk file instead.
            objSaveDialog.DereferenceLinks = True

            'Initial filename to be used
            objSaveDialog.FileName = "test.xls"

            'Title of the Dialog
            objSaveDialog.Title = "Save As"

            'Allow only Valid File Names (no ",|,<,> characters)
            objSaveDialog.ValidateNames = True

            'If you want the dialog to open using the last directory you were in, then set this to
            'False and comment the .InitialDirectory.
            objSaveDialog.RestoreDirectory = False

            If objSaveDialog.ShowDialog() = DialogResult.OK Then
                'Open the file as a stream to read or write
                Dim fileStream As System.IO.Stream
                'Open Read/write file
                '************************
                ' CODE GOES IN HERE?

                '************************

                fileStream = objSaveDialog.OpenFile() 'Create the FileStream to write with.

 
            Else
            End If

*************
*************
*************
Code to write to Excel.

            Dim objExcel As Object
            Dim objBook As Object
            Dim objSheet As Object
            objExcel = CreateObject("Excel.Application")
            objBook = objExcel.Workbooks.Add
            objSheet = objBook.Worksheets(1)

            'Set up the Query Table and tell it where to find the data.
            Dim objQryTable As Object
            objQryTable = objSheet.QueryTables.Add("OLEDB;Provider=sqloledb;Data Source=" & strSQLReturn & _
                ";Initial Catalog=" & lstDatabases.SelectedItem & _
                ";Trusted Connection=Yes;Integrated Security=SSPI;", objSheet.Range("A1"), strSQL)
            objQryTable.RefreshStyle = 2 ' x1InsertEntire Rows = 2
            objQryTable.Refresh(False)

            'sPath is a string containing the location to save the file to
            objBook.SaveAs("C:\test.xls")
            'Clear everything so you can display it to the user
            objQryTable = Nothing
            objSheet = Nothing
            objBook = Nothing
            objExcel.Quit()
            objExcel = Nothing

            MsgBox("File exported to 'C:\test.xls'. Microsoft Excel is now opening.", MsgBoxStyle.Information)

            'This will open Excel with the new workbook you just created. This step is not necessary, but I find it nice.
            Dim xlApp As Excel.Application
            Dim xlMappe As Excel.Workbook
            xlApp = New Excel.Application()
            xlApp.Visible = True
            xlMappe = xlApp.Workbooks.Open("C:\test.xls")

Thanks in advance!.
0
Comment
Question by:Trancedified
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 15

Accepted Solution

by:
Timbo87 earned 500 total points
ID: 10736469
           Dim objSaveDialog As New System.Windows.Forms.SaveFileDialog()

            'If the user omits Extension, add for them. Specify
            'the default extension to use as well.
            objSaveDialog.AddExtension = True
            objSaveDialog.DefaultExt = ".xls"

            'Prompt the user to create a file if it doesn't exist or overwrite if it does exist.
            objSaveDialog.CreatePrompt = False
            objSaveDialog.OverwritePrompt = True

            'Show the help button
            objSaveDialog.ShowHelp = True

            'Set the Initial Directory
            objSaveDialog.InitialDirectory = "C:\"

            'Set the Filter of File types to be opened
            objSaveDialog.Filter = "xls files (*.xls)|*.xml|" & _
                "All files (*.*)|*.*"

            'Set the Default Filter Index to the first item in the .Filter
            objSaveDialog.FilterIndex = 1

            'Verify the file and path exist
            objSaveDialog.CheckPathExists = True

            'Return the path of the file a shortcut references if it is a shortcut .lnk file the dialog found.
            'Set to False to return the actual location of the
            '.lnk file instead.
            objSaveDialog.DereferenceLinks = True

            'Initial filename to be used
            objSaveDialog.FileName = "test.xls"

            'Title of the Dialog
            objSaveDialog.Title = "Save As"

            'Allow only Valid File Names (no ",|,<,> characters)
            objSaveDialog.ValidateNames = True

            'If you want the dialog to open using the last directory you were in, then set this to
            'False and comment the .InitialDirectory.
            objSaveDialog.RestoreDirectory = False

            If objSaveDialog.ShowDialog() = DialogResult.OK Then
                'Open the file as a stream to read or write
            Dim objExcel As Object
            Dim objBook As Object
            Dim objSheet As Object
            objExcel = CreateObject("Excel.Application")
            objBook = objExcel.Workbooks.Add
            objSheet = objBook.Worksheets(1)

            'Set up the Query Table and tell it where to find the data.
            Dim objQryTable As Object
            objQryTable = objSheet.QueryTables.Add("OLEDB;Provider=sqloledb;Data Source=" & strSQLReturn & _
                ";Initial Catalog=" & lstDatabases.SelectedItem & _
                ";Trusted Connection=Yes;Integrated Security=SSPI;", objSheet.Range("A1"), strSQL)
            objQryTable.RefreshStyle = 2 ' x1InsertEntire Rows = 2
            objQryTable.Refresh(False)

            'sPath is a string containing the location to save the file to
            objBook.SaveAs(objSaveDialog.FileName)
            'Clear everything so you can display it to the user
            objQryTable = Nothing
            objSheet = Nothing
            objBook = Nothing
            objExcel.Quit()
            objExcel = Nothing

            MsgBox("File exported to " & objSaveDialog.FileName & " Microsoft Excel is now opening.", MsgBoxStyle.Information)

            'This will open Excel with the new workbook you just created. This step is not necessary, but I find it nice.
            Dim xlApp As Excel.Application
            Dim xlMappe As Excel.Workbook
            xlApp = New Excel.Application()
            xlApp.Visible = True
            xlMappe = xlApp.Workbooks.Open(objSaveDialog.FileName)

            Else
            ' other code if they don't save
            End If
0
 
LVL 1

Author Comment

by:Trancedified
ID: 10736637
Great!

Except I forgot to change:

            'Set the Filter of File types to be opened
            objSaveDialog.Filter = "xls files (*.xls)|*.xml|" & _
                "All files (*.*)|*.*"

To

            'Set the Filter of File types to be opened
            objSaveDialog.Filter = "xls files (*.xls)|*.xls|" & _
                "All files (*.*)|*.*"
0

Featured Post

Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Summary Displaying images in RichTextBox is a common requirement with limited solutions available. Pasting through clipboard or embedding into RTF content only support static images.  This article describes how to insert Windows control objects int…
The object model of .Net can be overwhelming at times – so overwhelming that quite trivial tasks often take hours of research. In this case, the task at hand was to populate the datagrid from SQL Server database in Visual Studio 2008 Windows applica…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question