| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Export Format
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Export Format
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 "basic" wrote: 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Export Format
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 "Joel" wrote: 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 "basic" wrote: 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Export Format
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 "basic" wrote: 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 "Joel" wrote: 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 "basic" wrote: 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Export Format
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! "Joel" wrote: 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 "basic" wrote: 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 "Joel" wrote: 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 "basic" wrote: 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Export Format
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. "basic" wrote: 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! "Joel" wrote: 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 "basic" wrote: 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 "Joel" wrote: 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 "basic" wrote: 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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Export Format
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 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. "Joel" wrote: 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. "basic" wrote: 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! "Joel" wrote: 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 "basic" wrote: 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 "Joel" wrote: 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 "basic" wrote: 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Export Format
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 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. "Joel" wrote: 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. "basic" wrote: 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! "Joel" wrote: 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 "basic" wrote: 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 "Joel" wrote: 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 "basic" wrote: 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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Export Format
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 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. "Joel" wrote: 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. "basic" wrote: 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! "Joel" wrote: 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 "basic" wrote: 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 "Joel" wrote: 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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Export Format
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 "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 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. "Joel" wrote: 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. "basic" wrote: 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! "Joel" wrote: 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 "basic" wrote: 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 "Joel" wrote: 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" |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Export Format
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 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. "Joel" wrote: 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. "basic" wrote: 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! "Joel" wrote: 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 "basic" wrote: 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 "Joel" wrote: 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" |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Export Format
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 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. "Joel" wrote: 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. "basic" wrote: 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! "Joel" wrote: 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 "basic" wrote: |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Export Format
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 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. "Joel" wrote: 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. "basic" wrote: 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! "Joel" wrote: 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 |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Export Format
"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 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. "Joel" wrote: 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. |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Export Format
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 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. "Joel" wrote: 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 |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Export Format
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 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. "Joel" wrote: |
|
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Export Format
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 |
|
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Export Format
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 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 |
|
#19
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Export Format
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 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. |
|
#20
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Export Format
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Export a spreadsheet and keep format | Excel Discussion (Misc queries) | |||
| Inconsistent CSV export format | Excel Discussion (Misc queries) | |||
| Export to outline format | Excel Worksheet Functions | |||
| Format of CSV File in export | Excel Discussion (Misc queries) | |||
| Format of CSV File in export | Excel Worksheet Functions | |||
Linear Mode
