none
Run sql query in excel RRS feed

  • Question

  • Hi

    I wont to export query results from Access to Excel.

    From my Access app i execute this function

    Public Sub qExcel(pQuery As String)
        DoCmd.Hourglass True
        Dim dbs As ADODB.Connection
        Dim rs As New ADODB.Recordset
        
        Dim ex As Excel.Application
        Dim wb As Excel.Workbook
        Dim ws As Excel.Worksheet
                
        Set dbs = CurrentProject.AccessConnection
        Set ex = CreateObject("Excel.Application")
        Set wb = ex.Workbooks.Add
        Set ws = wb.ActiveSheet
        
        ex.Visible = True
        
        rs.Open pQuery, dbs
            
            With ws.QueryTables.Add(Connection:=rs, Destination:=Range("A1"))
                .Refresh
            End With
        rs.Close
        
        DoCmd.Hourglass False
    End Sub
     
    Everything is OK when I run this function for the first time. But when I try to run it the second time I get

    method range of object _global failed 

    error.


    Also I've noticed that first execution creates excel process that is in my process list even after I close excel. Second execution creates second process and after error when I close excel both processes are gone.

    Third execution creates no error fourth creates errors .... and so on

    Sorry for my English :)


    Wednesday, October 21, 2009 11:34 AM

Answers

All replies