Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2143
  • Last Modified:

VB.Net Export Table to Excel

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
sanjay-gandhi
Asked:
sanjay-gandhi
  • 6
  • 6
3 Solutions
 
Randy DownsOWNERCommented:
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
 
sanjay-gandhiAuthor Commented:
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
 
Randy DownsOWNERCommented:
So this file is there and can be opened by Excel?

D:\Finance\2012Jan_Mar.xlsx
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
sanjay-gandhiAuthor Commented:
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
 
Randy DownsOWNERCommented:
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
 
sanjay-gandhiAuthor Commented:
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
 
Randy DownsOWNERCommented:
Is the header and 1st row correct?
0
 
sanjay-gandhiAuthor Commented:
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
 
Randy DownsOWNERCommented:
So it's working or freezes at 1st row?
0
 
sanjay-gandhiAuthor Commented:
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
 
Randy DownsOWNERCommented:
OK freeze panes is by design. Sounds like you everything is working fine.
0
 
sanjay-gandhiAuthor Commented:
Oh ya. Thanks for the solution however.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now