read in excel file using vbscript and add up column values


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,
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

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




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
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
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.
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

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?

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.
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
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.

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?

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"

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

    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=""

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)


Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DiCanio13Author Commented:
thanks sirbounty and teylyn, it works now
Excellent!  Glad I could help - thanx for the grade. :^)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.