Macro to Create Individual Records from List with Qtys
I receive data from a customer such as in file "Applied Data.xls"
Column A is Quantity. Column B is data.
I need to run this data through software that only accepts individual records. For example Qty 15 of Data 9929 in first line of file must show "9929" listed 15 times to print correctly.
2nd attachment shows the resulting .csv file that I need with example data & quantities. Cells A1, "Applied1" and A2 "txtField1" must remain UNCHANGED and always be present.
Can a macro be written to take data from "Applied Data.xls" and create a new file looking like "Applied1.csv"
It must work with any number of records. For example, there may be dozens of data records with multiple Qtys required of each.
If not a macro, then what? Open to suggestions
Re: Macro to Create Individual Records from List with Qtys
Administrative note
Please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to ( if you are not using XL2003 any more). Thanks
An Excel 2003 beginner VBA starter demonstration (in fact works whatever the version !) :
PHP Code:
Sub Demo1() Dim V, F%, R& V = ActiveSheet.UsedRange.Columns("A:B").Value2 F = FreeFile Open ActiveWorkbook.Path & Application.PathSeparator & "Applied1 .csv" For Output As #F Print #F, "Applied1"; vbCrLf; "txtField1"; For R = 1 To UBound(V) Print #F, Application.Rept(vbCrLf & V(R, 2), V(R, 1)); Next Close #F End Sub
► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » !
Hi Marc,
Thanks for your help! This works perfectly!
Is it possible for slight modification to the code to allow multiples files to be saved? For example, i will run the macro and it creates the Applied1.csv file. But if I change the quantities and data fields and re-run the macro, it replaces the first file with the new data. Is it possible to not overwrite the first file and save multiple files?
Hi Marc,
A few years ago, (Jan 2020), you helped me with a Macro in this thread. I've been using it for 3 years with much success, it works very well!
I now have a need to repeat 2 columns of data. Before, it was Col A - Qty. Col B - Data. Data to repeat on number of lines as qty shown. If I try the old Macro here, it doesn't work.
Attached files showing sample data & what I want as result. Thanks!
Originally Posted by Marc L
An Excel 2003 beginner VBA starter demonstration (in fact works whatever the version !) :
PHP Code:
Sub Demo1() Dim V, F%, R& V = ActiveSheet.UsedRange.Columns("A:B").Value2 F = FreeFile Open ActiveWorkbook.Path & Application.PathSeparator & "Applied1 .csv" For Output As #F Print #F, "Applied1"; vbCrLf; "txtField1"; For R = 1 To UBound(V) Print #F, Application.Rept(vbCrLf & V(R, 2), V(R, 1)); Next Close #F End Sub
► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » !
Thanks to well read this time the advise just below the code
According to your last attachment another starter VBA demonstration :
PHP Code:
Sub Demo2() Dim V, F%, R& V = Sheet1.UsedRange F = FreeFile Open ActiveWorkbook.Path & Application.PathSeparator & Sheet1.Name & " .csv" For Output As #F Print #F, Sheet1.Name; ","; vbCrLf; V(1, 2); ","; V(1, 3); For R = 2 To UBound(V) Print #F, Application.Rept(vbCrLf & V(R, 2) & "," & V(R, 3), V(R, 1)); Next Close #F End Sub
► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄
Bookmarks