Sign in to follow this  
Followers 0
Chimaera

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

12 posts in this topic

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

Share this post


Link to post
Share on other sites



#3 ·  Posted (edited)

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 by jdelaney

IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window.

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

 

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 by Chimaera

Share this post


Link to post
Share on other sites

Please check the wiki for the Excel page.

Will post details when at home again.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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?


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites

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

What do you mean by "NAME error"?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

$oWorkbook.Worksheets($i).Name

gives you the name of the worksheet (tab).




			
		

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#12 ·  Posted (edited)

Here:

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

output:

001

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

IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0