Count Excel Records from Access

[Drand]

Hi All

Is there a way to count the records in an Excel spreadsheet from within Access? I want to check the number of records before I import them to Access and then run a check that the correct number of records have been appended to the table.

Many thanks

 

[ebs17]

You can count in a variety of ways. Perhaps one would like to do this in a simple way with little effort. The Excel table that you can import can be linked and you get a table that you can use directly in Access, i.e. you can also use a DCount or an SQL statement with SELECT COUNT(*).

What concerns do you have about the correct number?

 

[June7]

If the sheet has a simple structure, consider:

SELECT Count(*) AS Cnt FROM [yoursheetname$] AS xlData IN 'your workbook filepath\yourfilename.xlsx'[Excel 12.0;HDR=yes;IMEX=0;ACCDB=Yes];

With this technique, can actually pull data from worksheet into a query object or recordset without setting link with External Data wizard.

To return just record count in VBA could be:
intCnt = CurrentDb.OpenRecordset("SELECT Count(*) AS Cnt FROM [yoursheetname$] AS xlData IN 'your workbook filepath\yourfilename.xlsx'[Excel 12.0;HDR=yes;IMEX=0;ACCDB=Yes];")(0)

 

[ebs17]

Sub importXL()
    Dim db As DAO.Database
    Dim sSQL As String

    Set db = CurrentDb
    sSQL = "INSERT INTO tTarget ([FieldList])" & _
           " SELECT T.* FROM [excel 12.0 xml;hdr=yes;imex=1;DATABASE=X:\AnyWhere\XLWorkbook.xlsx].[Sheet1$] AS T"
    db.Execute sSQL, dbFailOnError
    MsgBox db.RecordsAffected & " Records were imported"
End Sub

This routine imports all records, or no records if an error occurs. The number determined in the same process.
Conditions for "correct" can also be installed.

 

[Drand]

Hi All

Is there a way to count the records in an Excel spreadsheet from within Access? I want to check the number of records before I import them to Access and then run a check that the correct number of records have been appended to the table.

Many thanks

Thanks for the reply. The problem is that I have 134 spreadsheets from different countries that are appended to one table. I have a loop that achieves this but I was trying to insert code into my import that counts the records before they are imported.

Public Function ImportAllExcel()

Dim MyFile As String
Dim MYPath As String

MYPath = "C:\KPMG\CountryData\"
MyFile = Dir(MYPath & "*.xlsx")
Do While MyFile > vbNullString
    ' Count records before they are imported here
    DoCmd.TransferSpreadsheet acImport, 10, "tblConsolRawData", MYPath & MyFile, True     ' acSpreadsheetTypeExcel12Xml
    MyFile = Dir
    MsgBox "Raw Data Imported for " & MyFile & " From " & MYPath
Loop

End Function

 

[Pat Hartman]

Guess I'm not sure why linking to the spreadsheet and using dCount() to count it doesn't work for you? Then you can run an append query that appends the linked table to the permanent file, hopefully with some info that ties each row to the file it came from. As long as you keep linking as the same local name, the queries won't break.

 

[The_Doc_Man]

The problem is that I have 134 spreadsheets from different countries that are appended to one table.

From the way you described it, your loop could do any of several methods to perform the import. The method you ACTUALLY use would then determine the best way to pre-count.

I was trying to insert code into my import that counts the records before they are imported.

A couple of choices:

A) If you DO NOT use an Excel Application Object: Dynamically link the spreadsheet as though it were a read-only table and open the implied recordset. Do a recordset.MoveLast and then get the recordset.Count value.

B) If it happens that for other reasons, you already DO use an Excel Application Object: For the spreadsheet object you were going to import, you can do a something= spreadsheet.Cells.Rows.Count

 

[June7]

@Drand, did you see post 3?

 

[Drand]

@Drand, did you see post 3?

Sorry. It is xmas day here and I won't get to this until tomorrow. Merry Christmas

 

[Gasman]

Sorry. It is xmas day here and I won't get to this until tomorrow. Merry Christmas

Wow, it is Xmas day here as well! What a coincidence?

 

[June7]

@Gasman, Drand posted that when it was New Years Eve for me.

 

[Gasman]

@Gasman, Drand posted that when it was New Years Eve for me.

Yes, I know that countries have time difference. Even the states in the USA do. I used to be in the Merhcnat Navy.

 

[Drand]

If the sheet has a simple structure, consider:

SELECT Count(*) AS Cnt FROM [yoursheetname$] AS xlData IN 'your workbook filepath\yourfilename.xlsx'[Excel 12.0;HDR=yes;IMEX=0;ACCDB=Yes];

With this technique, can actually pull data from worksheet into a query object or recordset without setting link with External Data wizard.

