Excel - is it possible to change the same cell on each tab to the tab number on 600 pages?

[Chimaera]

As per the title really

I have one cell that needs to have the tab number included inside a formula so if 1st tab is Page 001 the formula for the cell would look like 

=Page 001!A2

 

So as the cell moves to the next tab it would be

 

=Page 002!A2

and so on across 600 pages and its the same cell on every tab

Can someone give me a hint which functions to use please

[JLogan3o13]

Something like this, you mean?

#include <Excel.au3>

$oExcel = _Excel_Open()
$oWorkBook = _Excel_BookOpen($oExcel, @DesktopDir & "\My.xls")

For $i = 1 To 100
    _Excel_RangeWrite($oWorkBook, $i, "=Page 00" & $i & "!A2", "A1", False)
Next

[jdelaney]

I'm using the old excel udf:

#include <Excel.au3>
$oExcel = _ExcelBookOpen(@DesktopDir & "\Book1.xlsx")
$aSheets = _ExcelSheetList($oExcel)
For $i = 1 To UBound($aSheets)-1
    _ExcelSheetActivate($oExcel, $i)
    _ExcelWriteCell($oExcel, $aSheets[$i],"A2")
Next
_ExcelBookSave($oExcel)

That enters fixed text...but if you want to it always pickup the current sheet name:

#include <Excel.au3>
$oExcel = _ExcelBookOpen(@DesktopDir & "\Book1.xlsx")
$aSheets = _ExcelSheetList($oExcel)
For $i = 1 To UBound($aSheets)-1
    _ExcelSheetActivate($oExcel, $i)
;~  _ExcelWriteCell($oExcel, $aSheets[$i],"A2")
    _ExcelWriteFormula($oExcel, '=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"),1))', "A2")
Next
_ExcelBookSave($oExcel)

Edited July 10, 2014 by jdelaney

[Chimaera]

 

Something like this, you mean?

#include <Excel.au3>

$oExcel = _Excel_Open()
$oWorkBook = _Excel_BookOpen($oExcel, @DesktopDir & "\My.xls")

For $i = 1 To 100
    _Excel_RangeWrite($oWorkBook, $i, "=Page 00" & $i & "!A2", "A1", False)
Next

Hmm weird im getting an NAME error with that and it gives me this=Page '0010'!A2

Ideally i would like it to use the tab name and add it in but ill play with it

 

 

I'm using the old excel udf:

#include <Excel.au3>
$oExcel = _ExcelBookOpen(@DesktopDir & "\Book1.xlsx")
$aSheets = _ExcelSheetList($oExcel)
For $i = 1 To UBound($aSheets)-1
    _ExcelSheetActivate($oExcel, $i)
    _ExcelWriteCell($oExcel, $aSheets[$i],"A2")
Next
_ExcelBookSave($oExcel)

That enters fixed text...but if you want to it always pickup the current sheet name:

#include <Excel.au3>
$oExcel = _ExcelBookOpen(@DesktopDir & "\Book1.xlsx")
$aSheets = _ExcelSheetList($oExcel)
For $i = 1 To UBound($aSheets)-1
    _ExcelSheetActivate($oExcel, $i)
;~  _ExcelWriteCell($oExcel, $aSheets[$i],"A2")
    _ExcelWriteFormula($oExcel, '=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"),1))', "A2")
Next
_ExcelBookSave($oExcel)

Do these work with the latest excel udf as im on the newest stable?, ill try anyway and see what happens

Nope they dont im trying to work out what the modern equivalent for 

_ExcelSheetActivate($oExcel, $i)

is?

Edited July 10, 2014 by Chimaera

[water]

Please check the wiki for the Excel page.

Will post details when at home again.

[Chimaera]

I dont understand this

When i run it from JLogans example

I get this

 

=Page '003'!A2

where the hell is it getting the ' ' from?

[JLogan3o13]

I was writing without testing, as I'm away from a PC. I'm guessing the $bValue parameter, which I set to False for the formula property, is what is causing it. What happens if you set it to True?

[water]

Hmm weird im getting an NAME error with that and it gives me this=Page '0010'!A2

What do you mean by "NAME error"?

[Chimaera]

I was writing without testing, as I'm away from a PC. I'm guessing the $bValue parameter, which I set to False for the formula property, is what is causing it. What happens if you set it to True?

Just the same

What do you mean by "NAME error"?

in the excel cell it shows #NAME? which is an invalid name error i think its caused by the =Page '003'!A2

The easiest way i think would be to just add the tab name into the equation 

"=" & $tabname & !A2

Just a non working example of course, is there an easy way to find the tab name and insert it?

[water]

$oWorkbook.Worksheets($i).Name

gives you the name of the worksheet (tab).

[Chimaera]

This works

#include <Excel.au3>

$oExcel = _Excel_Open()

$oWorkBook = _Excel_BookOpen($oExcel, @ScriptDir & "\CS_Catalogue.xlsx")



For $i = 1 To 700
    _Excel_RangeWrite($oWorkBook, $i, "='L:\Cat\CS_Catalogue\[CS_Cat.xlsx]" & $oWorkbook.Worksheets($i).Name & "'!$T$2", "A1", True)
Next

Only slight thing wrong is it doesnt seem to work below tab100 it adds a "0" in front like 049 not sure why but ill have to check the excel sheet to make sure its not from that

[jdelaney]

Here:

ConsoleWrite(StringFormat("%03s",1) & @CRLF)

output:

001

For $i = 0 To 150
    ConsoleWrite(StringFormat("%03s",$i) & @CRLF)
Next

Edited July 10, 2014 by jdelaney