Export Format

B

basic

Please Help! I hope the formatting below is readable. I am trying to create a
spreadsheet to import into quickbooks. I have the following table created in
excel, but I am trying to set up automatic formating to quickbooks.

My original data looks like this:

ShipDate BOL PartNumber Quantity Price Amount
5-7-08 9497 Front 4680 9.85 46099.59
5-7-08 9497 Rear 450 8.98 4039.41
5-7-08 9497 Cap 1800 2.40 4314.29
5-9-08 9494 Front 2880 9.85 28368.98
5-9-08 9494 Rear 450 8.98 4039.41

The following format breaks the excel spreadsheet into individual
transactions by BOL. The only calculation needed is the amount on the !TRNS
line. This adds up all the amounts for each BOL as a total. Any information
that is in the example below but is not taken from the data above will be a
constant on all transactions.

!TRNS TYPE DATE ACCNT NAME AMOUNT DOCNUM TOPRINT TAXABLE ADDR1
!SPL TYPE DATE ACCNT NAME AMOUNT DOCNUM QNTY PRICE INVITEM
!ENDTRNS
TRNS INVOICE 5/9/2008 AR ABC 32408.39 9494
SPL INVOICE 5/9/2008 Sales -28368.98 9494 2880 9.85 Front
SPL INVOICE 5/9/2008 Sales -4039.41 9494 450 8.98 Rear
ENDTRNS
TRNS INVOICE 5/7/2008 AR ABC 54453.29 9497
SPL INVOICE 5/7/2008 Sales -46099.59 9497 4680 9.85 Front
SPL INVOICE 5/7/2008 Sales -4039.41 9497 450 8.98 Rear
SPL INVOICE 5/7/2008 Sales -4314.29 9497 1800 2.40 Cap
ENDTRNS
 
J

Joel

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:="Text Files (*.txt), *.txt")

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" & TABChr & "INVOICE" & TABChr & StrDate &
TABChr & _
"AR" & TABChr & COMPANY & TABChr & TotalAmount &
TABChr & "BOL"
tswrite.writeline OutPutLine
End If
PartNumber = Range("C" & RowCount)
Quant = Range("D" & RowCount)
Price = Range("E" & RowCount)
Amount = Range("F" & RowCount)
OutPutLine = "SPL" & TABChr & "INVOICE" & TABChr & StrDate & TABChr & _
"Sales" & TABChr & TABChr & TABChr & Amount & TABChr &
BOL & _
Quant & TABChr & Price & TABChr & 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
 
B

basic

Joel-

Thanks for the help. Your code is way over my head to try to troubleshoot.
I am running into a compile error; Syntax error.


The following shows in red when I copy it into a macro. It then gives the
"compiler error/Syntax error when I run it:
Red:
TotalAmount = WorksheetFunction.SumIf(Columns("A"), TransDate,
Columns("F"))
OutPutLine = "TRNS" & TABChr & "INVOICE" & TABChr & StrDate &
TABChr & _
"AR" & TABChr & COMPANY & TABChr & TotalAmount &
TABChr & "BOL"
tswrite.writeline OutPutLine
End If
PartNumber = Range("C" & RowCount)
Quant = Range("D" & RowCount)
Price = Range("E" & RowCount)
Amount = Range("F" & RowCount)
Red: OutPutLine = "SPL" & TABChr & "INVOICE" & TABChr & StrDate &
TABChr & _
"Sales" & TABChr & TABChr & TABChr & Amount & TABChr &
BOL & _
Quant & TABChr & Price & TABChr & PartNumber
 
J

Joel

The problem was with the lenght of the lines. Whenyou post code that has
more than 80 characters on a line the line wraps to two lines. I fixed the
code below so this doesn't happen. I added the underline character
(continuation line) at the end of all of the long lines to prevent errors.

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:="Text Files (*.txt), *.txt")

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" & TABChr & "INVOICE" & TABChr & _
StrDate & TABChr & "AR" & TABChr & COMPANY & _
TABChr & TotalAmount & TABChr & "BOL"
tswrite.writeline OutPutLine
End If
PartNumber = Range("C" & RowCount)
Quant = Range("D" & RowCount)
Price = Range("E" & RowCount)
Amount = Range("F" & RowCount)
OutPutLine = "SPL" & TABChr & "INVOICE" & TABChr & StrDate & TABChr & _
"Sales" & TABChr & TABChr & TABChr & Amount & TABChr & _
BOL & Quant & TABChr & Price & TABChr & 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
 
B

basic

Joel-
Very impressive! It is almost exactly what I am looking for.
There are just a couple problems.
1. The "Total Amount" in the first line is ok, but the individual totals
which should be below the "Total Amount" are moved to the right one column.
Also the BOL and the Quantity columns are getting combined into one column.

2. If possible the top three header rows you created should be all
individual cells going across, they are actually headers for the detail.

Thanks again!
 
J

Joel

Is the data meant to go in a spreadsheet or do you need it to go in to a text
file? You asked for an export file which is general intepreted as a text
file. I seperated the columns with tabs because you original output data was
TAB delimited. I can change the code to be fixed spaced or delimited any way
you like. I don't know which is the best method. From your lasted
description you are referencing cell in a spreadsheet so I'm a little
confused. If you are importing the data into a worksheet, then try using TAB
delimited as the option for importing.
 
B

basic

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 it’s 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
YEARTODATE WAGEBASE

Should be in cell A3
!ENDTRNS

The transactions should line up under the correct headings when complete.

Again thanks for all your help.
 
J

Joel

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
 
B

basic

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!
 
J

Joel

You original data didn't have two different BOL on the same date so I didn't
know to seperate the BOL's. The new code will seperate sections eiuther if
the DATE or the BOL don't match in two consecutive rows.

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

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)
COMPANY = Range("G" & 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
 
J

Joel

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
 
B

basic

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.
 
J

Joel

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
 
B

basic

Joel said:
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
Click to expand...
 
B

basic

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
 
J

Joel

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?
 
B

basic

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.
 
J

Joel

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,INVITEM
'----------------------------------------------------------------------------------

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
 
B

basic

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,DOCNUM,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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

sql INSERT INTO record location 1
Offset formula 2
Outlook connector crashes outlook 0
Outlook crashes with Outlook connector 2002 0

Top