To return just record count in VBA could be:
intCnt = CurrentDb.OpenRecordset("SELECT Count(*) AS Cnt FROM [yoursheetname$] AS xlData IN 'your workbook filepath\yourfilename.xlsx'[Excel 12.0;HDR=yes;IMEX=0;ACCDB=Yes];")(0)

Hi June7

Thanks for this. I can certainly get intCnt to work for a specific file which is great. I have been trying to place variables in the code for filepath and filename as MyPath and MyFile respectively so that the code can loop through all 134 files and provide the record count for each file.

My code is:

Public Function CountExcelRecords()

Dim MyFile As String
Dim MYPath As String
Dim intCnt As Long

MYPath = "C:\KPMG\CountryData\"
MyFile = Dir(MYPath & "*.xlsx")
Do While MyFile > vbNullString
    
    
    intCnt = CurrentDb.OpenRecordset("SELECT Count(*) AS Cnt FROM [Sheet1$] AS xlData IN '& MyPath \& MyFile '[Excel 12.0;HDR=yes;IMEX=0;ACCDB=Yes];")(0)
    MyFile = Dir
  Debug.Print intCnt
    
Loop
End Function

This is obviously wrong but I cannot work out how to do this.

Are you able to assist?

Many thanks

 

[Gasman]

Put the SQL into a string variable, and debug.print that until you get it correct. Then use that variable in the openrecordset.

 

[Pat Hartman]

With this technique, can actually pull data from worksheet into a query object or recordset without setting link with External Data wizard.

But then you have to modify the query for every file.

Four people have tried to help you with slightly different approaches. I will describe my approach again and if you think that will work for you I will help to implement it. Otherwise, I will go away.

a) you will need to modify your RawData table to include a BatchID. Follow along below.
b) You will need to create a new table to log batches. It contains 4 columns. BatchID (autonumber), LinkCount, AppendedCount, AppendDate (default to Now()
When I do this, I create the concept of a "batch". A batch is the set of records imported from any file. Here are the steps.
1. Create a record in the batch table to include the name of the source file. Include a column for the count. Save the record
2. Use TransferSpreadsheet to LINK the spreadsheet
3. Using dCount() run a query that counts the rows in the linked spreadsheet and updates the link count in the batch record. The query will take arguments.
4. Run an append query that appends the data in the linked spreadsheet to the RawData table.
5. Using dCount() run a query that counts the rows in the RawData table for the batch and updates the AppendedCount in the batch record.
6. Using a dCount() with criteria = "BatchID = " & Me.BatchID & " AND LinkCount <> AppendedCount"

If the last dCount returns 0, the counts match.

PS, I've never had a problem with losing records in the append so I think this is overkill. I still use the batch concept but only to tag the records for the batch and log the file name to prevent importing the same file more than once.

 

[June7]

But then you have to modify the query for every file.

That's what VBA is for.

Dim MyFile As String
Dim MyPath As String
Dim intCnt As Integer
MyPath = "C:\KPMG\CountryData\"
MyFile = Dir(MyPath & "*.xlsx")
Do While MyFile <> ""
    intCnt = CurrentDb.OpenRecordset("SELECT Count(*) AS Cnt FROM [Sheet1$] AS xlData IN '" & MyPath & MyFile & "'[Excel 12.0;HDR=yes;IMEX=0;ACCDB=Yes];")(0)
    Debug.Print intCnt
    MyFile = Dir
Loop

This does assume each workbook has a sheet named "Sheet1".

 

[Drand]

But then you have to modify the query for every file.

Four people have tried to help you with slightly different approaches. I will describe my approach again and if you think that will work for you I will help to implement it. Otherwise, I will go away.

a) you will need to modify your RawData table to include a BatchID. Follow along below.
b) You will need to create a new table to log batches. It contains 4 columns. BatchID (autonumber), LinkCount, AppendedCount, AppendDate (default to Now()
When I do this, I create the concept of a "batch". A batch is the set of records imported from any file. Here are the steps.
1. Create a record in the batch table to include the name of the source file. Include a column for the count. Save the record
2. Use TransferSpreadsheet to LINK the spreadsheet
3. Using dCount() run a query that counts the rows in the linked spreadsheet and updates the link count in the batch record. The query will take arguments.
4. Run an append query that appends the data in the linked spreadsheet to the RawData table.
5. Using dCount() run a query that counts the rows in the RawData table for the batch and updates the AppendedCount in the batch record.
6. Using a dCount() with criteria = "BatchID = " & Me.BatchID & " AND LinkCount <> AppendedCount"

If the last dCount returns 0, the counts match.

PS, I've never had a problem with losing records in the append so I think this is overkill. I still use the batch concept but only to tag the records for the batch and log the file name to prevent importing the same file more than once.

Thanks Pat. I agree now that this is overkill. I have tested and tested the imports and they work perfectly. Perhaps a bit of insecurity around the import process at first but I am now convinced that I don't need to do this.