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?
5 3153
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
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
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
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
thx guys for the prompt responses. i will give it a try and let you know
Sign in to post your reply or Sign up for a free account.
Similar topics
by: William Buchanan |
last post by:
Hi folks
I want to show 2 records on a page side by side. Each record has an image
which will be displayed and a bit of text.
How can I do this?
Thanks
Will
|
by: Zeljko |
last post by:
I'm creating Address book.
Header of the main form (frmAddress) contains combo box (cboFilter) to
filter records by Occupation on main Form(Ocupation1). That's working. Combo
box also have "Show...
|
by: jaishu |
last post by:
Hi,
I am getting this error "The command or action 'Show all records' isn't available now", i get this when i try to do
DoCmd.ShowAllRecords
DoCmd.GoToControl ("ID")
DoCmd.FindRecord...
|
by: ontherun |
last post by:
Hi,
Could anyone please assist me on how to import Excel records to Access.
the records in excel are not in the same order as that of the one in Access. there are about 1000 records needs to be...
|
by: veaux |
last post by:
Might not have explained this correctly in subject, but query results
look like below:
Name ID Phone
Bill 001 123
Bill 001 234
Bill 001 ...
|
by: ochalove26 |
last post by:
hi all,
i'm a new programmer...
i use a combo box to filtering the records in my sub form..it was works...but when i want to show all records in my sub form it doesnt works..how it would be?please...
|
by: sranilp |
last post by:
Hi Friends,
My name is Anil,i will use the Business Objects/COGNOS which will retrives the morerecords like 3,00,000.I will create the .csv/.txt file for this and import to Access and do the pivot...
|
by: veer |
last post by:
can any one help by providing the method inserting the records in excel file
because i created it all it works fine but i have no idea about how to insert new records in the existing excel file....
|
by: trtaan |
last post by:
Hi all,
This is trtaan.
I wanted to export around 300000 records that are present in my MS Access 2003 version table to excelsheet version8.
I had written a code to export to multiple sheets in...
|
by: aryanraj |
last post by:
Hi all,
I wanted to export around 300000 records that are present in my MS Access 2003 version table to excelsheet version8.
I had written a code to export to multiple sheets in one workbook,...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |