VBA Express : Excel - Quickbooks import preparation

Excel

Quickbooks import preparation - payroll example

Ease of Use

Easy

Version tested with

97, 2000, 2003

Submitted by:

Brandtrock

Description:

Takes information from a sheet, formats the data, removes unnecessary lines then saves the result as an .iif file appropriate for importing to Quickbooks.

Discussion:

This workbook holds four worksheets that prepare a reconciliation of a third party payroll processing report, prepares a haard copy of a journal entry and a unique .iif file suitable for importing into Quickbooks. This import must be done manually; this example does not automate the Quickbooks side of the import process. QuickBooks and their development company Intuit have recently become much more open to coders writing applications to automate their product. The software developer's kit available from Intuit makes full automation of the import process possible. This example simply takes data in a typically styled reconciliation type workbook and formats the data as required by QuickBooks into an acceptable import format (the .iif file). The specific fields to import can be changed to accommodate a different third party payroll provider and/or the reporting desired by the business entity. The basic functionality of this code is to demonstrate how a worksheet that may have empty data can be whittled down to an appropriate journal entry import for QuickBooks.

Code:

instructions for use

			Option Explicit 
Dim MyJournal As Object 
Dim CurrentCell As Object 
Dim NextCell As Object 
 
Sub Transfer_to_QB_Import_File() 
     ' This will transfer the Payroll Journal to a QB iif file
     ' Turn off screen updates
     
    Application.ScreenUpdating = False 
     
    ActiveWorkbook.Sheets("Journal").Select 
     
    Set MyJournal = ActiveSheet.Range("DocNum") 
     
     ' Sets Proper Format for data import
    Sheets("QB Journal").Select 
    Columns("D:D").Select 
    Selection.NumberFormat = "mm/dd/yy" 
    Columns("H:H").Select 
    Selection.NumberFormat = "0.00" 
    Range("A1").Select 
     
     ' Copies "QB Journal" worksheet to a new Workbook
     
    Sheets("QB Journal").Copy 
     
     ' Turns off warning messages
     ' This allows file replacement without warning
     
    Application.DisplayAlerts = False 
     
     
    ActiveWorkbook.SaveAs Filename:="C:\QB Journal.xls", FileFormat:= _ 
    xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ 
    , CreateBackup:=False 
     
     
     ' Selects the copied worksheet for processing
     
    ActiveWorkbook.Sheets("QB Journal").Select 
    Range("A1").Select 
     
    Set CurrentCell = ActiveSheet.Range("H4") 
     
    Do While Not IsEmpty(CurrentCell) 
        Set NextCell = CurrentCell.Offset(1, 0) 
         
        If CurrentCell.Value = 0 Then 
            CurrentCell.EntireRow.Delete 
        End If 
         
        Set CurrentCell = NextCell 
    Loop 
     
     
     ' Copies all cells and removes all formula references
     
    Cells.Select 
    Selection.Copy 
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ 
    False, Transpose:=False 
     
     ' Saves the processed worksheet without the formula references
     
    ActiveWorkbook.Save 
     
     ' Makes certain that import file starts with a TRNS code
     
    ActiveSheet.Range("A4").Value = "TRNS" 
     
     ' Saves the processed worksheet
     
    ActiveWorkbook.Save 
     
     ' Saves the processed worksheet as a QuickBooks .iif import file
     
     ' Change this to the appropriate pathname for your file.
     
    ActiveWorkbook.SaveAs Filename:= _ 
    "C:\" & MyJournal & ".iif", FileFormat:= _ 
    xlTextMSDOS, CreateBackup:=False 
     
     ' Closes the new QuickBooks .iif import file
     
    ActiveWorkbook.Close 
     
     ' Turns on warning messages
     
    Application.DisplayAlerts = True 
     
     
     ' Returns the application to the Menu sheet
     
    Sheets("Menu").Select 
    Range("C1").Select 
     
     ' Turn screen updates on
     
    Application.ScreenUpdating = True 
     
     ' Displays a message telling the user a file is now ready for import
     
    MsgBox "Payroll File is ready for Import into QuickBooks.", _ 
    vbOKOnly, "Process Completed Successfully" 
End Sub

How to use:

The example file has sheets named "Menu", "Input Sheet", "Journal", "Summary", and "QB Journal". The import routine references "Menu", "Journal", and "QB Journal". Amend the code to reflect the titles of sheets in your file as necessary. Also, the named range "DocNum" is used in the code. This range is used to name the file when it is saved. A range referencing this Journal Entry id must be included in the sheet this is used on.
Copy the above code.
Open your reconciliation workbook.
Press Alt + F11 to open the Visual Basic Editor (VBE).
From the Menu, choose Insert-Module.
Paste the code into the right-hand code window.
Close the VBE, save the file if desired.
In the example file, this macro is assigned to a graphic on the "Menu" sheet. Pressing the graphic launches the macro. This method can be used or the macro can be assigned to a key sequence, or manually selected by pressing Alt+F8, selecting Transfer_to_QB_Import_File and pressing OK.

Test the code:

The simplest test is to run the macro from the example file to see what it produces.
Adapting it to a different reconciliation format is easily done once the mechanics are seen.
Place an apostrophe before the line: Application.ScreenUpdating = False so you can watch what goes on behind the scenes while the macro runs.
The QB field names in the top two rows as well as the QB keywords in the left are the key to this file working correctly. Additionally, the fields must be in the proper format (mm/dd/yy, 0.00) for Quickbooks to handle.
The macro basically eliminates zero dollar rows from the data present, formats the data as the types required by Quickbooks, and saves the import file with a unique name.

Sample File:

QBPayrollImport.zip 63.01KB

Approved by mdmackillop

This entry has been viewed 196 times.