Objects are missing in VBA Query

[tdeeg]

Hello Everyone

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.

So:

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.

Best regards

Tobias

[lionel · Accepted solution]

Hello Tobias,

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 😉

Regards,

Lionel

[tdeeg]

Hello Lionel

i attached the excel and the objects that are related (just a snipout from the whole dataset).

Best Regards

Tobias

[lionel]

Hello Tobias,

could you please export one of the missing org-unit and join the export file and the excel file ?

Lionel

[tdeeg]

Hello lionel

same result, the same objects are not found.

Regards

Tobias

[lionel]

Hello Tobias,

what happens if you try :

Set newOrgeinheit = objMegaRoot.getObjectFromId(neueIDOrg)

 Regards,

Lionel