vba load access query to specific excel cells

[xopherira]

I want to run multiple queries and have the count of records returned populate specific cells on spreadsheet. I am running into the issue of not being able to save to the specific cell before next query is ran. I need to be able to run query write to excel and continue on until all queries are finished.

Public Sub countRecords1107()
Dim rsCount As Integer
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim queryName As String
Dim objXL As Object
Dim objWB As Object
Dim objWS As Object
Set objXL = CreateObject("Excel.Application")
Set objWB = objXL.Workbooks.Open("L:\Metrics\Dashboard\Metrics_Dashboard_v1001.xlsx")
Set objWS = objWB.worksheets("DashBoard")

queryName = "MY_QUERY_NAME"
Set db = CurrentDb
Set rst = db.OpenRecordset(queryName)
   
'Source Data Records
If Not (rst.EOF And rst.BOF) Then
    Do While Not rst.EOF
    
    rsCount = rst.RecordCount
    Debug.Print rsCount
    'rst.Edit
    'rst.Update
    rst.MoveNext
    Loop
      
    rst.Close
    Set rst = Nothing
    Set db = Nothing
End If
MsgBox rsCount, vbOKOnly, "Count"
With objWS
.Cells(3, 2).Value = rsCount
End With
 objXL.Visible = True
End Sub

[ranman256]

take control of Excel , get your query count, then assign the count

Public Sub ControlXL()
Dim lCount As Long
Dim xl As Excel.Application


Set xl = CreateObject("excel.application")
With xl
    .Visible = True
    .Workbooks.Open  vXLfile

     lCount = DCount("*","qsQuery")

    .Range("F3").value = LCount
    
    .activeworkbook.save
End With
Set xl = Nothing
End Sub

[ssanfu]

One method might be to execute the queries, saving the count of records to variables, then write the values to Excel all at the same time.
How many queries are you executing?


Also, from your code, it looks like you are not doing proper clean up; ie closing and setting objects to Nothing. (Do not close "db")

[xopherira]

This is what I want but having a hard time coming up with. Seems like I am going in circles. Do you an example?

[ssanfu]

Again, How many queries are you executing?

How are you executing the queries? In one sub? Multiple subs?

[xopherira]

9 queries and my first attempt was to have them all in one sub, but I have not been able to accomplish.

[ssanfu]

