Help reading excel control data in Access form

[aussieTrev]

Hi all,
I have a legacy Excel template which multiple users complete. On completing the form a copy is saved into a network folder.
I then have a form created in Access which currently I type in all the data on the form, audit the report and it is then entered into the corresponding database. There are several situations when one form entry creates multiple entries in the database, so the form is completely unbound from the table, and I use code to add the required number of rows to the table dependent on the data.

This worked well when there was about 40 reports a month. Now with nearly three times that, the manual entry consumes a large part of my day, especially when I return from a month of leave .

So I've spent a couple of days and have created more code to select one of the reports, open it, and read in the data which is placed in cells on the form.

There are more than 30 controls on the form, so I am currently in the process of trying to read data from those controls and this is where I have struck a problem. Whenever I try to access the value or caption of a control (at the moment I'm concentrating on the check boxes) I get a error.

I have tried accessing explicitly (like in an excel module,) via the shapes collection and I'm currently trying to use the OLEObjects collection.
Here is a copy of the code fragment that tries to accesses the data. Note that this is not the final version, but I'm just trying to get the value at this stage, and by using the array I'm think I've isolated the problem to the oleControl.value part of the statement.

    Dim sFoundXLS As String
    Dim xlWS As Worksheet
   Dim oleControl As OLEObject
   Dim n As Integer
   Dim varControls() As Variant
         n = 1

         For Each oleControl In xlWS.OLEObjects
            If oleControl.ProgId = "Forms.CommandButton.1" Or _
               oleControl.ProgId = "Forms.ToggleButton.1" Or _
               oleControl.ProgId = "Forms.CheckBox.1" Or _
               oleControl.ProgId = "Forms.OptionButton.1" Or _
               oleControl.ProgId = "Forms.SpinButton.1" Then 'add more types if needed
               With oleControl
                  ReDim Preserve varControls(1 To 3, 1 To n)
                     varControls(1, n) = .Parent.Name
                     varControls(2, n) = .Name
                     varControls(3, n) = .Value
               End With
            Else
               n = n - 1 'reset index if control is not the right type, preventing empty entries
            End If
            n = n + 1
         Next

Note that I can successfully access the worksheet through xlWS in other sections of the code.

When I run the code, it fails at the varControls(3,n) = .value line with a run time error 438: Object doesn't support this property or method.

I've spent the best part of a day trying to access this method and I'm currently at this point: :banghead:.

Can anyone give me some assistance? I'd like to attach both the access database and template. Unfortunately if work found out that I'd posted the database I'd be looking for a new job shortly after, I could probably get a similar excel template online but I suspect IT wouldn't be that happy with me posting the template either.

I've expressly enabled the References in access VBA to Microsoft Excel 14.0 object library and added & enabled the Microsoft Forms 2.0 Object Library.

I'm guessing that I need to enable another reference or work out how to turn a variant variable into the respective Controls type to get to the right properties/methods but I haven't worked out how to do either.

Thanks for your help.

 

[aussieTrev]

Fixed it myself.

I needed to use the oleObject.Object property to access the checkbox value.

Like this:
varControls(3, n) = .object.Value