Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

VB.Net Export Table to Excel

Posted on 2012-04-12
12
Medium Priority
?
2,049 Views
Last Modified: 2012-05-01
Following is my code. I want to Export my SQL table Finance into an Excel file. I am getting the error "File either does not exist or you do not have permission to access it". Whereas the file exists already. Let me tell you I already have the successful SQL session open.

' Imports

Imports System.Windows.Forms
Imports Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Interop


' Class Code

        Dim objExcel As Excel.Application
        Dim objWorkBook As Excel.Workbook
        Dim objSheet As Excel.Worksheet

        Dim ExlCommand As SqlCommand
        Dim SQLCommand As String = ""
        Dim ExcelFile As String = ""

        SQLCommand = "SELECT * INTO [Excel 12.0;Database=D:\Finance\2012Jan_Mar.xlsx].[12JM] FROM FinanceTable"

        objExcel = CType(CreateObject("Excel.Application"), Microsoft.Office.Interop.Excel.Application)
        objWorkBook = CType(sobjExcel.Workbooks.Add, Microsoft.Office.Interop.Excel.Workbook)
        objSheet = CType(sobjWorkBook.Worksheets(1), Microsoft.Office.Interop.Excel.Worksheet)
        objSheet.SaveAs("D:\Finance\2012Jan_Mar.xlsx")
        objWorkBook.Close()
        sobjExcel.Quit()

        ExlCommand = New SqlCommand(SQLCommand, SQLConnection)
        ExlCommand.ExecuteNonQuery()
        objWorkBook.Save()
        objWorkBook.Close()
        sobjExcel.Quit()

