CSV or XLSX Bank Statement to Access module (need to import via VBA and then write back to Excel with imported flag) (2 Viewers)

[writeprivate]

Hi - I am new here. I am a reasonably experienced Access developer and am close to the MVP stage of developing an investment tracking application with double-entry bookkeeping. The last module/functionality for this version will be handling bank statements. I have not worked with Excel to Access to Excel before so would really appreciate some guidance.

The desired workflow is as follows:

1. The user has multiple bank accounts with different banks - each having a different bank statement layout
2. The user will create an Excel spreadsheet for each bank type. Then periodically, the user will download the CSV/XLSX from the bank and either OPTION A) manually append records to the single spreadsheet or OPTION B) simply save the new spreadsheet with latest records using a standard file naming format and within a standard folder. ** I am working with the OPTION A at the moment - see why below **
3. Using a button to run code (having selected bank type, folder location etc.), the desired VBA code will append all Excel records ("imported" field marked as false) - to the MS Access table "tblBankStatements". During import, the MS Access table boolean field "Imported" will be set to TRUE. At the same time, the Excel Imported field will also be set to true (or -1) for those corresponding records.

I have tried working with the in-built functions of Access for importting, appending, linking to Excel but frankly these approaches are not robust and reproducable and i think that code is the better way to go so that i can keep things neater and scalable.

ChatGPT has given me this skeleton of code. Your thoughts on this would be appreciated - is this the right road?

Option Compare Database
Option Explicit

Sub ImportExcelFile()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim xlApp As Object
Dim xlWorkbook As Object
Dim xlWorksheet As Object
Dim strFile As String
Dim strFolder As String
Dim strSQL As String

' Set the folder path where your Excel files are located
strFolder = "C:\YourFolderPath"

' Initialize Excel objects
Set xlApp = CreateObject("Excel.Application")

' Disable Excel alerts and visibility for faster processing
xlApp.DisplayAlerts = False
xlApp.Visible = False

' Set the database and recordset objects
Set db = CurrentDb()
Set rst = db.OpenRecordset("YourTableName")

' Loop through each file in the folder
strFile = Dir(strFolder & "\*.xlsx") ' Change the file extension if necessary

Do While strFile <> ""
' Check if the file has already been imported
strSQL = "SELECT Count(*) FROM YourTableName WHERE ImportedFlag=True AND FileName='" & strFile & "'"
If DCount("*", "YourTableName", "ImportedFlag=True AND FileName='" & strFile & "'") = 0 Then

