I will be able to help. I looked back at our previous postings and could not
figure out which column Is the Invoice Number. What is the difference betwnn
Invoice Section (look at your posting today) and Invoice Column?

"basic" wrote:

Yes, I must have cleared my post before sending it.

I now need to add an invoice number to my data. I have tried this but I am
having trouble getting it to fill in in the Invoice section. Would you be
able to help me with this problem.

Thanks,

Tom

"Joel" wrote:

I just got a posting form you with no comments? Did you forget to include
something?

"basic" wrote:



"Joel" wrote:

I think your totals may be incorrect. Try this new code. Also the code
relies on the data being sorted which can be added into the macro. The macro
can also do the import of the data from Access.

Sub ConvertQuickbook()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
TABChr = Chr(9)

Set fswrite = CreateObject("Scripting.FileSystemObject")

folder = ThisWorkbook.Path
ChDir (folder)

FNAME = Application.GetSaveAsFilename( _
fileFilter:="Quickbook Files (*.iif), *.iif")

If FNAME < False Then

fswrite.CreateTextFile FNAME
Set fwrite = fswrite.GetFile(FNAME)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)

OutPutLine = "!TRNS,TYPE,DATE,ACCNT,NAME," & _
"AMOUNT,DOCNUM,TOPRINT,TAXABLE,ADDR1"
tswrite.writeline OutPutLine
OutPutLine = "!SPL,TYPE,DATE,ACCNT,NAME," & _
"AMOUNT,DOCNUM,QNTY,PRICE,INVITEM"
tswrite.writeline OutPutLine
OutPutLine = "!ENDTRNS"
tswrite.writeline OutPutLine
tswrite.writeline

LastRow = Range("A" & Rows.Count).End(xlUp).Row
RowCount = 2
Do While Range("A" & RowCount) < ""
TransDate = Range("A" & RowCount)
LastTransDate = Range("A" & (RowCount - 1))
BOL = Range("B" & RowCount)
LastBOL = Range("B" & (RowCount - 1))
If (TransDate < LastTransDate) Or _
(BOL < LastBOL) Then

COMPANY = Range("G" & RowCount)
StrDate = Range("A" & RowCount).Text

