mardi 18 décembre 2018

Retrieving information of OLEObjects from Workbook with VBA

Scenario: I am trying to read all the information or a worksheet with VBA (initially Python, but I could find no way to do this). Since the sheets I have to read have different formats and are usually a mess, I am looping through all objects in the sheet, getting their name and value (checked or not, as a binary).

Issue: The boxes are usually out of order, so I have no way to know what comes in which order. So I am trying to retrieve some basic form of location or reference to the cells around it.

What I tried: Following the documentation (https://docs.microsoft.com/en-us/office/vba/api/excel.oleobjects) I tried all types of different properties, but none can help with this issue directly. The closest I got was with BottomRightCell, but this only yields the value of the cell, whereas I would need the location or cell number, so I can reference the checkbox properly.

Question: Is there a way to do this kind of identification? Would there be a better way to read all the contents in a sheet (including if a checkbox is checked or not) directly, or those two operations must be done separately?

Code do far:

Sub Test_retrieve()

' this will get all non object values from the sheet

Dim array_test As Variant
Dim i As Long, j As Long

array_test = ThisWorkbook.Sheets(1).UsedRange

For i = 1 To ThisWorkbook.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
    For j = 1 To ThisWorkbook.Sheets(1).Cells(1, Columns.Count).End(xlToLeft).Column
        ThisWorkbook.Sheets(2).Cells(i, j) = array_test(i, j)
    Next j
Next i

End Sub


Sub getavticeboxvalue()

    ' this will get the names and values (as binary) of all the activex controlbox objects in the sheet

    Dim objx As Object
    Dim i As Long

    i = 1

    For Each objx In ThisWorkbook.Sheets(1).OLEObjects

        If objx.Object.Value = True Then
            ThisWorkbook.Sheets(3).Cells(i, 1).Value = 1
            ThisWorkbook.Sheets(3).Cells(i, 2).Value = objx.Name
            ThisWorkbook.Sheets(3).Cells(i, 3).Value = objx.Placement 'here is the issue

        ElseIf objx.Object.Value = False Then
            ThisWorkbook.Sheets(3).Cells(i, 1).Value = 0
            ThisWorkbook.Sheets(3).Cells(i, 2).Value = objx.Name
            ThisWorkbook.Sheets(3).Cells(i, 3).Value = objx.Placement 'here is the issue
        End If
        i = i + 1

    Next objx

End Sub




Aucun commentaire:

Enregistrer un commentaire