[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Save Dialog Box & Writing Dataset to Excel

Posted on 2004-04-01
2
Medium Priority
?
1,144 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Recently while returning home from work my wife (another .NET developer) was murmuring something. On further poking she said that she has been assigned a task where she has to serialize and deserialize objects and she is afraid of serialization. Wha…
In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
Loops Section Overview
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

873 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