Using the Excel object

[crowegreg]

That's not good. I spent a good amount of time yesterday researching this problem. It seems simple, but I can't figure out a solution. Thanks for your assistance!!

[ItsMe]

... I spent a good amount of time yesterday researching this problem....

I know what you mean. Obviously this problem is above my pay scale.

[June7]

crow, how does my suggested code edit create another instance of Excel object? I actually did not test from Access, I tested VBA creating the textbox and setting the formula from Excel.

ItsMe, for manual test, create the textbox from the tool on the ribbon Insert tab. Don't want to see blinking cursor within the textbox frame. Once the textbox is selected, click into the formula bar. It does seem your TextFrame is something different.

[crowegreg]

Every time I make a change to the code, after running the process, I confirm that no instance of Excel is running.

When I add the line of code of:
shapeExcel.select , then run the process, there is no instance of Excel open.

Then when I add the line of code:
Selection.formula = "='Auto Submit Calculations'!B3", then run the process, there is an instance of Excel open

What did I do wrong?

[ItsMe]

...ItsMe, for manual test, create the textbox from the tool on the ribbon Insert tab. Don't want to see blinking cursor within the textbox frame. Once the textbox is selected, click into the formula bar. It does seem your TextFrame is something different.

This is what I did from word go. What I did not do was use an expression like "=A1"

So
=A1+A2

Does not work when I have Excel open and I use the Ribbon to add a Text Box but
=A1
Does work

I am starting to see that there just is not a method for the Text Box object (from within Access) that adds the formula to the object. Perhaps giving the object a name (or using the name or index from the parent object) is a solution, not sure.

[June7]

The textbox cannot have a complex formula, all it can do is reference a cell. The cell must have formula and do the calculation.

crow's 'formula' is just a reference to cell on another sheet.

[crowegreg]

ItsMe, so create an object for the text box, and see if this makes any difference?

[ItsMe]

Earlier, I gave it another go from Access, using VBA. I was unable to find a property that was within scope to edit the formula. I tried a few things like, Formula, Caption, Characters, and some others. The only other thing I can think of trying now would be to create a Chart object and use the chart object as a parent to a textbox and see if that helps.

I was looking online and saw mention of ActiveX vs plain Jane TextBox. Not sure if there is any benefit of one over the other with regard to your needs.

[crowegreg]

I figured out a workaround. With not being able to set the textbox to refer to another cell within the workbook, I decided to use the variables that were within the form that were the values of the cells in question.
Here's the finished code.

Dim xlApp As Object
Dim wbExcel As Object
Dim sheetExcel As Worksheet
Dim shapeExcel As Shape
Dim wb1 As Workbook

Set xlApp = New Excel.Application
'xlApp.Visible = True

Set wb1 = xlApp.Workbooks.Open(Environ$("USERPROFILE") & "\documents\" & strFileName & ".xlsx")
Set sheetExcel = wb1.Sheets(1)

'Auto Submit WE1 Textbox
Set shapeExcel = sheetExcel.Shapes.AddLabel(msoTextOrientationHoriz ontal, 617, 70, 35, 35)
shapeExcel.Select
shapeExcel.TextFrame2.TextRange.Characters.Text = dblASCountWE1

'Auto Submit WE2 Textbox
Set shapeExcel = sheetExcel.Shapes.AddLabel(msoTextOrientationHoriz ontal, 740, 70, 35, 35)
shapeExcel.Select
shapeExcel.TextFrame2.TextRange.Characters.Text = dblASCountWE2

'Auto Approved WE1 Textbox
Set shapeExcel = sheetExcel.Shapes.AddLabel(msoTextOrientationHoriz ontal, 617, 425, 35, 35)
shapeExcel.Select
shapeExcel.TextFrame2.TextRange.Characters.Text = dblAACountWE1

'Auto Approved WE2 Textbox
Set shapeExcel = sheetExcel.Shapes.AddLabel(msoTextOrientationHoriz ontal, 740, 425, 35, 35)
shapeExcel.Select
shapeExcel.TextFrame2.TextRange.Characters.Text = dblAACountWE2

'Close and Save Workbook
xlApp.ActiveWorkbook.Close (True)

'Close Excel Object
xlApp.Quit

Set shapeExcel = Nothing
Set sheetExcel = Nothing
Set wb1 = Nothing
Set xlApp = Nothing

Thanks for your assistance!!

[ItsMe]

I am glad to hear that your business rules allow for this solution.