Trouble getting excel controls from a worksheet

Hello all,
I have a legacy excel book which has multiple controls on a sheet which I am currently trying to get the data off into an Access form.

Multiple users enter data onto this form (template) which get saved as separate excel spreadsheets in a common folder.

My job is to gather statistics from these sheets. Up until now I've been manually entering the data into an Access database, what I'm trying to now do is automatically load each workbook, get the data from the relevant sheet and then populate the relevant fields in the access form.

So far I've successfully opened the first workbook and read the cell data. Now I'm trying to get the data off the controls.

First I tried using:

Please Login or Register  to view this content.

And I get a data type mismatch error at the for each statement if I DIM sh as shape,
or an error at sh.value if I leave sh as a variant.

I've created a for i=1 to .shapes.count loop, with the second statement set sh= shapes(i) but that fails at the set statement with a type mismatch error also.

Now the person who set up the worksheet just created checkboxes called ch1 to ch21, I was hoping to
get the caption and value for each checkbox one at a time in a loop and use a select case statement to place the data on the access form but have head butting all day so far and would prefer an expert start me along the right track as I can't now see the wood for the trees!

Thanks for your help...

ps: Here's my current attempt at reading the data. To put it in context, this code is running in access, xlWS is a link to the worksheet which contains the controls

Please Login or Register  to view this content.

Firstly Leith,
Sorry about the Excel version, I guess I just entered 2012 when I set my profile up last year, I'm actually using Excel 2010. I apologize for the mistake and I've corrected my profile now.

I might be able to bend some rules and post a blank copy of the excel template if that is needed, I'd prefer not to - I'm fairly sure that ICT dept at work would find a reason to frown on that.
I'm not sure if it is that helpful, as I stated above, this code runs from an Access database, and I definitely can't post that online, that's a offense which could result in me looking for a new job...

Anyway, here is the code fragment that sets up the xlWS variable. As I'm trying to work out what is going on I've gradually explicitly referenced it again through the code so that I can try and rule out that as an issue.

Please Login or Register  to view this content.

I'm fairly sure that because this is the first time I've tried to get data from controls using VBA, there is something that I don't understand about referencing the shapes collection and type casting the resultant shape to a control so that I can get to the properties and methods of the various controls used.

Basically, can I reference WS.shapes(i) and expect to get a shape or do I need to do something with a shaperange to get down to a specific shape?

Otherwise I guess that I could explicitly reference each control via the worksheet somehow, perhaps something like

Please Login or Register  to view this content.

but I guess I am trying to do something that is a bit more elegant as I'd need to manually track each control down on the excel spreadsheet (there are over 21 checkboxes and more than a dozen other controls I need to read and handle.)

Anyway, thanks for trying to assist, I hope I've provided enough info now.

Let me know if you really need a copy of the excel spreadsheet, if you do, I guess I'll just have to dummy up a sheet with a few controls on it.

Thanks for your suggestion,

So I've tried it and I loop through the oleControls and when it gets to the with oleControl section it fails with an "Object doesn't support this property or method" error (438).

I'm going to ask a stupid question: have you got this working from access? If so, I wonder if there is another reference I need to enable in access to make this work.

Thanks for your help so far.

OK Guys I worked out the problem!!

If you look at the following code, i needed to used the .Object property to get to the .Value of the control.

Thanks for your help!!

Trev

Please Login or Register  to view this content.