Importing data into MYOB AccountEdge


I was recently asked by a client to automate the process of transferring monthly sales data tracked in FileMaker Pro to MYOB AccountEdge. The fields that the client wanted to export from FileMaker included the customer’s Card ID, Job Number, Description, Account Number, Amount, Salesperson Last Name, Referral Source, and Applied to Date.

In some cases, the client receives income (customer fees) and books expenses (sales commissions) in the same transaction. In other instances, only income is booked. Given the nature of the task, my client felt that using Microsoft Excel 2004 as an intermediary program between FileMaker and MYOB would facilitate the data conversion. Handling both scenarios proved to be a challenge.

I approached the assignment by first addressing the income-only scenario. Researching the MYOB discussion boards, I noted that the accounting program accepts the import of tab-delimited text files and requires each transaction to be separated by a blank row.

I used Excel’s macro recorder and some pre-defined scripts I found on various web sites to document each step I would need to follow to manually produce blank lines in my test file. I ultimately produced an Excel macro that was something like 25 lines long and a bit cryptic to understand.

Figuring I could do better than this, I searched the Internet again and came up a macro that was far easier to decipher that used a basic Do While loop that I could easily adapt for both scenarios.

Sub MYOBSingleTransaction()
‘ MYOBSingleTransaction Macro

‘Select first cell to enter blank row
Range(“A3”).Select

Do While ActiveCell.Value <> Empty

‘Insert blank row
ActiveCell.EntireRow.Insert

‘Move down 2 cells
ActiveCell.Offset(2, 0).Select

Loop

‘Return to home cell
Range(“A1”).Select

End Sub

Sub MYOBGroupedTransaction()
‘ MYOBGroupedTransaction Macro

‘Select first cell to enter blank row
Range(“A4”).Select

Do While ActiveCell.Value <> Empty

‘Insert blank row
ActiveCell.EntireRow.Insert

‘Move down 3 cells
ActiveCell.Offset(3, 0).Select

Loop

‘Return to home cell
Range(“A1”).Select

End Sub

There are a couple of “gotchas” that need to be taken into account when importing service sales data into MYOB.

1). The customer’s Card ID has to be pre-defined in MYOB before importing. In my test environment, I manually created a Card ID of “999999” that I associated with any sale to a new customer.

2). The Job Number, Salesperson, and Referral Source also have to be pre-defined in MYOB before importing.

While MYOB keeps tabs of each Salesperson by both first and last names, my client’s FileMaker database lists each Salesperson in a single field. Consequently, I only entered data for Salesperson last name in MYOB and left the first name field blank.

Here is how my test data looked like before

Card ID Description Account # Amount Job Salesperson Applied to Date Referral Source
100001 Referral: Jimmy D 41410 $250.00 101 Johnson $100.00 Barbara
100001 Credit: Jane D 42410 ($125.00) 101 Johnson Barbara
100002 Referral: Sally G 41410 $750.00 102 Johnson $200.00 Barbara
100002 Credit: Jane D 42410 ($375.00) 102 Johnson Barbara
999999 Referral: Bobbi S 41410 $300.00 103 Johnson $150.00 Barbara
999999 Credit: Jenny L 42410 ($150.00) 103 Johnson Barbara
999999 Referral: Sally G 41410 $200.00 104 Johnson $100.00 Barbara
999999 Credit: Jenny L 42410 ($100.00) 104 Johnson Barbara

…and after I ran the grouped transaction macro:

Card ID Description Account # Amount Job Salesperson Applied to Date Referral Source
100001 Referral: Jimmy D 41410 $250.00 101 Johnson $100.00 Barbara
100001 Credit: Jane D 42410 ($125.00) 101 Johnson Barbara

100002 Referral: Sally G 41410 $750.00 102 Johnson $200.00 Barbara
100002 Credit: Jane D 42410 ($375.00) 102 Johnson Barbara

999999 Referral: Bobbi S 41410 $300.00 103 Johnson $150.00 Barbara
999999 Credit: Jenny L 42410 ($150.00) 103 Johnson Barbara

999999 Referral: Sally G 41410 $200.00 104 Johnson $100.00 Barbara
999999 Credit: Jenny L 42410 ($100.00) 104 Johnson Barbara

Job 104 appeared in the MYOB Sales Register as follows:

Advertisements

Leave a comment

Filed under Databases, Third Party Software

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s