TotalAmount = Evaluate("SumProduct(" & _
"--(A2:A" & LastRow & "=DateValue(""" & TransDate & """))," & _
"--(B2:B" & LastRow & "=" & BOL & "),F2:F" & LastRow & ")")

OutPutLine = "TRNS',INVOICE," & _
StrDate & ",AR," & COMPANY & _
"," & TotalAmount & ",BOL"
tswrite.writeline OutPutLine
End If
PartNumber = Range("C" & RowCount)
Quant = Range("D" & RowCount)
Price = Range("E" & RowCount)
Amount = -1 * Range("F" & RowCount)

OutPutLine = "SPL,INVOICE," & StrDate & ",Sales,," & Amount & "," & _
BOL & "," & Quant & "," & Price & "," & PartNumber
tswrite.writeline OutPutLine

NextTransDate = Range("A" & (RowCount + 1))
NextBOL = Range("B" & (RowCount + 1))
If (TransDate < NextTransDate) Or _
(BOL < NextBOL) Then
OutPutLine = "ENDTRNS"
tswrite.writeline OutPutLine
tswrite.writeline
End If
RowCount = RowCount + 1
Loop
End If
tswrite.Close
End Sub



"basic" wrote:

Joel-

Perfect! Thanks for all the help.

Do you know how to change the title of my subject so that others may find
this post? I think your code would help out a lot of people.

"Joel" wrote:

I put the company in the wrong spot in the code. Use this code instead

Sub ConvertQuickbook()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
TABChr = Chr(9)

Set fswrite = CreateObject("Scripting.FileSystemObject")

folder = "C:\temp"
'folder = ThisWorkbook.Path
ChDir (folder)

FNAME = Application.GetSaveAsFilename( _
fileFilter:="Quickbook Files (*.iif), *.iif")

If FNAME < False Then
fswrite.CreateTextFile FNAME
Set fwrite = fswrite.GetFile(FNAME)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)

OutPutLine = "!TRNS,TYPE,DATE,ACCNT,NAME," & _
"AMOUNT,DOCNUM,TOPRINT,TAXABLE,ADDR1"
tswrite.writeline OutPutLine
OutPutLine = "!SPL,TYPE,DATE,ACCNT,NAME," & _
"AMOUNT,DOCNUM,QNTY,PRICE,INVITEM"
tswrite.writeline OutPutLine
OutPutLine = "!ENDTRNS"
tswrite.writeline OutPutLine
tswrite.writeline

RowCount = 2
Do While Range("A" & RowCount) < ""
TransDate = Range("A" & RowCount)
LastTransDate = Range("A" & (RowCount - 1))
BOL = Range("B" & RowCount)
LastBOL = Range("B" & (RowCount - 1))
If (TransDate < LastTransDate) Or _
(BOL < LastBOL) Then

COMPANY = Range("G" & RowCount)
StrDate = Range("A" & RowCount).Text
TotalAmount = WorksheetFunction.SumIf(Columns("A"), _
TransDate, Columns("F"))
OutPutLine = "TRNS',INVOICE," & _
StrDate & ",AR," & COMPANY & _
"," & TotalAmount & ",BOL"
tswrite.writeline OutPutLine
End If
PartNumber = Range("C" & RowCount)
Quant = Range("D" & RowCount)
Price = Range("E" & RowCount)
Amount = -1 * Range("F" & RowCount)

OutPutLine = "SPL,INVOICE," & StrDate & ",Sales,," & Amount & "," & _
BOL & "," & Quant & "," & Price & "," & PartNumber
tswrite.writeline OutPutLine

NextTransDate = Range("A" & (RowCount + 1))
NextBOL = Range("B" & (RowCount + 1))
If (TransDate < NextTransDate) Or _
(BOL < NextBOL) Then
OutPutLine = "ENDTRNS"
tswrite.writeline OutPutLine
tswrite.writeline
End If
RowCount = RowCount + 1
Loop
End If
tswrite.Close
End Sub

"basic" wrote:

Joel-

Everything is working great except it is not seperating the transactions by
BOL, instead it looks like it is seperating by the ship date.

When I imported it I also realized that I had left out two other adjustments.
1. Instead of having the Customer default to "ABC", can you make it so it
picks up the actual customer that is in column G?

2. Can you make the amount entered in the !SPl line a negitive. (Example:
-2500.00). The amount in the !TRNS line should remain positive.

I hope this is the last time, but thanks a million for you help!


"Joel" wrote:

The code saves the file as a iif suffix in CVS format. If there is a
problem, you may have to add or eliminate some of the commas.

Sub ConvertQuickbook()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Const COMPANY = "ABC"
TABChr = Chr(9)

Set fswrite = CreateObject("Scripting.FileSystemObject")

folder = "C:\temp"
'folder = ThisWorkbook.Path
ChDir (folder)

FNAME = Application.GetSaveAsFilename( _
fileFilter:="Quickbook Files (*.iif), *.iif")

If FNAME < False Then
fswrite.CreateTextFile FNAME
Set fwrite = fswrite.GetFile(FNAME)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)

OutPutLine = "!TRNS,TYPE,DATE,ACCNT,NAME," & _
"AMOUNT,DOCNUM,TOPRINT,TAXABLE,ADDR1"
tswrite.writeline OutPutLine
OutPutLine = "!SPL,TYPE,DATE,ACCNT,NAME," & _
"AMOUNT,DOCNUM,QNTY,PRICE,INVITEM"
tswrite.writeline OutPutLine
OutPutLine = "!ENDTRNS"
tswrite.writeline OutPutLine
tswrite.writeline

RowCount = 2
Do While Range("A" & RowCount) < ""
TransDate = Range("A" & RowCount)
LastTransDate = Range("A" & (RowCount - 1))
BOL = Range("B" & RowCount)
If TransDate < LastTransDate Then

StrDate = Range("A" & RowCount).Text
TotalAmount = WorksheetFunction.SumIf(Columns("A"), _
TransDate, Columns("F"))
OutPutLine = "TRNS',INVOICE," & _
StrDate & ",AR," & COMPANY & _
"," & TotalAmount & ",BOL"
tswrite.writeline OutPutLine
End If
PartNumber = Range("C" & RowCount)
Quant = Range("D" & RowCount)
Price = Range("E" & RowCount)
Amount = Range("F" & RowCount)
OutPutLine = "SPL,INVOICE," & StrDate & ",Sales,," & Amount & "," & _
BOL & "," & Quant & "," & Price & "," & PartNumber
tswrite.writeline OutPutLine

NextTransDate = Range("A" & (RowCount + 1))
If TransDate < NextTransDate Then
OutPutLine = "ENDTRNS"
tswrite.writeline OutPutLine
tswrite.writeline
End If
RowCount = RowCount + 1
Loop
End If
tswrite.Close
End Sub

"basic" wrote:

Joel-

Sorry I tried to keep it simple for the original posting because I thought
it would be too complicated to explain. Obviously you have proven me wrong.
Here is exactly what I am looking for.

My original data is in Access. I am exporting this data into excel, adding
the neccesary titles and data and then saving it as a cvs file in excel. I
then change the cvs extension to an iif extension in order to import it into
quickbooks. If you have any shortcut ideas please use them.

If you don't have a "better" way then the import template for quickbooks
requires each heading in its own cell. The data that corresponds with the
headings should be in the cells beneath the heading. Here is the exact layout
with headers.

Should be in cells A1..S1
!Trns TRNSID TRNSTYPE DATE ACCNT NAME CLASS AMOUNT DOCNUM
MEMO CLEAR TOPRINT NAMEST ADDR1 ADDR2 ADDR3 TERMS SHIPVIA
SHIPDATE

Should be in cells A2..S2
!Spl SPLID TRNSTYPE DATE ACCNT NAME CLASS AMOUNT DOCNUM
MEMO CLEAR QNTY PRICE INVITEM OTHER1 TAXABLE OTHER2