VBA Compile CSV Text File for PO Header

[michaelbarker]

Hi Team,

Looking for expertise on "how have you done this before" with building the text CSV files for importing PO headers?

Help is much appreciated.

[michaelbarker]

This is where we are at so far with our process - hoping to interject a simple for loop using an if statement to print a specified number of "columns" per each record type (300, 221, 210, and 200):

1. Populate the PO Header import Excel file with approved PO data ensuring each column has all data correct and no comma values within the text/data
2. Copy all record type 300 data into new tab cell A1 and add a column in BA to tag the lines to the PO number
3. Add a second new column in BB called ComboBOX - this is a formula field =BA1&A1 to combine the PO number with the record type - this is key to sort the data later
4. Copy all record type 200 data into the new tab in the first blank row beneath the 300's 
5. Copy the PO number value to the BA column
6. Count the number of 200 rows 
7. IF you use the same record type 220 and 210 for all PO's like we did, this works for you
8. Copy and paste all PO number values from record type 200 into new rows in BA (this is to assign each record type 220 to it)
9. Copy and paste single line item for record type 220 the total number of times counted in step 6
10. Repeat step 7-9 for record type 210
11. Copy formula in ComboBOX in column BB down the entire data set
12. Sort the entire data in descending order on column BB only (this sets the import structure up perfectly)

Now is where the for loop would come in play assigned to a button to print to CSV where:

'cRECORDTYPE = Fixed columns range for import into SAP Concur
For c300 = 1 To 52
For c220 = 1 To 20
For c210 = 1 To 20
For c200 = 1 To 61

for each row if column A = c300 =  text = IIf(c300 = 1, "", text & ",") & RawRange.Cells(i, c300)

I'll keep posting to this thread to bring awareness and let you all know I'm not just lopping poop over the fence hoping someone will do my job haha

Cheers,

[michaelbarker · Accepted solution]

scrapping the vba route. Using text join instead! Once the data is sorted using the previous step I used this formula to create the CSV file within a new column:

=IF(A7=300,TEXTJOIN(",",FALSE,A7:AZ7),IF(A7=220,TEXTJOIN(",",FALSE,A7:T7),IF(A7=210,TEXTJOIN(",",FALSE,A7:T7),IF(A7=200,TEXTJOIN(",",FALSE,A7:BI7)))))