Joel-

Sorry it took so long for me to get back to you. I have been trying to
figure out what I have tried and what exactly I needed. So here it goes.

The only line I am looking to change is part of the !TRNS line.

Here is your orginal !TRNS line in the iif file that gets created when I run
your macro:
!TRNS,TRNSTYPE,TRNSID,DATE,ACCNT,NAME,AMOUNT,DOCNU M,TOPRINT,TAXABLE,ADDR1

Here is the !TRNS data line I am getting when I run the macro:
TRNS,INVOICE,1110,08/01/08,Accounts Receivable - Customers,Driveline,2842.92

Here is the problem:
After the amount of 2,842.92 there should be a DOCNUM that is not coming
over. This data should be from column B from the excel data.

Also I am trying to add a "PONUM" to my data. This data should be from
column H from the excel data.


Here is how I got it to work:
I took your origin !TRANS Line and added the PONUM after DOCNUM:
TRNS,TRNSTYPE,TRNSID,DATE,ACCNT,NAME,AMOUNT,DOCNUM ,PONUM,TOPRINT,TAXABLE,ADDR1

In the data line I entered the following:
TRNS,INVOICE,1110,08/01/08,Accounts Receivable -
Customers,Driveline,2842.92,2562,1234

What I need is to have the DOCNUM and the PONUM to show up in the iif file
as shown above,

Hope this makes sense!

Thanks again,

Tom

"Joel" wrote:

See comments below. I think four changes are needed

1) Add INVOICENUMBER to first Header
2) Add INVOICENUMBER to second Header
3) Get the InvoiceNumber for one of the columns on the worksheet. Not
sure which one
4) Add Invoice Number to data line


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)

'----------------------------------------------------------------------------------
'Do we need to add Invoice Number in Header Row below?
' OutPutLine = "!TRNS,TYPE,DATE,ACCNT,NAME," & _
' "AMOUNT,INVOICENUMBER,DOCNUM,TOPRINT,TAXABLE,ADDR1 "
'----------------------------------------------------------------------------------
OutPutLine = "!TRNS,TYPE,DATE,ACCNT,NAME," & _
"AMOUNT,DOCNUM,TOPRINT,TAXABLE,ADDR1"
tswrite.writeline OutPutLine

'----------------------------------------------------------------------------------
'Do we need to add Invoice Number in Header Row below?
' OutPutLine = "!SPL,TYPE,DATE,ACCNT,NAME," & _
' "AMOUNT,INVOICENUMBER,DOCNUM,QNTY,PRICE,INVITE M"
'----------------------------------------------------------------------------------

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)
'----------------------------------------------------------------------------------
'Which column has the Invoice Number.
'we Need to add a line like This
'
' InvoiceNumber = Range("?" & RowCount)
'
'Then the line below need to change to this where Invoice Number is after
Amount
'
' OutPutLine = "SPL,INVOICE," & StrDate & ",Sales,," & Amount & "," & _
' InvoiceNumber & "," & BOL & "," & Quant & "," & Price &
"," & _
' PartNumber
'
'----------------------------------------------------------------------------------

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:

In quickbooks there is a field for invoice #, this is where I would like this
number to fill in when imported. I did not have the invoice field on my
original data sheet because I did not need it imported at the time. I have
added this field in the column next to the amount.

"Joel" wrote:

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