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"))
            End With
        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 


    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


All replies