' Open the Excel file
Set xlWorkbook = xlApp.Workbooks.Open(strFolder & "\" & strFile)

' Specify the relevant worksheet in the Excel file
Set xlWorksheet = xlWorkbook.Sheets("YourWorksheetName")

' Loop through the rows in the worksheet
Dim i As Integer
For i = 2 To xlWorksheet.UsedRange.Rows.Count ' Assuming the data starts from row 2

' Extract the relevant information from the Excel file
Dim transactionDate As Date
Dim transactionAmount As Double
' ... extract other fields as necessary

' Insert the data into the Access table
rst.AddNew
rst!TransactionDate = transactionDate
rst!TransactionAmount = transactionAmount
' ... set other fields as necessary
rst!FileName = strFile ' Store the file name for reference
rst!ImportedFlag = True ' Mark the record as imported
rst.Update

' Mark the corresponding "Imported" field as True in the Excel sheet
xlWorksheet.Cells(i, "D").Value = True ' Assuming the "Imported" field is in column D

Next i

' Close the Excel file without saving changes
xlWorkbook.Close False
End If

' Move to the next file in the folder
strFile = Dir
Loop

' Clean up objects
rst.Close
Set rst = Nothing
Set db = Nothing

' Quit Excel and release the objects
xlApp.Quit
Set xlWorksheet = Nothing
Set xlWorkbook = Nothing
Set xlApp = Nothing

MsgBox "Import completed successfully."

End Sub






NOTE: Why using OPTION A. The source downloaded bank statements need manual data manipulation anyway because the download has marketing text and other information in header and footer rows so not a simple sheet with first row as column headings. In the future when i figure out Excel code to remove and reformat the source data, I may move to OPTION B. Right now I want to understand the broader picture of MS Access VBA talking to Excel and then I will have a skeleton to learn from.

 

[writeprivate]

Very much want to make it easy for readers to assist so I ahve now asked ChatGPT to summarise my request - here is the output:


Request: Importing Microsoft Excel into Microsoft Access with VBA

I'm looking for assistance with importing bank statements in CSV or XLSX format into Microsoft Access using VBA code. I have specific requirements and desired functionality that I would like to achieve:

1. Importing new records: I want to import transactions periodically into Access from a spreadsheet. To avoid duplicates, I need a way to mark the records as imported in the spreadsheet. In the future, the code should only import new records where there is no flag indicating they have been imported.
2. Bank-specific spreadsheets: I have different spreadsheets for each bank due to varying layouts. I would like to create a set of spreadsheets, and then map the fields in each spreadsheet to a common field in a single table in Access.

Desired functionality:

• Use VBA code to programmatically import Excel files into Access.
• Write back to the spreadsheet to indicate that a record has been imported.
• Import only new records based on the presence of an "imported" flag in the spreadsheet.
• Handle different spreadsheet layouts for each bank.

Any guidance or code samples would be greatly appreciated!

 

[MsAccessNL]

I would import all the data into a table, you can also try TransferSpreadSheet, and after import i would move the excel file to a different folder called “Imported”.

How do you want to prevent that the same bank statement isn’t imported twice (your flag system will not work for this).

 

[writeprivate]

Thanks for your comment. I would respond:

1. The idea is to add new rows to the same spreadsheet rather than keep separate spreadsheets
2. The flag would mean it is not relevant if the same spreadsheet is 'imported' as only records which have the imported flag set to false would be imported

 

[GaP42]

While your process is to use bank statements (with headers and graphics etc) or CSV, there are standard formats used to export transaction data from bank accounts for ready import into applications such as Quicken, MS Money, Reckon, MYOB - text files: QIF or OFX or CSV. You could perhaps leverage off these to make things simpler (however they are "barebones" - may lack headers, may not include account info).
If you intend to append new records to the same spreadsheet, then you need to acquire the new "statement" records as a spreadsheet, then process these to append to the existing cumulative spreadsheet, then deal (delete/archive) with the acquired spreadsheet after checking the validity of appended records? A simpler process is to import the standard file to a temp local table, check data quality, (perhaps add some red tape data - to track processing and ensure the record belongs to a particular account - if not in the export data) and then process the records to the appropriate table in your database. A transaction for an account must be unique - as composite of account/date(time)/transactionlabel/amount, use a unique index.
If you keep the cumulative spreadsheet of transactions and the table of transactions in the db then you have a duplication of the data. Is thus needed/ warranted?

 

[CJ_London]

Seems a complicated way of going about this.

I use a query to import the csv from the bank to an access table. The query ignores any transaction that has already been imported.

the fact the transactions start on say row 10 is easy to overcome by excluding records where say the value is not a date or is a double. Depends on the statement

And the fact that columns may have different names and be in a different order is also easily overcome

I have a client with accounts at several banks and all their csv’s have different layouts so that code is adjusted as required. I do this with a table to hold the variations of the code

The sql is created in VBA and the result is as follows

INSERT INTO tblBankTrans SELECT BankFK, tranDate, tranDetail, moneyOut, moneyIn, Balance
FROM (SELECT 1201 AS BankFK, cDate(F2) AS tranDate, Trim([F3]) AS tranDetail, F4 AS moneyOut, F5 AS moneyIn, F6 AS Balance
              FROM [TEXT;DATABASE=C:\Bank Statements;HDR=no].[CSV_Export_00032374_20230620.csv]
               WHERE IsDate([F2])=True and isnumeric([F3]) = false)  AS [imp]

LEFT JOIN tblBankTrans ON ([imp].Balance = tblBankTrans.Balance) AND ([imp].tranDetail = tblBankTrans.tranDetail) AND ([imp].tranDate = tblBankTrans.tranDate) AND ([imp].BankFK = tblBankTrans.BankFK)

WHERE tblBankTrans.BankTranPK Is Null

1201 is the ledger code and is determined by the user stating which bank's statement they want to import

this part varies from one bank to another and is stored in the bank record

cDate(F2) AS tranDate, Trim([F3]) AS tranDetail, F4 AS moneyOut, F5 AS moneyIn, F6 AS Balance

this criteria eliminates all none transactional data - so column names default to F1, F2 etc
WHERE IsDate([F2])=True and isnumeric([F3]) = false

Whilst the left join on 3 fields and the criteria WHERE tblBankTrans.BankTranPK Is Null eliminates duplicates.

 

[writeprivate]

Thanks GaP42

The reason for not using standard formats like QIF is that the relevant banks are of the swiss variety and offer very limited and non-structure exports only in csv/xlsx. It is not me that wants to incorporate headers, but the bank which includes rows at the top and bottom which are non standard - things like a dsiclaimer. For sure, i can manipulate with some Excel VBA when i get more proficient in this platform. I need to add other columns too for things like account number and currency (which is natively included only in the header)

Thanks CJ_London

I like your code and logic. I will look more at how i can import the files as they are downloaded and code around the various anomalies. Having the different SQL in a table based on the selected bank is a genius idea - I will definately make use of this thinking.


For now, I have written a Sub which works perfectly. I include below for future readers of the thread.

Sub ImportExcelFiles()

    Dim db As DAO.Database

    Dim rst As DAO.Recordset

    Dim xlApp As Object

    Dim xlWorkbook As Object

    Dim xlWorksheet As Object

    Dim strFolderPath As String

    Dim strFileName As String

 

    ' Set the folder path containing the statement files

    strFolderPath = "C:\Users\User\Documents\Excel Tables\statements\"

 

    ' Initialize Excel objects

    Set xlApp = CreateObject("Excel.Application")

    xlApp.DisplayAlerts = False

    xlApp.Visible = False

 

    ' Set the database and recordset objects

    Set db = CurrentDb()

    Set rst = db.OpenRecordset("tblBankStatements")

 

    ' Get the first file in the folder

    strFileName = Dir(strFolderPath & "*.xlsx")

 

    ' Process each statement file in the folder

    Do While strFileName <> ""

        ' Open the Excel file

        Set xlWorkbook = xlApp.Workbooks.Open(strFolderPath & strFileName)

     

        ' Process the worksheet

        Set xlWorksheet = xlWorkbook.Sheets("Cash balances") ' Adjust sheet name as per your layout

     

        ' Loop through the rows in the worksheet

        Dim i As Integer

        For i = 2 To xlWorksheet.UsedRange.Rows.Count ' Assuming the data starts from row 2

            ' Extract the relevant information from the Excel file

            Dim transactionDate As Date

            Dim portfolioNumber As Double

            Dim accountNumber As Double

            Dim valueDate As Date

            Dim transactionRef As Double

            Dim transactionDesc As String

            Dim debit As Double

            Dim credit As Double

            Dim rowImported As Boolean

         

            transactionDate = xlWorksheet.Cells(i, "A").Value

            portfolioNumber = xlWorksheet.Cells(i, "B").Value

            accountNumber = xlWorksheet.Cells(i, "C").Value

            valueDate = xlWorksheet.Cells(i, "D").Value

            transactionRef = xlWorksheet.Cells(i, "E").Value

            transactionDesc = xlWorksheet.Cells(i, "F").Value

         

            If IsNumeric(xlWorksheet.Cells(i, "G").Value) Then

                debit = CDbl(xlWorksheet.Cells(i, "G").Value)

            Else

                debit = 0

            End If

         

            If IsNumeric(xlWorksheet.Cells(i, "H").Value) Then

                credit = CDbl(xlWorksheet.Cells(i, "H").Value)

            Else

                credit = 0

            End If

         

            rowImported = xlWorksheet.Cells(i, "I").Value

         

            ' Check if the record has already been imported based on the flag

            If Not rowImported Then

                ' Insert the data into the Access table

                rst.AddNew

                rst!trade_date = transactionDate

                rst!portfolio_number = portfolioNumber

                rst!account_number = accountNumber

                rst!value_date = valueDate

                rst!transaction_ref = transactionRef

                rst!Description = transactionDesc

                rst!debit = debit

                rst!credit = credit

                rst!imported = True

                rst.Update

             

                ' Mark the corresponding "Imported" field as True in the Excel sheet

                xlWorksheet.Cells(i, "I").Value = True

             

                ' Save the changes made to the worksheet

                xlWorkbook.Save

            End If

        Next i

     

        ' Close the Excel file without saving changes

        xlWorkbook.Close False

     

        ' Get the next file in the folder

        strFileName = Dir

    Loop

 

    ' Clean up objects

    rst.Close

    Set rst = Nothing

    Set db = Nothing

 

    ' Quit Excel and release the objects

    xlApp.Quit

    Set xlWorksheet = Nothing

    Set xlWorkbook = Nothing

    Set xlApp = Nothing

 

    MsgBox "Import completed successfully."

End Sub

 

[Gasman]

Hmm, some Dims are inside the For Next Loop?

ChatGPT is not that smart then?

 

[CJ_London]

Up to you - it’s your app.

if you still want to populate an excel file then look at using copyfromrecordset - much faster and less prone to error

 

[writeprivate]

Didn't see your reply until just now. I have been refactoring for the last 3 hours to incorporate your thinking. Much more complex code but works brilliantly and feels really solid. Now i just dump the raw downloaded spreadsheet into a known source folder and the code gets all the bits of data and ignores the irrelevant rows. Have a log file to boot showing how may records imported and then the spreadsheet file is moved to an 'imported' folder (even though there is build in protection from duplicates as the filename of each spreadsheet file is passed to the imported table and it wont allow dupes) - but the folder move is good for the human eyeball and also speeds up the routine as only opening files ready to import.

Appreciate the brainstorm - got me thinking about this differently.

@Gasman - ChatGPT is pretty dumb - but it helps with laying out the skeleton - more of a speed thing

 

[CJ_London]

still think importing an excel file rather than .csv has it's issues. One of my client's banks is foreign (client in UK, bank is Spanish). That bank exports a .xls (not .xlsx). The problem is that the format for the currency values includes a £ symbol, but when trying to import it is interpreted as 'ao' (or something like that, it's been a while) I assume because it has been generated with a Spanish version of Excel and something had been 'lost in translation' - however .csv file, no problem.

Excel is not datatyped which means that when importing to a database (which is strongly datatyped) the datatype can be misinterpreted. I learned many years ago to avoid importing from excel wherever possible. With a .csv, in the worst case data is typed as text which can then easily be reinterpreted as the correct datatype as part of the import process.

A few years ago a client was having intermittent problems importing a .csv file partly because they thought it was an excel file (the default app for opening .csv's). They would open the file (in Excel) check it was 'ok' then rather than just closing they occasionally saved the file again (as a .csv). This caused some of the data to be slightly modified because Excel had interpreted the data to be something it was not and applied additional formatting causing incorrect values to be imported. Dates/times, yes/no and currency fields were the main culprits. Even then it wouldn't happen every time, it all depended what was in the first 8 rows of data which is what Access will use to determine datatype.

Just backing up a little, a monthly bank statement should be OK, but if (as my clients do) they regularly import of transactions 2 or 3 times a week or even daily or several times a day, most banks provide a 'from date of last import' option. So if they run that export at say midday today, they will get all transactions up until midday today. If they run the report again in 3 days time (it doesn't really matter when) they will get repeats of the transactions from this morning. This is why you need to exclude transactions already imported.

As you are doing I move imported files to an archive folder and have checks to verify that specific file has not previously been imported - multiple same day downloads can generate the same name for the file, and there may be reasons why you want to reimport data from an already imported file. However I don't bother with a log file, instead I include a timestamp in the banktran table so I know exactly when a record was imported.

 

[Gasman]

@Gasman - ChatGPT is pretty dumb - but it helps with laying out the skeleton - more of a speed thing

Well it is not wrong, but not tidy code as I would think of it.
I have seen better code that it has made that others have posted.

I do not even know what multiple DIMming a variable would do?, perhaps clear it out? If I get bothered, I might test myself.

 

[Cotswold]

Personally, in this type of application I would use the OFX file import/export. It is basically a standard layout between banks, where CSVs can change and will be (depending on your code of course) less dependent on users than a CSV could be. CJ_London has mentioned issues that can turn your imports into a can of worms. I find the OFX easier to handle than CSVs in banking and investment applications.

 

[CJ_London]

I would use the OFX file

Not all banks use the same formats - for my client one bank you can export a .csv or .ofx, for another a .xls or .qif

 

[Cotswold]

Not all banks use the same formats - for my client one bank you can export a .csv or .ofx, for another a .xls or .qif

I understood that the QIF file had issues in todays world, currency types amongst them and was being dropped by Quicken. I've been out of it for a while but I recall reading somewhere that Quicken were shifting to QFX which I presume is a plagiarised OFX. I'm really quite surprised a bank will not provide a version of an OFX.

But the nice thing about standards is that there are so many of them.