Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a pivot table and I'm trying to remove a field by it's name. I found help from other posts, but I am unable to make it work correctly. Can someone help please?
Macro:
sub
addfields105
Set
Tabl = ActiveDocument.GetSheetObject("SD02")
Set
Prop = Tabl.GetProperties
Set
Dims = Prop.Dimensions
for
i = 0 to Dims - 1
if
Prop.Dimensions(i).Title.v = "Sub_ProductID"end if
next
end
sub
Are you getting an error when running this macro?
It looks like the problem is with your for statement. In your macro, Dims is an object. You don't want the Dims object in your for statement, you want the count of the memebers of the Dims object.
Use:
for i = 0 to Dims.Count - 1
That worked for me using some sample data. Keep in mind that if you change the Title of the Dimension to something more user friendly, you will need to search for that instead of the field name. For example Prop.Dimensions(i).Title.v = "Product ID".
Here is what I have and still no error:
sub
addfields105Set
Tabl = ActiveDocument.GetSheetObject("SD02")
Set
Prop = Tabl.GetProperties
set
dims = prop.Dimensions
for
i = 0 to Dims.Count - 1
if
Prop.Dimensions(i).Title.v = "Sub_ProductID"then
end
if
next
end
sub
Well, you do know that you have nothing in between the if statements, so if that Title is encountered, nothing happens, correct? I used the following sub and got a pop-up saying Found It! I changed the Object ID and the Dimension Title to reflect my app.
sub addfields105
Set Tabl = ActiveDocument.GetSheetObject("CH25")
Set Prop = Tabl.GetProperties
set dims = prop.Dimensions
for i = 0 to Dims.Count - 1
if Prop.Dimensions(i).Title.v = "Week" then
MsgBox("Found It!")
end if
next
end sub
What I did to test this out on my system was to set a variable to the Title of one of the Dimensions. That way, I could see what QlikView was calling it. For your case, I would figure out what Dimension number Sub_ProductID is and then before your for statement, add:
ActiveDocument.Variable("vTestTitle").SetContent Prop.Dimensions(1).Title.v
Where 1 is the Dimension number you are looking for. Then you can make sure that the string your looking for matches the string that QlikView has.
Got it. Thanks for the help. I found that I wasn't looking for the correct property since I hadn't customized the header names yet. Thanks agin.
Ok. I spoke too soon. I'm still having issues finding the dimension after adding it. Here is my latest code. Is there a good way to paste code into this forum without having to replace all the tabs with spaces? The statement below chokes on the portion in bold. It says "Invalid Procedure call or argument"
sub addfields105
set btn = ActiveDocument.GetSheetObject("BU105")
set propb = btn.GetProperties
set pt = ActiveDocument.GetSheetObject("SD02")
Set propt = pt.GetProperties
set Dims = propt.Dimensions
if (propb.Text.v = "Add Product") then
propb.Text.v = "Remove Product"
btn.SetProperties propb
pt.AddDimension "Sub_ProductID"
propt.Dimensions(Dims.Count).Title.v = "ProductID"
pt.SetProperties propt
else
for i = 0 to Dims.Count - 1
propb.Text.v = "Add Product"
btn.SetProperties propb
if propt.Dimensions(i).Title.v = "ProductID" then
pt.RemoveDimension i
end if
next
end if
end sub
I haven't found a good way to paste code into the forum. I usually paste it into Notepad before pasting it here to get it as plain text. I believe that may also keep your tabs.
I think the problem with that line is that the Count starts at 0, so there is no Dimension with the number Dims.Count. Dims.Count - 1 would be your highest numbered dim.
The Dims.Count - 1 is working but it is removing the wrong field, because the code is naming the wrong one. I believe the add dimension quit working now.
Code:
Rem add/remove field from Subscriber Detail
sub addfields105
set btn = ActiveDocument.GetSheetObject("BU105")
set propb = btn.GetProperties
set pt = ActiveDocument.GetSheetObject("SD02")
Set propt = pt.GetProperties
set Dims = propt.Dimensions
if (propb.Text.v = "Add Product") then
propb.Text.v = "Remove Product"
btn.SetProperties propb
pt.AddDimension "Sub_ProductID"
set Dims = propt.Dimensions
propt.Dimensions(Dims.Count - 1).Title.v = "ProductID"
pt.SetProperties propt
else
for i = 0 to Dims.Count - 1
propb.Text.v = "Add Product"
btn.SetProperties propb
if propt.Dimensions(i).Title.v = "ProductID" then
pt.RemoveDimension i
end if
next
end if
end sub
:End
Weird. If you comment out the three lines after the AddDimension, the Dimension will be added successfully, but not renamed. If you leave those three lines in, the Dimension will not be added, but the highest numbered Dimension will be renamed ProductID.
I found a work around (I have no idea why it works this way and not the other). Do the renaming in a separate Sub.
sub addfields105
set btn = ActiveDocument.GetSheetObject("BU24")
set propb = btn.GetProperties
set pt = ActiveDocument.GetSheetObject("CH25")
Set propt = pt.GetProperties
set Dims = propt.Dimensions
if (propb.Text.v = "Add Product") then
propb.Text.v = "Remove Product"
btn.SetProperties propb
pt.AddDimension "Sub_ProductID"
'propt.Dimensions(Dims.Count - 1).Title.v = "ProductID"
'pt.SetProperties propt
Call newSub
else
for i = 0 to Dims.Count - 1
propb.Text.v = "Add Product"
btn.SetProperties propb
if propt.Dimensions(i).Title.v = "ProductID" then
pt.RemoveDimension i
end if
next
end if
end sub
sub newSub
set btn = ActiveDocument.GetSheetObject("BU24")
set propb = btn.GetProperties
set pt = ActiveDocument.GetSheetObject("CH25")
Set propt = pt.GetProperties
if (propb.Text.v <> "Add Product") then
set Dims = propt.Dimensions
propt.Dimensions(Dims.Count - 1).Title.v = "ProductID"
pt.SetProperties propt
end if
END SUB
The only thing odd I could find on the original is that you define Dims twice. Removing one didn't solve the problem. Other than that, the two methods should be equivilent.
Here is what I ended up with:
sub addfields105
set btn = ActiveDocument.GetSheetObject("BU105")
set propb = btn.GetProperties
if (propb.Text.v = "Add Product") then
propb.Text.v = "Remove Product"
set pt = ActiveDocument.GetSheetObject("SD02")
pt.AddDimension "Sub_ProductID"
set propt = pt.GetProperties
btn.SetProperties propb
set Dims = propt.Dimensions
propt.Dimensions(Dims.Count - 1).Title.v = "ProductID"
pt.SetProperties propt
else
set pt = ActiveDocument.GetSheetObject("SD02")
set propt = pt.GetProperties
set Dims = propt.Dimensions
for i = 0 to Dims.Count - 1
propb.Text.v = "Add Product"
btn.SetProperties propb
if propt.Dimensions(i).Title.v = "ProductID" then
pt.RemoveDimension i
end if
next
end if
end sub
The only issue I have left is that when the field is added it wants to be horizonal instead of a vertical column. I'll have to look for the property to see if I can set it.
Thanks for all your help. If you happen to know a quick fix for my last problem. Please feel free. Thanks again.