Chimaera Posted July 10, 2014 Share Posted July 10, 2014 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 If Ive just helped you ... miracles do happen. Chimaera CopyRobo() * Hidden Admin Account Enabler * Software Location From Registry * Find Display Resolution * _ChangeServices() Link to comment Share on other sites More sharing options...
Moderators JLogan3o13 Posted July 10, 2014 Moderators Share Posted July 10, 2014 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 "Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball How to get your question answered on this forum! Link to comment Share on other sites More sharing options...
jdelaney Posted July 10, 2014 Share Posted July 10, 2014 (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 July 10, 2014 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. Link to comment Share on other sites More sharing options...
Chimaera Posted July 10, 2014 Author Share Posted July 10, 2014 (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 July 10, 2014 by Chimaera If Ive just helped you ... miracles do happen. Chimaera CopyRobo() * Hidden Admin Account Enabler * Software Location From Registry * Find Display Resolution * _ChangeServices() Link to comment Share on other sites More sharing options...
water Posted July 10, 2014 Share Posted July 10, 2014 Please check the wiki for the Excel page. Will post details when at home again. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Chimaera Posted July 10, 2014 Author Share Posted July 10, 2014 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? If Ive just helped you ... miracles do happen. Chimaera CopyRobo() * Hidden Admin Account Enabler * Software Location From Registry * Find Display Resolution * _ChangeServices() Link to comment Share on other sites More sharing options...
Moderators JLogan3o13 Posted July 10, 2014 Moderators Share Posted July 10, 2014 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? "Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball How to get your question answered on this forum! Link to comment Share on other sites More sharing options...
water Posted July 10, 2014 Share Posted July 10, 2014 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Chimaera Posted July 10, 2014 Author Share Posted July 10, 2014 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? If Ive just helped you ... miracles do happen. Chimaera CopyRobo() * Hidden Admin Account Enabler * Software Location From Registry * Find Display Resolution * _ChangeServices() Link to comment Share on other sites More sharing options...
water Posted July 10, 2014 Share Posted July 10, 2014 $oWorkbook.Worksheets($i).Name gives you the name of the worksheet (tab). My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Chimaera Posted July 10, 2014 Author Share Posted July 10, 2014 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 If Ive just helped you ... miracles do happen. Chimaera CopyRobo() * Hidden Admin Account Enabler * Software Location From Registry * Find Display Resolution * _ChangeServices() Link to comment Share on other sites More sharing options...
jdelaney Posted July 10, 2014 Share Posted July 10, 2014 (edited) 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 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. Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now