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.
cbruneAsked:
Who is Participating?
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.

Bill PrewCommented:
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
0
cbruneAuthor Commented:
sorry i dont want u to think of it as an excel file, think of the data being in a pivot table object
0
Bill PrewCommented:
I'll need a real world example of the date you are trying to process.

~bp
0
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

cbruneAuthor Commented:
well the data is the same but in a pivottable object.. with the column a spanning multiple rows
0
Bill PrewCommented:
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
0
cbruneAuthor Commented:
ok those are the exact names.. the pivot table is in memory so basically i outputed it into the excel document.
0
Bill PrewCommented:
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
0
cbruneAuthor Commented:
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
0
Bill PrewCommented:
Okay, I think I understand better now.  Unfortunately I have no experience with Qlikview so would not know how to access data via vbscript.  It does sound like there are methods that can be used in vbscript macros run within Qlikview that might allow this, but I am not an expert on those.

You might want to add additional Topic areas to this question that are Qlikview or Cognos related, in hopes of catching someones eye there that might have info.

Sorry I'm not of more help.

~bp
0

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