|
|
|
|
|
|
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()
Application.ScreenUpdating = False
ActiveWorkbook.Sheets("Journal").Select
Set MyJournal = ActiveSheet.Range("DocNum")
Sheets("QB Journal").Select
Columns("D:D").Select
Selection.NumberFormat = "mm/dd/yy"
Columns("H:H").Select
Selection.NumberFormat = "0.00"
Range("A1").Select
Sheets("QB Journal").Copy
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="C:\QB Journal.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
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
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveWorkbook.Save
ActiveSheet.Range("A4").Value = "TRNS"
ActiveWorkbook.Save
ActiveWorkbook.SaveAs Filename:= _
"C:\" & MyJournal & ".iif", FileFormat:= _
xlTextMSDOS, CreateBackup:=False
ActiveWorkbook.Close
Application.DisplayAlerts = True
Sheets("Menu").Select
Range("C1").Select
Application.ScreenUpdating = True
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.
|
|