You should just use
objWS is already assigned as "objWB.Worksheets("DashBoard")"
I reworked your code a little.
Code:
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
Dim blnEXCEL As Boolean
' Establish an EXCEL application object
On Error Resume Next
'see if Excel is running
Set objXL = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set objXL = CreateObject("Excel.Application")
blnEXCEL = True
End If
Err.Clear
On Error GoTo 0
' this is the location of my test Excel file
' Set objWB = objXL.Workbooks.Open("C:\Accmdb\testtest\Metrics_Dashboard_v1001.xlsx")
Set objWB = objXL.Workbooks.Open("L:\Metrics\Dashboard\Metrics_Dashboard_v1001.xlsx")
Set objWS = objWB.Worksheets("DashBoard")
'activate sheet "Dashboard"
objWS.Activate
rsCount = 0
'my query name - change to your query name
queryName = "qryCGF" '<<= do not use "Name" as a query name
Set db = CurrentDb
Set rst = db.OpenRecordset(queryName)
'Source Data Records
If Not (rst.EOF And rst.BOF) Then
rst.MoveLast
rsCount = rst.RecordCount
Debug.Print rsCount
End If
rst.Close
msgbox rsCount, vbOKOnly, "Count"
With objWS
.Cells(3, 2).Value = rsCount
End With
objXL.Visible = True
' close the EXCEL file and save the new data
objWB.Close True '<<= add a breakpoint here if you want to see the spreadsheet before it is saved and closed
DoEvents ' for timing issues when saving workbook
Set objWS = Nothing
Set objWB = Nothing
If blnEXCEL = True Then
objXL.Quit
End If
Set objXL = Nothing
Set rst = Nothing
Set db = Nothing
End Sub