Sanjay Gandhi
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.E xcel
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\2 012Jan_Mar .xlsx].[12 JM] FROM FinanceTable"
objExcel = CType(CreateObject("Excel. Applicatio n"), Microsoft.Office.Interop.E xcel.Appli cation)
objWorkBook = CType(sobjExcel.Workbooks. Add, Microsoft.Office.Interop.E xcel.Workb ook)
objSheet = CType(sobjWorkBook.Workshe ets(1), Microsoft.Office.Interop.E xcel.Works heet)
objSheet.SaveAs("D:\Financ e\2012Jan_ Mar.xlsx")
objWorkBook.Close()
sobjExcel.Quit()
ExlCommand = New SqlCommand(SQLCommand, SQLConnection)
ExlCommand.ExecuteNonQuery ()
objWorkBook.Save()
objWorkBook.Close()
sobjExcel.Quit()
Thanks.
' Imports
Imports System.Windows.Forms
Imports Microsoft.Office.Interop.E
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\2
objExcel = CType(CreateObject("Excel.
objWorkBook = CType(sobjExcel.Workbooks.
objSheet = CType(sobjWorkBook.Workshe
objSheet.SaveAs("D:\Financ
objWorkBook.Close()
sobjExcel.Quit()
ExlCommand = New SqlCommand(SQLCommand, SQLConnection)
ExlCommand.ExecuteNonQuery
objWorkBook.Save()
objWorkBook.Close()
sobjExcel.Quit()
Thanks.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
So this file is there and can be opened by Excel?
D:\Finance\2012Jan_Mar.xls x
D:\Finance\2012Jan_Mar.xls
ASKER
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.xls x. Does that file actually get created and can it be opened in Excel?
objSheet.SaveAs("D:\Finance\2012Jan_ Mar.xlsx")
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Is the header and 1st row correct?
ASKER
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, I guess we can close it now.
So it's working or freezes at 1st row?
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Oh ya. Thanks for the solution however.
ASKER