Using the Excel object

[crowegreg]

Within Access, I'm using the Excel object. I've created a chart, and I'm trying to change the colors within the chart. When I create a macro within Excel, this is what it shows:
'ActiveSheet.ChartObjects("Chart 2").Activate
'ActiveChart.SeriesCollection(1).Select


'With wb1.Sheets(1).Shapes(1).Chart.SeriesCollection(1)
'.Visible = msoTrue
'.Format.Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent2
'.ForeColor.TintAndShade = 0
'.ForeColor.Brightness = 0
'.Transparency = 0
'.Solid
'End With

Using the following code to identify my worksheet:
wb1.sheets(1)

I can't figure out how to add the code from the macro. I keep getting an error "Object doesn't support this property or method" when I add the SeriesCollection to anything I've tried.

Thanks in advance

[ItsMe]

It is not specific to the properties you are trying to adjust but here is an example using ChartObject. It may help you to access its Members/Properties.
https://www.accessforums.net/program...tml#post212827

[crowegreg]

Thanks. I'll give you link you provided a try.

[crowegreg]

Using what you referred, I came up with this solution:
wb1.Sheets(1).ChartObjects(1).Chart.SeriesCollecti on(1).Interior.Color = RGB(192, 80, 77)


My next problem, I've figured out how to add a text box. I can't figure out how to fill the text box with a formula. Here's what I have so far:
wb1.Sheets(1).Shapes.AddTextbox(msoTextOrientation Horizontal, 171, 135, 72, 72) _
.Select

The formula I need to have inserted into this text box is: 'Auto Submit Calculations'!B3

[ItsMe]

I am not an expert in Excel or the Automation of Excel but, why are you applying a formula to an object like a TextFrame? I would think a cell would be best suited for a formula.

When I look at your approach to writing code, I notice that you are not trying to instantiate new objects. This is going to limit your ability to rely on intellisense and debug. Adding lines of code will help to isolate issues when trying to incorporate new methods and adjusting properties.

I was able to get the following code to work and add text to a TextFrame but do not see the advantage of this.

.

Dim xlApp As Excel.Application
Dim myBook As Workbook
Dim mySheet As Worksheet
Dim myShape As Shape

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = False

Set myBook = xlApp.Workbooks.Open("C:\Test\" & "TestAutomation.xlsx")
Set mySheet = myBook.Sheets.Item(1) 'Grab the first worksheet
Set myShape = mySheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 171, 135, 72, 72)

myShape.TextFrame.Characters.Text = "'Auto Submit Calculations'!B3"

myBook.Save
myBook.Close
xlApp.Quit

Set myShape = Nothing
Set myBook = Nothing
Set mySheet = Nothing
Set myChart = Nothing
Set xlApp = Nothing

[crowegreg]

The text box is within a chart, not within an individual cell.

I'll see if I can use your code. Thanks!!

[crowegreg]

I've used some of your suggestions, and here's what I have now:

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(msoTextOrientationHor izontal, 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

But I can't figure out how to get the formual in. What you suggested merely pastes the text 'Auto Submit Calculations'!B3 into the text box.

Any suggestions?

[ItsMe]

How would you normally add a formula to a TextBox? I do not see a way to do this.

[crowegreg]

When working within Excel, if you add a text box. Then go to the formula bar, and enter a formula within that text box.

As an example, once the code has run to create the above worksheet, with the text box created. If I click within the textbox, then put the cursor in the formula box, then enter =''Auto Submit Calculations'!B3, the text box will display the contents of cell B3 within tab Auto Submit Calculations.

[ItsMe]

When I insert a Text Box using the Text Box tool and try that I get this... EDIT: I realize I goofed the syntax but I get the same result regardless.


.

[crowegreg]

Huh. Within sheet2, put a value in cell A1. Then within the text box, insert the formula =Sheet2!A1

[June7]

I also don't understand what you want to do. What do you mean textbox is within chart?

The only textboxes I am aware of in Excel are ActiveX textbox control from ribbon Developer tab and textbox from Insert tab. The latter is the one ItsMe shows in image.

However, I was able to manually accomplish what you describe with the Insert tab textbox. What purpose does textbox serve?

Maybe code needs to select the shape first then set the cell reference. This worked for me:

shapeexcel.Select
Selection.Formula = "=A1"

Why are you building chart in Excel using code in Access? Maybe this will be of interest https://www.accessforums.net/program...vba-29178.html

[ItsMe]

As for the specifics to getting the formula to the Text Box, sounds like June has the solution by going after the .Formula property.

[crowegreg]

June7's suggestion works, but it creates another instance of the Excel object.

[ItsMe]

I think the textbox I am creating with the code I offered is a different type of object because I am unable to add a formula to it, using VBA or otherwise.