Thanks.
0
Comment
Question by:sanjay-gandhi
[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
  • 6
  • 6
12 Comments
 
LVL 30

Assisted Solution

by:Randy Downs
Randy Downs earned 2000 total points
ID: 37837782
Try this
http://www.codeproject.com/Articles/19840/Export-to-Excel-using-VB-Net

         Dim dt1 As New DataTable
          Dim I1, J1 As Integer
          For I1 = 0 To dsmas1.Tables(0).Columns.Count - 1
                dt1.Columns.Add(dsmas1.Tables(0).Columns(I1).ColumnName)
           Next
           For I1 = 0 To dsmas1.Tables(0).Rows.Count - 1
                Dim DR As DataRow = Nothing
                DR = dt1.NewRow
                For J1 = 0 To dsmas1.Tables(0).Columns.Count - 1
                    DR.Item(J1) = dsmas1.Tables(0).Rows(I1).ItemArray(J1)
                Next
                dt1.Rows.Add(DR)
           Next
           rel_ds.Tables.Add(dt1)
           Dim dt As New DataTable
           Dim I, J As Integer
           For I = 0 To dschd1.Tables(0).Columns.Count - 1
                dt.Columns.Add(dschd1.Tables(0).Columns(I).ColumnName)
           Next
           For I = 0 To dschd1.Tables(0).Rows.Count - 1
                Dim DR As DataRow = Nothing
                DR = dt.NewRow
                For J = 0 To dschd1.Tables(0).Columns.Count - 1
                    DR.Item(J) = dschd1.Tables(0).Rows(I).ItemArray(J)
                Next
                dt.Rows.Add(DR)
           Next
            rel_ds.Tables.Add(dt)
0
 

Author Comment

by:sanjay-gandhi
ID: 37837883
Thanks, I've tried the above-like code already, but for the data containing 40,000 rows, it takes good 2 to 3 hours to export. My application also uses Import from Excel into SQL table for the same number of rows. And using single command 'Insert Into..' it imports in just 10 minutes. Therefore, I was looking for a solution with a single command for export as well.
0
 
LVL 30

Expert Comment

by:Randy Downs
ID: 37837911
So this file is there and can be opened by Excel?

D:\Finance\2012Jan_Mar.xlsx
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:sanjay-gandhi
ID: 37838971
Sorry, but what does this comment above mean?? My requirement is to export the file fast. Does the above code provided, fullfill this requirement?
0
 
LVL 30

Expert Comment

by:Randy Downs
ID: 37839249
It looks like your code is exporting to D:\Finance\2012Jan_Mar.xlsx. Does that file actually get created and can it be opened in Excel?

objSheet.SaveAs("D:\Finance\2012Jan_Mar.xlsx")
0
 

Accepted Solution

by:
sanjay-gandhi earned 0 total points
ID: 37895918
As I had mentioned that was giving the error. So I've rested with the code like the one you gave above. Here's the code that creates Excel file with Header from populated Grid, and colors and freezes the first row in Excel.

Private sub CreateFinance()
        Dim sobjExcel As Excel.Application
        Dim sobjWorkBook As Excel.Workbook
        Dim sobjSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value

        Dim sSQLCommand As String = "", ssExcelFile As String = ""
        Dim gRow As Integer, gCol As Integer                  ' g for grid

        ' Start
        sobjExcel = New Excel.ApplicationClass
        sobjWorkBook = sobjExcel.Workbooks.Add(misValue)
        sobjSheet = sobjWorkBook.Sheets("Sheet1")

        ' Export to Excel
        Try
             ssExcelFile = "2012Jan_Mar.xlsx"
             gRows = GrSbillMatching.RowCount                  ' grid containing table values
             For gRow = 0 To GrSbillMatching.RowCount - 1
                 For gCol = 0 To GrSbillMatching.ColumnCount - 1
                     If gRow = 0 Then
                         ' header and first record
                         sobjSheet.Cells(gRow + 1, gCol + 1) = GrSbillMatching.Columns(gCol).HeaderText                  ' transferring grid header
                         sobjSheet.Cells(gRow + 2, gCol + 1) = GrSbillMatching.Item(gCol, gRow).Value
                     Else
                         ' regular records
                         sobjSheet.Cells(gRow + 2, gCol + 1) = GrSbillMatching.Item(gCol, gRow).Value
                     End If
                 Next
             Next
             ' Format Excel sheet
             With sobjSheet
                 .Columns("A:S").entirecolumn.autofit()     ' column autofit
                 .Range("A1").Select()
                 .Range("A1:AS1").Interior.Color = System.Drawing.ColorTranslator.ToOle(Color.LightBlue)             ' header color Sbill Cols
                 .Range("A2").Select()
                 .Application.ActiveWindow.FreezePanes = True
             End With


        Catch Ex As Exception
            MsgBox("File could not be created..." & vbNewLine & Ex.Message, MsgBoxStyle.Exclamation, csMessageTitle)
            ' Release objects
            ReleaseObject(sobjExcel)
            ReleaseObject(sobjWorkBook)
            ReleaseObject(sobjSheet)
            MessageText(False)
            Exit Sub

        End Try

        ' Try Saving - in case Cancel pressed then Catch handles
        Try
            sobjSheet.SaveAs("D:\Finance\" & ssExcelFile)
            sobjWorkBook.Close()
            sobjExcel.Quit()
            MsgBox("Excel file " & ssExcelFile & " created...", MsgBoxStyle.Exclamation, csMessageTitle)
        Catch Ex As Exception
            MsgBox("File could not be Saved..." & vbNewLine & Ex.Message, MsgBoxStyle.Exclamation, csMessageTitle)
        End Try

        ' Release objects
        ReleaseObject(sobjExcel)
        ReleaseObject(sobjWorkBook)
        ReleaseObject(sobjSheet)
end sub
0
 
LVL 30

Expert Comment

by:Randy Downs
ID: 37897306
Is the header and 1st row correct?
0
 

Author Comment

by:sanjay-gandhi
ID: 37897424
Yes, because when gRow is 0, then I pick up the headertext. And since I m actually at 0th row of the grid which is first data row, therefore the data also picks up fine.

So, I guess we can close it now.
0
 
LVL 30

Expert Comment

by:Randy Downs
ID: 37897446
So it's working or freezes at 1st row?
0
 

Author Comment

by:sanjay-gandhi
ID: 37897528
Yes, it also freezes at 1st row after transferring the entire data, because I want the freeze panes before A2. In case you are asking whether the loop freezes at first row, no it doesn't.
0
 
LVL 30

Assisted Solution

by:Randy Downs
Randy Downs earned 2000 total points
ID: 37897674
OK freeze panes is by design. Sounds like you everything is working fine.
0
 

Author Closing Comment

by:sanjay-gandhi
ID: 37913839
Oh ya. Thanks for the solution however.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

636 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