at the moment i try to map relationships from old OrgUnits to new OrgUnits via a VBA Macro. This is my code so far (just the relevant lines):
Sub mapOldtoNew() j = 2 Worksheets("mapOldNewOrgs").Activate 'find out amount of lines endetabelle = Cells(Rows.Count, 2).End(xlUp).Row Set myOrgeinheiten = Nothing Set myOrgeinheiten = objMegaRoot.OrgEinheit For i = 2 To endetabelle oldNameOrg = Cells(i, 1).Value newNameOrg = Cells(i, 3).Value neueIDOrg = Cells(i, 4).Value Set newOrgeinheit = myOrgeinheiten(neueIDOrg) Set oldOrgEinheit = myOrgeinheiten.Item(oldNameOrg) If oldOrgEinheit.GetID > 0 Then If newOrgeinheit.GetID > 0 Then 'map old to new, snipped out, not relevant... '..... '..... Else Debug.Print neuerNameOrg & " not found." End If Else Debug.Print alterNameOrg & " not found." End If Next End Sub
Now the fact is that this script doesn't find some of the new OrgUnits that should be there. Some are found, some are not.
In the Excel file i have the exact local name and the absolute ID of the new OrgUnit. I tried it with the example above but nothing works.
I make an example: In my Excel i have an Orgunit called "AXA CH (W)" with ID "mBdhvcE(H9sW". So "neueIDOrg" would be "mBdhvcE(H9sW" and with
"Set newOrgeinheit = myOrgeinheiten(neueIDOrg)"
i expect newOrgeinheit to be the OrgUnit "AXA CH (W)". But it's not found.
I tried it with the "newNameOrg" which would be "AXA CH (W)" and changing the code to
"Set newOrgeinheit = myOrgeinheiten.Item(newNameOrg)". Still not found.
I also tried it with an explicit Select Statement:
selectstate = "Select [OrgEinheit] Where [Absolute ID] ='" & neueIDOrg & "'" Set tmpOrgeinheit = objMegaRoot.GetSelection(selectstate) Set newOrgeinheit = tmpOrgeinheit(1)
If i copy paste the Select Statement from the VBA Debug window in the Query tool and execute it, the OrgUnit is found.
Select [OrgEinheit] Where [Absolute ID] ='mBdhvcE(H9sW'
pasted in the Mega Query reveals this:
I have no idea why this isn't working, because others are found. It's also not a rights or visibility issue, since all OrgUnits are on the lowest rights- and visibility levels and i work as an admin right now for testing purposes.
So i am asking for help on this here, because i can't figure out what is wrong.
Solved! Go to Solution.
i attached the excel and the objects that are related (just a snipout from the whole dataset).
sorry to come back to you so late... but I just came back from vacation...
I should have seen your problem before.... everything comes from those lines :
If oldOrgEinheit.GetID > 0 Then If newOrgeinheit.GetID > 0 Then
Indeed, id's can be negative number... which is the case for the org-units that your code "can't" find...
Just replace those lines by :
If oldOrgEinheit.GetID <> 0 Then If newOrgeinheit.GetID <> 0 Then
And it will work correctly ;-)