OK, this is a really rough, brute force example. (I don't know enough about your dB.)
This is AIR CODE!!

Public Sub countRecords1107()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset

    'variables to hold record counts
    Dim rsCount1 As Long, rsCount2 As Long, rsCount3 As Long
    Dim rsCount4 As Long, rsCount5 As Long, rsCount6 As Long
    Dim rsCount7 As Long, rsCount8 As Long, rsCount9 As Long

    '    Dim queryName As String
    Dim objXL As Object
    Dim objWB As Object
    Dim objWS As Object


    Set db = CurrentDb

    'initialize variables
    rsCount1 = 0
    rsCount2 = 0
    rsCount3 = 0
    rsCount4 = 0
    rsCount5 = 0
    rsCount6 = 0
    rsCount7 = 0
    rsCount8 = 0
    rsCount9 = 0

    '1st query
    Set rst = db.OpenRecordset("MY_QUERY_NAME")
    'Source Data Records
    If Not (rst.EOF And rst.BOF) Then
        rst.MoveLast
        rsCount1 = rst.RecordCount
    End If
    rst.Close

    '2nd query
    Set rst = db.OpenRecordset("HIS_QUERY_NAME")
    'Source Data Records
    If Not (rst.EOF And rst.BOF) Then
        rst.MoveLast
        rsCount2 = rst.RecordCount
    End If
    rst.Close

    '3rd query
    Set rst = db.OpenRecordset("OUR_QUERY_NAME")
    'Source Data Records
    If Not (rst.EOF And rst.BOF) Then
        rst.MoveLast
        rsCount3 = rst.RecordCount
    End If
    rst.Close

    '4th query
    Set rst = db.OpenRecordset("HER_QUERY")
    'Source Data Records
    If Not (rst.EOF And rst.BOF) Then
        rst.MoveLast
        rsCount4 = rst.RecordCount
    End If
    rst.Close

    '5th query
    Set rst = db.OpenRecordset("THIS_QUERY")
    'Source Data Records
    If Not (rst.EOF And rst.BOF) Then
        rst.MoveLast
        rsCount5 = rst.RecordCount
    End If
    rst.Close

    '6th query
    Set rst = db.OpenRecordset("THAT_QUERY")
    'Source Data Records
    If Not (rst.EOF And rst.BOF) Then
        rst.MoveLast
        rsCount6 = rst.RecordCount
    End If
    rst.Close

    '7th query
    Set rst = db.OpenRecordset("ANOTHER_QUERY")
    'Source Data Records
    If Not (rst.EOF And rst.BOF) Then
        rst.MoveLast
        rsCount7 = rst.RecordCount
    End If
    rst.Close

    '8th query
    Set rst = db.OpenRecordset("qryStart")
    'Source Data Records
    If Not (rst.EOF And rst.BOF) Then
        rst.MoveLast
        rsCount8 = rst.RecordCount
    End If
    rst.Close

    '9th query
    Set rst = db.OpenRecordset("qryEnd")
    'Source Data Records
    If Not (rst.EOF And rst.BOF) Then
        rst.MoveLast
        rsCount9 = rst.RecordCount
    End If
    rst.Close

    ' in the Locals window, you should see the variables and record counts.

    Set rst = Nothing
    Set db = Nothing


    Set objXL = CreateObject("Excel.Application")
    Set objWB = objXL.Workbooks.Open("L:\Metrics\Dashboard\Metrics_Dashboard_v1001.xlsx")
    Set objWS = objWB.worksheets("DashBoard")


    With objWS
        .Cells(3, 2).Value = rsCount1
        .Cells(4, 2).Value = rsCount2
        .Cells(5, 2).Value = rsCount3
        .Cells(6, 2).Value = rsCount4
        .Cells(7, 2).Value = rsCount5
        .Cells(8, 2).Value = rsCount6
        .Cells(9, 2).Value = rsCount7
        .Cells(10, 2).Value = rsCount8
        .Cells(11, 2).Value = rsCount9
    End With
    objXL.Visible = True
    
    
    'still need to save the workbook and do clean up
    
End Sub

See Ken's ACCESS Examples and Information for Working With EXCEL Workbook Files
http://www.accessmvp.com/kdsnell/EXCEL_MainPage.htm
for examples of how to write to Excel workbooks/worksheets.

[Micron]

ssanfu, you have put a lot into your reply and I commend you for putting forth the effort. I'd like to offer another way of executing the task - not that it's better just that it's different.

I'd try to open the Excel file for exclusive access first (someone may have it open and there are functions available to test this) and if I cannot, terminate the process with a message rather than run queries. Then I'd pass the 1st query name to a function that runs it (one rsCount is dim'd there) and returns the record count. Since the workbook is now open, write the value to the cells. Next line passes next query name to function which again returns the count and this gets written to the sheet, and so on. If any of xopherira's queries have parameters, it will get trickier, but these can be passed to the query function also. Aside from what happens if someone has the workbook open, the only advantage I suppose, is that there are a whole lot less lines of code this way.

Again, just sharing how I do this, not saying it's any better.

[ssanfu]

@ Micron
Agreed.

As I stated, the code was a rough, brute force (and air code) method. Not knowing the skill level of the OP, it seemed the easiest way to demo a method to run 9 queries and get the record counts. Also, not a lot of info was provided; how often this was run, are they parameter queries,.....

For my use, I might have used an array to hold the record counts, stored the query names in a table, given the queries a prefix that only certain queries had (so to get just the queries I wanted, looping through the queries collection), or stored the SQL of the queries in a table.

Lots of ways to do this.....