Pick values from embedded unbound excel sheet onto the form

[mkashif83]

I am trying to pick values from an unbound OLE object. Can you help me with the following:
1) I have a form which has a field AccountBalance.
2) I have embedded an unbound OLE excel object which consists of some simple additions and subtraction calculated cells. This sheet is embedded in the form and is not bound to an actual excel file. I simply want to add the sheet to perform temporary calculations, then pick specific info and move to the next record in the form.
3) I want to add a command button cmdPopulate. Once clicked, I would like this command button to pick value from one of the cell in the excel sheet (say C7) and populate the value in the AccountBalance field on the form.
Thank you in advance for your help.

 

[boblarson]

Have you tried this (I just did a test in Access 2007 with an embedded Office 12.0 Spreadsheet ActiveX control and it worked):

Me.AccountBalance = Me.YourSpreadsheetControlName.Range("C7").Value

 

[boblarson]

Also, be aware that the Intellisense will NOT recognize the code and so I'm sure there's much more Excel code available to you, but Intellisense cannot display it.

 

[mkashif83]

Hi Bob,

Thanks for the reply. I tried the code you mentioned but it didn't work. This is the error msg I get: "438 Object doesn't support this property or method".

Any other tips?

Thanks,

 

[boblarson]

Hi Bob,

Thanks for the reply. I tried the code you mentioned but it didn't work. This is the error msg I get: "438 Object doesn't support this property or method".

Any other tips?

Thanks,

Can you post the database here so we can work on figuring it out? Remember to remove any sensitive data, compact and repair and then zip the file.