Excel object (1 Viewer)

[crowegreg]

I've written code to export data from Access into an Excel spreadsheet. I have everything working except one part. Within 1 of the worksheets, I've created a chart. Using VBA, I've added a text box to the chart. I can't figure out how to place a formula within the text box. Here's the code I have:

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

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

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

Set shapeExcel = sheetExcel.Shapes.AddTextBox(msoTextOrientationHorizontal, 171, 135, 25, 25)

'Selection.Formula = "='Auto Submit Calculations'!B3"
'sheetExcel.Shapes("TextBox 1").Formula = "='Auto Submit Calculations'!B3"
'shapeExcel.Select


'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

Any suggestions??

 

[vbaInet]

It's merely a shape that takes only text, not a control.

 

[crowegreg]

You are able to put a formula within the textbox.

 

[vbaInet]

It's in the Shapes collection, I would imagine it only takes text. A forumula is also text but when put in the right place (i.e. a Textbox control) it will interpret the forumala.

 

[crowegreg]

Here's my working, finished code.

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


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(msoTextOrientationHorizontal, 617, 70, 35, 35)
shapeExcel.Select
shapeExcel.TextFrame2.TextRange.Characters.Text = dblASCountWE1

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

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

'Auto Approved WE2 Textbox
Set shapeExcel = sheetExcel.Shapes.AddLabel(msoTextOrientationHorizontal, 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