How do you change the sheet name with Excel Automation? 2

[robojeff]

I am creating Excel spreadsheets with tables and graphs
from Access using automation but I would also like to rename the worksheets as I load them with data.

I created a macro in Excel for renaming the worksheet "Sheet1" to "FPY & Rework" but this which gave me the following code:

Sub Macro1()
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "FPY & Rework"
End Sub

Within my access code I have the following:

Dim objXLSheet As Excel.Worksheet
Set objXLSheet = objXLBook.Worksheets("Sheet1")

I changed this to:

Set objXLSheet = objXLBook.Worksheets("FPY & Rework")

but this gives me a "subscript out of range" error...

What is the correct way to change this page name?

Thank you

 

[jebry]

Hi!

I think it would be like this:

Dim objXLSheet As Excel.Worksheet
Set objXLSheet = objXLBook.Worksheets("Sheet1")
objXLSheet.Name = "FPY & Rework"

Note, I haven't actually tried this.


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[traingamer]

Try (not tested)

Dim objXLSheet As Excel.Worksheet
Set objXLSheet = objXLBook.Worksheets("Sheet1")
objXSheet[b][blue].Name[/blue][/b] = "FPY & Rework"

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[traingamer]

Wow, that looked remarkedly the same.

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[jebry]

Yes it does, let's hope it is right!

Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[robojeff]

I used .Name = "FPY & Rework" inside the "with objXSheet" section of my code & it worked great



Thanks

 

[henniec]

robojeff,

In my previous reply I mentioned that it is the best to name the Excel sheet and then call it from within Access. When you call it from Access you need to activate the worksheet as you go along.

I would assume you create the graphs once and let the new data populate the graph on a fixed range. I futher assume it will be a workbook/worksheet thats going to be used regulaly. Monthly/weekly/daily. So create the names before you try anything else. If you are going to try and create the graphs from Access you will have a very difficult task ahead of you. Rather have a know workshet for your data and a second one for your graph, as it is the way I do it. I also mentioned that you should copy the name of the worksheet tab by highlighting it and then paste it into Access as a space difference in the name that you don't notice will give you the errors you are experiencing.

Check on what I gave you previously and follow the method and it will not give you a headache.

If you made conSHT_NAME1 a constant as in Const conSHT_NAME1 = "AlSB" there is no need to specify the name as I did but only conSHT_NAME1.

If you need some formating in the graph let me know and I will post some code.

Hennie


With objXL
.Visible = True

Set objWkb = .Workbooks.Open(conWKB_NAME)

On Error Resume Next

Set objSht(1) = objWkb.Worksheets(conSHT_NAME1) 'AlSB
objWkb.Worksheets("AlSB").Activate

objWkb.Windows("AlSB").Visible = True