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