Solved

Save Dialog Box & Writing Dataset to Excel

Posted on 2004-04-01
2
1,116 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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

696 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