Avatar of cbrune
cbrune asked on

VB script to take a pivot table and be able to parse it like the one attached.

example.xlsx

I need to know how to parse and get the value of the pivot table so I can grab the TTO than cycle through the subtasks.  I just want to get the tto without getting the total and if there was another TTO there how would i get that as well even though its crossing several rows.
VB Script

Avatar of undefined
Last Comment
Bill Prew

8/22/2022 - Mon
Bill Prew

Are you looking for something like this?

' Define Needed constants
Const xlUp = -4162

' Define Excel file to process
Const cExcelFile = "C:\EE\EE28226559\example.xlsx"

' Open Excel, hide it
Set oExcel = CreateObject("Excel.Application")
oExcel.Visible = False

' Access data from first sheets in first workbook
Set oMaster = oExcel.Workbooks.Open(cExcelFile, False, False)
Set oSheet = oMaster.Sheets(1)

' Iterate through all rows (skip header and total)
For iRow = 2 To oSheet.Cells(65536, "A").End(xlUp).Row - 1
   ' Get column A value when it changes
   sValue = Trim(oSheet.Cells(iRow, "A").Value)
   If sValue <> "" Then
      sTTO = sValue
   End If

   ' Get column B value when it changes
   sValue = Trim(oSheet.Cells(iRow, "B").Value)
   If sValue <> "" And sValue <> "Total" Then
      sSubTask = sValue
      Wscript.Echo sTTO & ", " & sSubTask
   End If
Next

oMaster.Close
oExcel.Quit

Open in new window

~bp
ASKER
cbrune

sorry i dont want u to think of it as an excel file, think of the data being in a pivot table object
Bill Prew

I'll need a real world example of the date you are trying to process.

~bp
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
cbrune

well the data is the same but in a pivottable object.. with the column a spanning multiple rows
Bill Prew

Okay, so could you please prepare an actual excel workbook with the pivot table you want to pull data out of.  It's critical that the example you provide is exactly like the real files you will be working with, since extracting data from a pivot table relies on using the exact names of pivot fields, etc.

~bp
ASKER
cbrune

ok those are the exact names.. the pivot table is in memory so basically i outputed it into the excel document.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Bill Prew

What do you mean by "the pivot table is in memory"?  As I understood it you wanted to process an Excel file, but that doesn't sound like the situation.  If it isn't, then please describe further where the data is coming from?

~bp
ASKER
cbrune

yea so actually i am grabbing it from a pivottable in a qlikview application and need to process it and export a report to excel
ASKER CERTIFIED SOLUTION
Bill Prew

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question