Link to home
Start Free TrialLog in
Avatar of Sanjay Gandhi
Sanjay GandhiFlag for India

asked on

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.
SOLUTION
Avatar of Randy Downs
Randy Downs
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sanjay Gandhi

ASKER

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.
So this file is there and can be opened by Excel?

D:\Finance\2012Jan_Mar.xlsx
Sorry, but what does this comment above mean?? My requirement is to export the file fast. Does the above code provided, fullfill this requirement?
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")
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Is the header and 1st row correct?
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.
So it's working or freezes at 1st row?
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.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Oh ya. Thanks for the solution however.