[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Save Dialog Box & Writing Dataset to Excel

Posted on 2004-04-01
2
Medium Priority
?
1,147 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
2 Comments
 
LVL 15

Accepted Solution

by:
Timbo87 earned 2000 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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

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

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 my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
This video tutorial shows you the steps to go through to set up what I believe to be the best email app on the android platform to read Exchange mail.  Get the app on your phone: The first step is to make sure you have the Samsung Email app on your …
Suggested Courses
Course of the Month11 days, 2 hours left to enroll

612 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