We all know how frustrating it is to try to import a file into QuickBooks and receive an error from Intuit saying that something is wrong with the source data. If you have spent hours attempting to clean up source data files to ensure they are in the right format to import into QuickBooks, know that there is an easier way when you take advantage of Excel’s VBA Macros.
It may be best to explain with a specific example. Let’s say that a third-party software creates an Excel file that you need to import into QuickBooks using Transaction Pro, but the file that you export from your software returns all sales tax items as = “SD8.5” when your QuickBooks Sales Tax Item appears as: “San Diego 8.50%.” This will create a mismatch and QuickBooks will reject your import.
Example:
Your Quickbooks Sales Tax Item = San Diego 8.50%
Your Source data (Excel) Sales Tax Item = SD8.5
Quickbooks Sales Tax Items:
Source data (Excel) Sales Tax Items:
When you attempt to import this Excel file into Transaction Pro, QuickBooks will return the below error:
To fix this issue, here is a simple “Find and Replace” Excel VBA Macro to use. You can simply copy the text below and replace the values with accurate data:
Sub Replace_TaxItems()
‘ Replace_TaxItems Macro
‘
Columns(“AI:AI”).Select
Selection.Replace What:=”SD8.5″, Replacement:=”San Diego 8.50%”, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
End Sub
Creating the Macro in Excel
To start your macro, use the Record feature to give your macro a name:
Copy and paste the code provided above and replace the values with data that is relevant to your import:
Once you execute this macro, you’ll see that the data was replaced and you can now import your data with success. You can add to the macro and execute as many find and replace statements as needed. In fact, hundreds can be executed in seconds.
Other Uses for Excel VBA Macros
The above is one of many simple functions that can be executed using Excel VBA macros. Other examples include:
- Inserting a Default field
- Changing the format of the data (making data logical for imports)
- Using Excel formulas to calculate multiple fields and insert result into the new field.
- Using IF Statements to make logical formulas
- Joining multiple fields together for more detailed descriptions.
- Splitting a file into multiple files for imports into multiple Quickbooks databases.
- Removing unwanted fields or rows of data
- Creating Lookup tables in Excel that can be used to replace existing data
- Launching Command line scripts automatically import into Transaction Pro
- Any other functions that will solve your import issues.
If you require additional assistance with VBA Macros, please email our Customer Success team at support@transactionpro.com.
