Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Playing around with Syntax
Hi,
Some problem with syntax below. sub try() dim sh as worksheet Dim NameOfOSWorkbook As Workbook Dim NameOfOEWorkbook As Workbook NameOfOSWorkbook.Name = "Open end data (OS).xls" NameOfOEWorkbook.Name = "Open end data (OE).xls" For each sh in NameOfOSWorkbook ... ... ... Call readingarrayofuniquewords(sh) ... ... ... Next sh End sub Im getting -- Compile error : cant assign to read-only property-- at the line -- NameOfOSWorkbook.Name = "Open end data (OS).xls" Why am I getting the same. If possible please point out the flaw in my understanding (When we could write a statement like -- activeworkbook.name -- then why not -- NameOfOSWorkbook.Name -- Basically I wanted to store a workbook's name in one place so that if it changes I could change it at one place. I have gotten around the above problem in another circuitous manner, given below. Dim sh As Worksheet Dim NameOfOSWorkbook As String Dim NameOfOEWorkbook As String NameOfOSWorkbook = "Open end data (OS).xls" NameOfOEWorkbook = "Open end data (OE).xls" For each sh in Workbooks(NameOfOSWorkbook) ... ... ... Call readingarrayofuniquewords(sh) ... ... ... Next sh End sub But now am getting -- compile error ByRef argument type mismatch -- in the line Call readingarrayofuniquewords(sh). Basically I wanted to pass the name of the worksheet sh. When I changed it to -- Call readingarrayofuniquewords(sh.name) -- things were fine. Is there a more efficient way to code the above (I keep doing things by trial and error). Thanks a lot, Hari India |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Playing around with Syntax
notice that error message
"Can not assign property to READ-ONLY" workbooks("whatever").name is READ ONLY you can not assign it, as far as I know the only way to change a workbook name is to use SaveAs method. Anyone else know of something else? "Hari Prasadh" wrote: Hi, Some problem with syntax below. sub try() dim sh as worksheet Dim NameOfOSWorkbook As Workbook Dim NameOfOEWorkbook As Workbook NameOfOSWorkbook.Name = "Open end data (OS).xls" NameOfOEWorkbook.Name = "Open end data (OE).xls" For each sh in NameOfOSWorkbook ... ... ... Call readingarrayofuniquewords(sh) ... ... ... Next sh End sub Im getting -- Compile error : cant assign to read-only property-- at the line -- NameOfOSWorkbook.Name = "Open end data (OS).xls" Why am I getting the same. If possible please point out the flaw in my understanding (When we could write a statement like -- activeworkbook.name -- then why not -- NameOfOSWorkbook.Name -- Basically I wanted to store a workbook's name in one place so that if it changes I could change it at one place. I have gotten around the above problem in another circuitous manner, given below. Dim sh As Worksheet Dim NameOfOSWorkbook As String Dim NameOfOEWorkbook As String NameOfOSWorkbook = "Open end data (OS).xls" NameOfOEWorkbook = "Open end data (OE).xls" For each sh in Workbooks(NameOfOSWorkbook) ... ... ... Call readingarrayofuniquewords(sh) ... ... ... Next sh End sub But now am getting -- compile error ByRef argument type mismatch -- in the line Call readingarrayofuniquewords(sh). Basically I wanted to pass the name of the worksheet sh. When I changed it to -- Call readingarrayofuniquewords(sh.name) -- things were fine. Is there a more efficient way to code the above (I keep doing things by trial and error). Thanks a lot, Hari India |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Playing around with Syntax
Hi Ben,
Thnx for the reply. Actually my intention was not to change the Name of the workbook. Rather I want to store the name of the workbook in a String such as -- NameOfOSWorkbook -- and then through out my code whenever I wanted to refer the workbook I wanted to use the name of the string -- NameOfOSWorkbook -- rather than using the actual name of the workbook. This way if the workbook name changes I would have to change the code at one line, which is to change the right hand side of the code -- NameOfOSWorkbook.Name = "Open end data (OS).xls" -- Ok, now I understand that NameOfOSWorkbook.Name is erroneous because it implies changing name of the workbook. So, does that mean the following is the ONLY way to reference a workbook (NameOfOSWorkbook). Or is there a more efficient method? Dim sh As Worksheet Dim NameOfOSWorkbook As String Dim NameOfOEWorkbook As String NameOfOSWorkbook = "Open end data (OS).xls" NameOfOEWorkbook = "Open end data (OE).xls" For Each sh In Workbooks(NameOfOSWorkbook).Worksheets ... ... ... Call readingarrayofuniquewords(sh.name) ... ... ... Next sh Thanks a lot, Hari India |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Detecting the actual playing or NOT playing of a WAV file | Excel Discussion (Misc queries) | |||
Games not playing in Excel | Excel Discussion (Misc queries) | |||
playing sound??? | Excel Programming | |||
Playing music | Excel Programming | |||
Playing a .wav when a cell's value changes | Excel Programming |