How to export record count from Access to Excel

[RunTime91]

Greetings ~



I am trying to send a record count from Access to an existing Excel workbook using a RecordSet.

Seems fairly simple - for those smarter than me

Here is what I have thus far - Unfortunately Google is of no help for this one

Const WbPath As String
Dim Rs5 As Recordset
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlRange As Excel.Range

StrSQL5 = "SELECT COUNT (*) FROM MyTable WHERE ImportDate = (SELECT MIN (ImportDate) " & _
                "FROM MyTable WHERE LEFT(ImportDate, 8) = FORMAT(GETDATE(),'yyyyMMdd')"

Set xlBook = "MyWorkbook"
Set xlSheet = "MyWorksheet"
Set xlRange = "MyRange"

Rs5.OpenRecordset StrSQL5, WBPath
MyRange ("AA3").CopyFromRecordset Rs5

Needless to say - I'm getting errors all over the place

If I comment out each of the Set xl... statements and the MyRange("AA3").Copy... statements the code errors on the Rs5.OpenRecordset StrSQL5
With an 'Object or With variable not set' error

Help?

Thanks all

[Micron]

A recordset is a specific object that you assign to an object variable. Also, the OpenRecordset method is one that belongs to the CurrentDb object. So try
set Rs5 = currentdb.OpenRecordset(StrSQL5)
The path variable has nothing to do with the recordset so it doesn't belong there. You would need that later to work with the workbook object.
Is there some reason that you have to use Automation instead of just TransferSpreadsheet function?
You don't specify the recordset type (ADO or DAO) so I have to guess that you don't need a connection string (fairly obvious from your code, I guess).
Can't assist with the other errors at this time as you haven't provided details.

[June7]

I assume you have actual values for the generic "My______" references.

All the recordset pulls is a single value. Why bother with CopyFromRecordset?

The Count(*) needs a fieldname alias. Get rid of the spaces after COUNT and MIN.

I would open recordset differently, never used what you show.

StrSQL5 = "SELECT COUNT(*) AS CountRecs FROM MyTable WHERE ImportDate = (SELECT MIN(ImportDate) " & _
                "FROM MyTable WHERE LEFT(ImportDate, 8) = FORMAT(GETDATE(),'yyyyMMdd')"
Set Rs5 = CurrentDb.OpenRecordset(StrSQL5)
xlSheet.Range("AA3") = Rs5!CountRecs

Did you first test that SQL statement in an Access query object?

What does the function GetDate() actually do - just return today's date?

[RunTime91]

Micron - June ~

Thanks you two for stepping into this one

Yesterday (for some reason) I wasn't able to copy/paste so I did my best to type longhand all the code - Fail!

So here are some answers along with the rest of the code posted below.

Micron:
I am using Early Binding and the Rs is DAO
As for using TransferSpreadsheet function - Wasn't able to figure out how to get it to transfer just the count of records rather than all of the records.
As for needing a connection string - Yep, I need a connection string - I simply failed to type it out - it is the code below
And lastly, the additional errors are coming from trying and failing to declare the Excel Workbook objects (App, Book, Sheet & Range)

June:
The GETDATE was borrowed from a SQL Statement I was leverging I have now changed that to just DATE and incorporated some of your other ideas
I have tested the code in the Access Query window - Works perfectly
I know I'm close - I just can't get past Setting the Excel Objects

I'm getting the following errors depending on how I am trying to get them to work

Invalid Qualifier
Invalid Use of Property
Type Mismatch (The code in its current configuration produces this error)

Sorry for the confusion with my earlier post - Hopfully this will help you - get me to the finish line

Const WbPath As String = "C:\Users\ABC1234\MainFolder\SubFolder1\Subfolder2\Subfolder3\SalesReport_TEST.xlsm"
        Dim Rs5 As DAO.Recordset
        Dim xlApp As Excel.Application
        Dim xlBook As Excel.Workbook
        Dim xlSheet As Excel.Worksheet
        Dim xlRange As Excel.Range
        
        StrSQL5 = "SELECT COUNT(*) AS CountRecs FROM tblQCMainAuditTable WHERE ImportDate = (SELECT MIN(importdate)" & _
          "FROM tblQCMainAuditTable WHERE LEFT(ImportDate, 8) = FORMAT(DATE(), 'yyyyMMdd') AND SpecialFlag is null)"
        
        Set xlBook = "SalesReport_TEST.xlsm"
        Set xlSheet = "RecordSource"
        Set xlRange = Range("A3")
        
        Set Rs5 = CurrentDb.OpenRecordset(StrSQL5)
        xlSheet.Range("AA3") = Rs5!CountRecs
        
End Sub

[Micron]

Wasn't able to figure out how to get it to transfer just the count of records rather than all of the records

The code you show doesn't get the count, just the records, except it looks like there's a call to an external procedure named CountRecs. If that's an intrinsic Access function, I've never heard of it. Any particular reason you don't get the count within your posted procedure?

AFAIK, a connection string only applies to ADO recordsets. What you show is a string variable for a path - not the same thing.

Your automation errors likely stem from the fact that you've declared the workbook object but failed to instantiate it. Ken Snell has a lot of great info on automation going both ways to/from Access and Excel. I could have simply provided an example, but you should look here as the circumstances affect exactly how you might want to do it, such as checking for an existing instance of Excel - or not. The main thing you're missing is the CreateObject (or GetObject)... part.

http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm

[June7]

Need a space after Min(importdate) so the text doesn't run together when compiled. Use: Debug.Print StrSQL5 to see the compiled SQL statement in Immediate Window.

Why are you using LEFT and FORMAT on the date criteria? Date value is actually in a text field? However, I tested this and should work.

Is SpecialFlag a Yes/No field? Yes/No field can never be Null, it's either True or False. No records will match this.

I tested this query/subquery syntax in VBA and it does return a single record with count value. Micron, might look at the query again. CountRecs is field name.

Now, as Micron noted, need to instantiate Excel objects. One example:

Dim xlx As Excel.Application, xlw As Excel.Workbook, xls As Excel.Worksheet
Dim blnEXCEL As Boolean

On Error Resume Next
Set xlx = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
    Set xlx = CreateObject("Excel.Application")
    blnEXCEL = True
End If
Err.Clear

'xlx.Visible = True
Set xlw = xlx.Workbooks.Open("your path here", , True) ' opens in read-only mode
Set xls = xlw.Worksheets("your worksheet here")

'recordset code here

Set xls = Nothing
xlw.Close False
Set xlw = Nothing
If blnEXCEL = True Then xlx.Quit
Set xlx = Nothing