Help creating an Excel script.

[guyinpv]

I've got a funky situation here that I think a script could solve, but I'm no good at scripting Excel, so there!
It's Excel 2007 btw.

My online store exports a CSV of orders along with the line items of the order. To accomplish this, it outputs one row as the customer, having name, order #, etc etc. Then it spits out consecutive rows where those fields are left blank, but the item fields are filled in. Looks something like this for example:

fname,lname,ordernum,productname,qty
jack,wilson,12345,,
,,,widget1,1
,,,widget2,1
,,,widget3,4
bill,smith,39289,,
,,,widget1,3
,,,widget2,1

Etc...

It actually looks alright in Excel, as the list of products sits nicely under the customer "row".

My problem is this, I've got thousands of rows, I'm looking for all the customers that have bought widget2 to get their name and Email so we can update them on the product.

I can't simply search for "widget2" and grab the row above, cause the row above might be another line item or whatever. So somehow I have to search for the widget2, then offset left and see if it's blank, if it is blank, then offset UP until it finds text (which would be the Email or name field). Once it finds the text, I need THAT row copied out onto another sheet to build the Email list with.

Does that sound convoluted? I'm not sure how else to do this but Excel scripting may be the only way to go, if someone can help program the logic of the search and row-finding that would be awesome.

Ideas?

[Reverend Jim]

I can write you something to use from within Excel but because you already have the csv files you can just process them from the command line. Copy the following code and save it into a file with the name "ProductReport.vbs". If your csv file is named myfile.csv you can use it as follows from the command line:

ProductReport widget1 myfile.csv

Output is to the console. If this isn't quite what you want let me know and I'll tweak it.

'fso is used for file operations
'arg is for accessing command line arguments

set fso = CreateObject("Scripting.FileSystemObject")
set arg = Wscript.Arguments

'if a file name is not specified show command syntax and exit

if arg.Count <> 2 then
	wscript.Echo "ProductReport product csvfile"
	wscript.Quit
end if

'the product you are looking for is the first argument.
'the csv file name is the second argument

product = arg(0)
csvfile = arg(1)

if not fso.FileExists(csvfile) then
	wscript.Echo "could not find file",csvfile
	wscript.Quit
end if

'open the csv file and process it one line at a time
'I am assuming that thformat of the file is strictly as you specified
'where all lines but the customer line start with a comma

set tso = fso.OpenTextFile(csvfile)

'add a comma to the start and end of the product so we don't accidentally
'confuse two products. for example, without the commas, "widget1" would
'match both "widget1" and "widget12"

match = "," & product & ","

'I know that the first line contains the column headers and will be
'taken as a customer but because the next line contains a new
'(valid) customer this won't matter.

wscript.Echo "scanning",csvfile,"for",product
wscript.Echo

do until tso.AtEndOfStream

	line = tso.ReadLine()
	
	if left(line,1) = "," then
		if instr(1,line,match,vbTextCompare) > 0 then
			wscript.Echo product,"->",cust
		end if
	else
		parsed = Split(line,",")
		cust = parsed(0) & " " & parsed(1)
	end if
	
loop

tso.Close

[Reverend Jim]

I was bored so I did the same thing but from within Excel. The disadvantage is that the code must be added to every workbook that you want to run it against. The vbs version is run from outside the Excel environment and is not part of the workbook. Please note that this code was written in Excel 2003. Your environment may differ. To add the embedded code, do the following:

1) load the data file into Excel
2) select menu items Tools -> Macro -> Macros
3) enter ProductReport as the macro name
4) click Create
5) replace the displayed code (all of it) with my code
6) save the workbook

To run, press Alt-F8 then select the ProductReport macro

Sub ProductReport()

    '  This macro scans the first sheet in the current workbook for all customers who
    '  have placed an order for a product which is entered at the prompt when the macro
    '  is run. The resulting report is copied into spare cells on that same worksheet.
    '  The location of the report is determined by the values of oRow and oCol. From
    '  the sample data it appears that the end of the customer data can be detected
    '  when both columns 1 and 4 are both blank.
    '
    '  2011-04-15 - JdG - original code

    Product = InputBox("Product:", "Enter product to search for")   'product name
    Match = LCase(Product)                                          'name in lower case
    
    iRow = 2    'the starting input row
    oRow = 2    'the starting output row (for report data)
    oCol = 8    'the starting output column
    
    With Worksheets(1)  '1 = first worksheet
    
        'clear out any existing report data
        
        r = oRow
        
        Do Until .Cells(r, oCol) = ""
            .Cells(r, oCol + 0) = ""
            .Cells(r, oCol + 1) = ""
            .Cells(r, oCol + 2) = ""
            r = r + 1
        Loop
    
        'keep going until we have a row with nothing in both the first name and product columns
    
        Do Until .Cells(iRow, 1) = "" And .Cells(iRow, 4) = ""
            
            'if first column is not blank then this is a customer row - save the row number
            'otherwise check the product column and display the info if a product match
            'note that all comparisons are done in lower case
            
            If .Cells(iRow, 1) <> "" Then
                cRow = iRow
            Else
                If LCase(.Cells(iRow, 4)) = Match Then
                    .Cells(oRow, oCol + 0) = .Cells(cRow, 1)    'copy the customer first name
                    .Cells(oRow, oCol + 1) = .Cells(cRow, 2)    'copy the customer last name
                    .Cells(oRow, oCol + 2) = .Cells(iRow, 4)    'copy the product name
                    .Cells(oRow, oCol + 3) = .Cells(iRow, 5)    'copy the prodoct amount
                    oRow = oRow + 1                             'select next output row
                End If
            End If
            
            iRow = iRow + 1

        Loop

    End With

End Sub