[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Save Dialog Box & Writing Dataset to Excel

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

650 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