Export query to excel (cant translate VB6 code to .NET)
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 4 of 4

Thread: Export query to excel (cant translate VB6 code to .NET)

  1. #1
    Join Date
    Jan 2006
    Posts
    197

    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!

  2. #2
    Join Date
    Jan 2006
    Posts
    197

    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!

  3. #3
    Join Date
    Jan 2006
    Location
    Chicago, IL
    Posts
    14,961

    Re: Export query to excel (cant translate VB6 code to .NET)

    Start by looking
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  4. #4
    Join Date
    Jan 2006
    Posts
    197

    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
  •  


Azure Activities Information Page

Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center