We help IT Professionals succeed at work.

Access Data to QuicBooks

dougp23
dougp23 asked
on
So, I have this table in Access, full of data.  I want to use VBA to reformat the table so I can export it to Excel, in a format that QuickBooks can read.  Here's what I got:

Acct#  Lot#  DaysIn  ChargePerDay
300    1     12      1.50
300    2     9       4.00
300    3     1       0.50
301    1     30      8.12

But, here's what QuickBooks wants:

BEGINTRANS!
300    1     12      1.50
300    2     9       4.00
300    3     1       0.50
ENDTRANS!
BEGINTRANS!
301    1     30      8.12

Getting the data written to a temporary table is easy.  The problem is inserting these empty records after each acct number changes.  The entire record must be nothing but BEGINTRANS! or ENDTRANS!.  I tried setting up an empty field (called QBTRANS), which would write in the BEGIN or ENDTRANS!.  But how do I go through the table looking for each acct# change?  And how would I add these BEGIN and ENDTRANS things?

I'd appreciate any help at all!
Comment
Watch Question

Commented:
Not real familiar with QB's but are you saying that you want the output to be EXACTLY as you see in your example

For instance in excel  
Column A would contain
BEGINTRANS!
300
300
300
ENDTRANS!
BEGINTRANS!
301

Column B would contain
1
2
3

1

Column C would contain 12
9
1

30

If this is so I would do the following

Create a report based on your query or table.

Group by Account#
On the group make sure you say that each group should have a header and a footer.

Place a text box in the Group Header That says BEGINTRANS!
Place the data in the detail seciton.
Place a text box in the Group Footer that says ENDTRANS!

Do not place a Report Header/Footer
Do not place a Page Header/Footer

Run the report.  You should have the output desired.

Once the report is open you can go to File and SaveAs / Export and export it to Excel

This should accomplish this for you.

Andy


What you may be looking for is a check in your loop.  As you navigate down your recordset, check each record's account # to see if it matches the previous.  If so, insert the EndTrans/BeginTrans lines.  Then set your checker value to the new account#.  

Also, don't know much about QB either but wouldn't a text file work?  FileSystemObject?
Sorry, the first 2 words of my 3rd sentence should be "If not, ".  sorry for the confusion.

Commented:
Just a minor note - the Microsoft web site for Office updates contains an Access-Quickbooks importer/exporter utility that is free for the download.

Author

Commented:
I like the idea of traversing the table comparing the previous record to the current one.  However, when I insert that blank record, it gets inserted at the END of the recordset.  I must then find a way to go back to where I was!  (There may be multiple entries for the same account#, so I can't ref by acct # really).

I didn't see the Microsoft utility.  Do you have the link?  Maybe they took it down, they do that a lot!

Thanks!
Here's some code that should work.  Just make sure you save the empty spreadsheet "c:\trans.xls" first before you run.

Sub QB_Export()
On Error GoTo ErrorHandler
Dim dbs As Database, strSQL As String, rst As Recordset
Dim mysheet As Object, xlApp As Object, i As Integer, strAcct As String

Set xlApp = CreateObject("Excel.Application")
Set mysheet = xlApp.workbooks.Open("c:\trans.xls").Sheets(1)
Set dbs = CurrentDb
strSQL = "select * from MyTable where MyTable.[Acct#] is not null order by MyTable.[Acct#]"
Set rst = dbs.OpenRecordset(strSQL)
With rst
    While Not .EOF
        i = i + 1
        mysheet.cells(i, 1) = "BEGINTRANS!"
        i = i + 1
        Do
            mysheet.cells(i, 1) = ![Acct#]
            mysheet.cells(i, 2) = ![Lot#]
            mysheet.cells(i, 3) = ![DaysIn]
            mysheet.cells(i, 4) = ![ChargePerDay]
            strAcct = ![Acct#]
            .MoveNext
            i = i + 1
            If .EOF Then Exit Do
        Loop While (strAcct = ![Acct#])
        mysheet.cells(i, 1) = "ENDTRANS!"
    Wend
End With

mysheet.Application.activeworkbook.Save
mysheet.Application.activeworkbook.Close
xlApp.Quit

SubExit:
On Error Resume Next
Set dbs = Nothing
Set rst = Nothing
Set mysheet = Nothing
Exit Sub
ErrorHandler:
MsgBox "Error " & Err.Number & vbNewLine & Err.Description
Resume SubExit
End Sub

Explore More ContentExplore courses, solutions, and other research materials related to this topic.