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.
Sanjay GandhiFounder, KenhalAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 GandhiFounder, KenhalAuthor 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
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Sanjay GandhiFounder, KenhalAuthor 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 GandhiFounder, KenhalAuthor 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Randy DownsOWNERCommented:
Is the header and 1st row correct?
0
Sanjay GandhiFounder, KenhalAuthor 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 GandhiFounder, KenhalAuthor 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 GandhiFounder, KenhalAuthor Commented:
Oh ya. Thanks for the solution however.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.