-
November 1st, 2009, 02:42 PM
#1
Export query to excel (cant translate VB6 code to .NET)
I have this working on VB6 that saves a query in a excel sheet:
Code:
Dim xlApp As Object
Dim cn as new ADODB.connection
Dim sql as String
'Connect to database
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & App.Path & "\temp.mdb;"
'Create excel object
Set xlApp = CreateObject("Excel.Application")
xlApp.Application.Workbooks.Open (App.Path & "\" & XLSNombre & ".xls")
xlApp.Application.Visible = True
'Close object (it doesnt allow insert of values with object open)
xlApp.Application.ActiveWorkbook.Close savechanges:=True
xlApp.Quit
'Insert values
sql = "SELECT * INTO [Excel 8.0;Database=" & App.Path & _
"\" & XLSNombre & ".xls].[Inicial] FROM Table where seccion='Inicial' or seccion='Suplente Inicial' order by seccion,nombre;"
cn.Execute sql
The XLSNombre is a variable with the name of the excel file that has been set before.
The connection I have in .NET is to SQL server (but is the same).
How can I use this code (or..how can I make this code work for .NET) or what is the equivalent of this for .NET?
I found some solutions but with inserts of single rows or I have to set the title of the columns, I dont find any "simple" solution to do an "insert into" (like the code I use in VB6).
Thanks!
-
November 1st, 2009, 03:44 PM
#2
Re: Export query to excel (cant translate VB6 code to .NET)
I advanced a little:
I create the file with:
Code:
Public Sub CreateExcelFile(ByVal sFile As String)
Dim xlApp As Object
xlApp = CreateObject("Excel.Application")
xlApp.workbooks.add()
xlApp.Workbooks(1).SaveAs(sFile)
xlApp.Workbooks(1).Close()
xlApp.Quit()
End Sub
But I have an error when I try to do this:
Code:
Public Sub Export_To_EXCEL()
If m_cn.State <> ConnectionState.Open Then m_cn.Open()
Dim cmd As New SqlCommand
cmd.Connection = m_cn
cmd.CommandText = "SELECT * INTO [Excel 8.0;Database=C:\Test.xls].[Hoja1]" & _
" FROM movimientos;"
cmd.ExecuteNonQuery()
End Sub
the error is in executenonquery(): The specified schema name "Excel 8.0;Database=C:\Test.xls" either does not exist or you do not have permission to use it.
Nothing is using that file and It exists. What can I be doing wrong?
thanks again!
-
November 1st, 2009, 06:13 PM
#3
Re: Export query to excel (cant translate VB6 code to .NET)
Start by looking
-
November 1st, 2009, 07:47 PM
#4
Re: Export query to excel (cant translate VB6 code to .NET)
I'll try that, thanks dglienna
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|