Database query to an Excel file

Question

I have a code here made in VB.NET. The code enables the program to query from MS ACCESS database and save it to an Excel File(.xls) and prompts the user if he/she wants to open the file. The code works well yet I have a problem when the file has been opened. The columns are not auto-fitted to its contents which is making the file so messy and I also wanna allow the user to have an option to print the file. Is there any way to solve my problem? If you have any clarification, feel free to ask.

If (Not Directory.Exists("C:\Sales Monitoring Report")) Then
   Directory.CreateDirectory("C:\Sales Monitoring Report")
End If
System.IO.File.Delete("C:\Sales Monitoring Report\Transaction.xls")
Dim createExcelFile = "SELECT ORNumber, UserID, TransactionID, Vatable, Tax, Amount, TransactionDate, Status INTO [Excel 12.0;HDR=YES;DATABASE=C:\Sales Monitoring Report\Transaction.xls].[Sheet1] FROM tbl_transaction"
ExecNonQuery(createExcelFile)

If MessageBox.Show("Do you want to open the file?", "Open File", MessageBoxButtons.YesNo) = Windows.Forms.DialogResult.Yes Then
    Dim excelFile As New Excel.Application
    Dim excelWorkBook As Excel.Workbook
    excelWorkBook = excelFile.Workbooks.Open("C:\Sales Monitoring Report\Transaction.xls")
    excelFile.Visible = True
    excelWorkBook.Activate()
 End If

Try to limit your questions to one per post. Multiple questions in a post will dilute the knowledge base. — Sam Axe
– Sam Axe, Commented Apr 25, 2013 at 18:29

Alexander · Accepted Answer · 2013-04-25 20:22:39Z

1

To auto-fit columns in first worksheet, insert following lines after workbook activation:

excelWorkBook.Worksheets(1).Columns.AutoFit()
excelWorkBook.Worksheets(1).Rows.AutoFit()

To print active workbook, use PrintOut method:

excelWorkBook.PrintOut()

answered Apr 25, 2013 at 20:22

Alexander

4,1831 gold badge26 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gord Thompson Over a year ago

If this answer worked for you then please consider "accepting" it so the rest of us know that your issue has been resolved. If you found it especially helpful you can "upvote" it, too.

matzone · Accepted Answer · 2013-04-28 13:18:40Z

EZ way :

Make .xls file with columns as destination field, set columns width, and the formula ..
Use above .xls as template that you'll make copy at runtime
Use the copy of .xls to store record from your ACCESS DB

Something like this :

'In button event ..

Private Sub btnXLS_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnXLS.Click

    'copy .xls template to .xls target --> full path
    'consider TemplateA.xls as ".xls template" and sFN as ".xls target" (full path)


    Try
        My.Computer.FileSystem.CopyFile("TemplateA.xls", sFN, _
                            FileIO.UIOption.AllDialogs, FileIO.UICancelOption.DoNothing)
        Dim cnXLS As Data.OleDb.OleDbConnection
        Dim cnAccess As Data.OleDb.OleDbConnection
        Dim cmdXLS As New Data.OleDb.OleDbDataAdapter
        Dim cmdAccess As New Data.OleDb.OleDbDataAdapter
        Dim dsXls As New DataSet
        Dim dsAccess As New DataSet

        'opening cnAccess connection codes here .. dsAccess as source dataset .. and source table

        cmdAccess = New Data.OleDb.OleDbDataAdapter("SELECT * FROM .... , cnAccess)
        cmdXLS.Fill(dsXls, "xls")

        cnXLS = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;" & _
                "data source=" & sFN & ";Extended Properties=Excel 8.0;")
        cnXLS.Open()

        'Daftar --> as table name or sheet name in excel
        cmdXLS = New System.Data.OleDb.OleDbDataAdapter("select * from [Daftar$]", cnXLS)

        cmdXLS.Fill(dsXls, "xls")

        For n = 0 To dsAccess.table(source_table).Rows.Count - 1

            'some codes here

            cmdXLS.InsertCommand = New OleDb.OleDbCommand( .. ,cnXls)
            cmdXLS.InsertCommand.ExecuteNonQuery()
        Next
        cnXLS.Close()

        MsgBox("Transfer finished ! -- " & sFN, MsgBoxStyle.OkOnly, "Info")
        Exit Sub

    Catch ex As Exception
        MsgBox(ex.Message, MsgBoxStyle.OkOnly, "Error)
    End Try
End Sub

Though I found the answer to my problem, I'm interested to learn your solution. Can you show me a sample code for that please?

Collectives™ on Stack Overflow

Database query to an Excel file

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related