read in excel file using vbscript and add up column values

Hi,

I need to write some vbscript to scroll through some lines of an excel sheet (col D) and then add up some figures in col H. I have attached a sample spreadsheet of the input data that i need to use. Basically i need to go through the rows in column "D" and while the data doesnt change (ie. doesnt change from "CompanyA" to "CompanyB") i need to take the values in column H that are associated with Company A and add them up. I then need to write the output to another excel file, before moving onto CompanyB and adding up the figures related to that company, etc

any help would be great,
thanks
testData.xls
DiCanio13Asked:
Who is Participating?
 
sirbountyConnect With a Mentor Commented:
This should accomplish what you want.
It basically creates a CSV for output, but it can still be opened in Excel, just needs text to columns command, using comma as the delimeter.
If you need it in excel output, I can do that to, but have to head out for now...
Dim objXL : Set objXL = CreateObject("Excel.Application")
Dim objWB : Set objWB = objXL.Workbooks.Open("C:\testData.xls")
Dim objDic : Set objDic = CreateObject("Scripting.Dictionary")

x=4 'First line after header "Supplier Name"

Do 
  SupplierNumber = objXL.Cells(x,3)
  SupplierName = objXL.Cells(x,4)
  ObjectNumber = objXL.Cells(x,5)
  Total = 0

  Do
    Total = Total + objXL.Cells(x,8)
    x = x + 1
  Loop While objXL.Cells(x,4) = SupplierName

  objDic.Add SupplierNumber, SupplierName & "," & ObjectNumber & "," & Total
Loop Until objXL.Cells(x,4).Value=""
objWB.Close

Dim objFSO : Set objFSO = CreateObject("Scripting.FileSystemObject")
Dim objOut : Set objOut = objFSO.CreateTextFile("C:\Output.xls")
objOut.WriteLine "Supplier Number, Supplier Name, Object, Amount"

For Each k in objDic.Keys
  objOut.WriteLine k & "," & objDic(k)
Next

objOut.Close

Open in new window

0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello Dicanio,

why recreate with code what Excel does with formulas in a blink?

=SUMIF($E$4:$E$13,C18,$I$4:$I$13)

or

=SUMIF($D$4:$D$13,B18,$I$4:$I$13)

You could use this formula in your code to write a helper table to add up all the results and copy them to another file.

cheers, teylyn
0
 
DiCanio13Author Commented:
Hi Teylyn,

I need to write this as vbscript code because it is part of an automation script. This is just one function of a larger script.

Thanks for the response though
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Still, even with script, since you have the file open, you could use Excel's capabilities to do the summarising for you instead of creating code that does it. Set up a helper table, fill in the Sumif formula, copy the output to your other file and then delete the helper table again.

That might be quicker and using less lines of code than trying to do it all with scripting.
0
 
DiCanio13Author Commented:
Hi Teylyn,

i wont know the output criteria though. I simply want to scroll through each row of an excel sheet (thousands of lines) and when the company name changes, stop adding up the values associated with that company. To do what you are saying i would need to know in advance the name of each company as output so that i could use that name as the criteria in your formula?

I can see how your way might be quicker if i only needed to do this for a few company's but i need to do thousands, which is why i believe vbscript would be better?

Thanks
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hmm, I see where you're coming from.

A quick Pivot Table might be useful in this case. Then your data does not even have to be sorted by company name.
0
 
DiCanio13Author Commented:
Can you explain what you mean?

will i still be able to output to a results file using this method? the 2nd part of my problem is that i need to output the results to a seperate excel file, ie output -
Company name (col D), Object (col E), total spend (col H)
Company name (col D), Object (col E), total spend (col H)
etc etc
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Attached is your file with a pivot table applied to your data. I don't do VB script, and only have a little VBA, but pivot tables can be created and controlled with VBA, so maybe also with VB script.

The table itself can then easily be copied and pasted elsewhere, using PasteSpecial - Values only.

hth
Copy-of-testData.xls
0
 
DiCanio13Author Commented:
Thanks for the effort teylyn, but i kinda really need this in vbscript, as it is part of a larger automated script. I therefore need to run this function as part of the bigger picture and cant stop the script to copy and paste parts, etc.

is there anyone else that does know how to do this in vbscript? essentially it is a just a matter of finding out which figures are associated with which company name and adding them up?

Regards
0
 
DiCanio13Author Commented:
thanks sirbounty and teylyn, it works now
0
 
sirbountyCommented:
Excellent!  Glad I could help - thanx for the grade. :^)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.