CODEWITHSUNDEEP
excelvba
10101
10101

Reputation: 2412

List values from sql search to Excel

I am running a search from Excel to get objects Internal ID's by External ID. In ThisWorkbook.Sheets("Other Data").Range("J30").Value I have External ID f5f9a21b-9208-de11-995f-005056bb3dfa. After search code should display Internal ID.

This one works and I am getting a message: There were 3 objects with the display Id of f5f9a21b-9208-de11-995f-005056bb3dfa

How I can make this code to actually display these ID's for example starting from cell A1?

So instead of just a message:

MsgBox ("There were " & results.Count & " objects with the display Id of" & ThisWorkbook.Sheets("Other Data").Range("J30").Value)

I would get the ID's in Excel? Basically I need results.Count not to count items but input them to Excel.

Code edited according to suggestion, but debugger is pointing to ThisWorkbook.Sheets("Start").Cells(i, 1).Value = results(i)

Private Sub SurroundingSub()
    Set oVault = oMFClientApp.BindToVault(szVaultName, 0, True, True)
    ' Create the condition.
    Dim condition As New SearchCondition
    Dim oScs: Set oScs = CreateObject("MFilesAPI.SearchConditions")
    Dim oVaultConnections As MFilesAPI.VaultConnections
    Dim i As Integer

    ' Set the expression.
    condition.Expression.DataStatusValueType = MFStatusType.MFStatusTypeExtID

    ' Set the condition type.
    condition.ConditionType = MFConditionType.MFConditionTypeEqual

    ' Set the value.
    ' In this case "MyExternalObjectId" is the ID of the object in the remote system.
    condition.TypedValue.SetValue MFDataType.MFDatatypeText, ThisWorkbook.Sheets("Other Data").Range("J30").Value

            'Add the condition to the collection.
            oScs.Add -1, condition

            'Search.
            Dim results 'As ObjectSearchResults
            Set results = oVault.ObjectSearchOperations.SearchForObjectsByConditions(oScs, MFSearchFlags.MFSearchFlagNone, False) ' False = SortResults

            'Output the number of items matching (should be one in each object type, at a maximum).
            'MsgBox ("There were " & results.Count & " objects with the display Id of" & ThisWorkbook.Sheets("Other Data").Range("J30").Value)

            For i = 1 To results.Count
            ThisWorkbook.Sheets("Start").Cells(i, 1).Value = results[i]
            Next i

End Sub

enter image description here

EDIT 2

Also () does not work:

enter image description here

Upvotes: 0

Views: 98

Answers (1)

Roman
Roman

Reputation: 156

If you're looking for a quick and direct way try this:

For i = 1 To results.Count Cells(i, 1).Value = results(i - 1) Next i

Tip: Cells(row, column)

Upvotes: 1

Related Questions