Solved

VB.Net Export Table to Excel

Posted on 2012-04-12
12
1,578 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
  • 6
  • 6
12 Comments
 
LVL 29

Assisted Solution

by:Randy Downs
Randy Downs earned 500 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 29

Expert Comment

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

D:\Finance\2012Jan_Mar.xlsx
0
 

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 29

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 29

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 29

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 29

Assisted Solution

by:Randy Downs
Randy Downs earned 500 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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now