Show number of records exported to Excel in msgbox

[neelsfer]

Is it possible to show the number of records that were exported to Excel, using" transferspreadsheet" method, in the msgbox that i use to show the process is complete? Any ideas?

[jimatqsi]

I would immediately link to the spreadsheet, open a recordset on the link and get the record count.

DoCmd.TransferText acLinkDelim, , SomeTblName, SomeExcelName, chkHasFieldNames
dim rs as dao.recordset
set rs = currentdb.openrecordset(SomeTblName,dbreadonly)
rs.movelast
msgbox "exported " & rs.recordcount & " records to Excel"
rs.close
set rs = nothing
 

Jim

[jimatqsi]

Conversely, you could open whatever table or query you used to export and get your count there. But you would be assuming 100% success to the Excel file.

Jim

[ADezii]

It would be a simple matter of determining how many Records were in the Data Source, once the Transfer has been completed, as in:

Private Sub cmdTest_Click()
On Error GoTo Err_cmdTest_Click
Dim strPATH As String
Dim strDataSource As String
Dim intRecsTransferred As Integer
 
strDataSource = "qryEmployeesByState"
strPATH = "C:\Test\Employees By State.xls"
 
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strDataSource, _
                          strPATH, True
 
'If you get here, all went well, get the Count of Records Transferred
intRecsTransferred = DCount("*", strDataSource)
 
'Confirmation
MsgBox intRecsTransferred & " Records have been Transferred to " & strPATH, _
       vbInformation, "Transfer Complete"
 
 
Exit_cmdTest_Click:
  Exit Sub
 
Err_cmdTest_Click:
  MsgBox Err.Description, vbExclamation, "Error in Excel Transfer"
    Resume Exit_cmdTest_Click
End Sub

[jimatqsi]

I like the DCount way better than my recordset manipulation. Might be best to do DCount on the exported result rather than the source query/table.

Jim

[neelsfer]

thx guys for the prompt responses. i will give it a try and let you know