We help IT Professionals succeed at work.

Build IIF for import into Quickbooks using MS Access

tristan69
tristan69 asked
on
917 Views
Last Modified: 2013-11-29
I would like to build an IIF from MS access for import into Quickbooks. Have anyone ever done it before. I have attached the file format below you can open it using a text editor. This file was generated using MS Excel and output to MSDOS text file how do I do the same using MS Access. Before this file can be imported into quickbook it must be save wit an IIF extension and must be a
MSDos text file.
Book2.xls
Comment
Watch Question

Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
tristan69,

1. I'm confused?
"I would like to build an IIF from MS access for import into Quickbooks."
"Build" an IIF what?

IIF in Access stands for (I)mmediate (IF) and is a function, much like the IF function in Excel.
So I can't see how "Building an IIF" would import a file to quickbooks.

2. The file you posted is an Excel file
How does this fit into a question about Access files being imported into Quickbooks as text files?

Please post a sample of you Access Database AND a copy of the *exact* Access Text file output you need.

JeffCoachman
CERTIFIED EXPERT
Top Expert 2016

Commented:

see this thread

https://www.experts-exchange.com/Software/Industry_Specific/Financial/QuickBooks/Q_23531692.html


jeff,  
IIF is the file extension of quickbook file
Quickbooks has a kit you can download with an instruction manual for creating IIF files:

http://support.quickbooks.intuit.com/support/Pages/KnowledgeBaseArticle/bde7b06f

Then you could create code that would generate a text file using data from your access tables as the manual prescribes.
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
capricorn1,

"jeff,
IIF is the file extension of quickbook file"

Oh, OK.
Thanks, I did not know that.
;-)

Jeff

Author

Commented:
ok Guys cool it, let me explain some more first for Jeff I attached an excel file because I had used excel to generate an MSDOStext file from a link I downloaded on the net. The attached files bear the format that is required. You could import the spreadsheet into an Access table the important thing here is that the first three rows remain in that order.  These are Kind of header rows, next the tranaction begin with TRNS only on the first rows after the three header rows all other rows of the same transaction begin with SPL then the trans action ends with the word END some thing like that. Now the only thing to do is to export the recordset to a MSDOS text file with a .IIF extension. I have finish all except the portion about exporting the file. the table is the same as the excel spread sheet it does not really matter what you called the columns because the column header are not need but the rows have to be number so that they are output in the same order as the rows in the spread sheet. Hope this explains it a little better.

regards

Tristan 69
Here is a function that will write text passed to it to a file:
Sub WriteToTextFile(varData As Variant)
Set fso = CreateObject("Scripting.filesystemobject")
 
Set fFile = fso.opentextfile("c:\test.iif", 2, True)
 
fFile.Write varData
 
fFile.Close
 
End Sub

Open in new window

Here is an improved version of the function that gives you the option to append too:
Sub WriteToTextFile(varData As Variant, strPathName As String, intIOMode As Integer)
' IOMode=2 for write 8 for append.  File must already exist to append
Set fso = CreateObject("Scripting.filesystemobject")
 
Set fFile = fso.opentextfile(strPathName, intIOMode, True)
 
fFile.Write varData
 
fFile.Close
 
End Sub

Open in new window

Author

Commented:
Hi vincem1099 how do I write the entire recordset using your function.

This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.