Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
error 9 - simple code
to open a workbook if it is not already open:
dim path as string path = " letter:\...\\workbook.xls" If Workbooks(path).IsOpen = False Then workbooks.open filename:=path Gives subscript out of range. The solutions i found by searching the forum are a bit long-winded - why doesn't the above work? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
error 9 - simple code
It's because the workbooks collection contains only open workbooks.
You can try the following, which checks all the open workbooks against the path you've specified: Sub test() Dim path As String path = "C:\blah.xls" Dim wb As Workbook For Each wb In Workbooks If wb.FullName = path Then Exit Sub Next wb Workbooks.Open Filename:=path End Sub You should be able to "PBcorn" wrote: to open a workbook if it is not already open: dim path as string path = " letter:\...\\workbook.xls" If Workbooks(path).IsOpen = False Then workbooks.open filename:=path Gives subscript out of range. The solutions i found by searching the forum are a bit long-winded - why doesn't the above work? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
error 9 - simple code
This generates a type mismatch error 13. please advise.
"Sam Wilson" wrote: It's because the workbooks collection contains only open workbooks. You can try the following, which checks all the open workbooks against the path you've specified: Sub test() Dim path As String path = "C:\blah.xls" Dim wb As Workbook For Each wb In Workbooks If wb.FullName = path Then Exit Sub Next wb Workbooks.Open Filename:=path End Sub You should be able to "PBcorn" wrote: to open a workbook if it is not already open: dim path as string path = " letter:\...\\workbook.xls" If Workbooks(path).IsOpen = False Then workbooks.open filename:=path Gives subscript out of range. The solutions i found by searching the forum are a bit long-winded - why doesn't the above work? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
error 9 - simple code
....which seems to be due to the "if wb.fullname = path" line.
"PBcorn" wrote: This generates a type mismatch error 13. please advise. "Sam Wilson" wrote: It's because the workbooks collection contains only open workbooks. You can try the following, which checks all the open workbooks against the path you've specified: Sub test() Dim path As String path = "C:\blah.xls" Dim wb As Workbook For Each wb In Workbooks If wb.FullName = path Then Exit Sub Next wb Workbooks.Open Filename:=path End Sub You should be able to "PBcorn" wrote: to open a workbook if it is not already open: dim path as string path = " letter:\...\\workbook.xls" If Workbooks(path).IsOpen = False Then workbooks.open filename:=path Gives subscript out of range. The solutions i found by searching the forum are a bit long-winded - why doesn't the above work? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
error 9 - simple code
What version of excel are you using? It mustn't be treating wb.fullname as a
string... try cstr(wb.fullname) instead? or wb.fullname.tostring "PBcorn" wrote: This generates a type mismatch error 13. please advise. "Sam Wilson" wrote: It's because the workbooks collection contains only open workbooks. You can try the following, which checks all the open workbooks against the path you've specified: Sub test() Dim path As String path = "C:\blah.xls" Dim wb As Workbook For Each wb In Workbooks If wb.FullName = path Then Exit Sub Next wb Workbooks.Open Filename:=path End Sub You should be able to "PBcorn" wrote: to open a workbook if it is not already open: dim path as string path = " letter:\...\\workbook.xls" If Workbooks(path).IsOpen = False Then workbooks.open filename:=path Gives subscript out of range. The solutions i found by searching the forum are a bit long-winded - why doesn't the above work? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
error 9 - simple code
Excel 2002 SP3
cstr generates the same error, wb.fullname.tostring generates "invalid qualifier". Please advise. "Sam Wilson" wrote: What version of excel are you using? It mustn't be treating wb.fullname as a string... try cstr(wb.fullname) instead? or wb.fullname.tostring "PBcorn" wrote: This generates a type mismatch error 13. please advise. "Sam Wilson" wrote: It's because the workbooks collection contains only open workbooks. You can try the following, which checks all the open workbooks against the path you've specified: Sub test() Dim path As String path = "C:\blah.xls" Dim wb As Workbook For Each wb In Workbooks If wb.FullName = path Then Exit Sub Next wb Workbooks.Open Filename:=path End Sub You should be able to "PBcorn" wrote: to open a workbook if it is not already open: dim path as string path = " letter:\...\\workbook.xls" If Workbooks(path).IsOpen = False Then workbooks.open filename:=path Gives subscript out of range. The solutions i found by searching the forum are a bit long-winded - why doesn't the above work? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
error 9 - simple code
Can you post your code here so I can have a look?
"PBcorn" wrote: Excel 2002 SP3 cstr generates the same error, wb.fullname.tostring generates "invalid qualifier". Please advise. "Sam Wilson" wrote: What version of excel are you using? It mustn't be treating wb.fullname as a string... try cstr(wb.fullname) instead? or wb.fullname.tostring "PBcorn" wrote: This generates a type mismatch error 13. please advise. "Sam Wilson" wrote: It's because the workbooks collection contains only open workbooks. You can try the following, which checks all the open workbooks against the path you've specified: Sub test() Dim path As String path = "C:\blah.xls" Dim wb As Workbook For Each wb In Workbooks If wb.FullName = path Then Exit Sub Next wb Workbooks.Open Filename:=path End Sub You should be able to "PBcorn" wrote: to open a workbook if it is not already open: dim path as string path = " letter:\...\\workbook.xls" If Workbooks(path).IsOpen = False Then workbooks.open filename:=path Gives subscript out of range. The solutions i found by searching the forum are a bit long-winded - why doesn't the above work? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
error 9 - simple code
I omitted to mention that i had added an "or" as follows:
If wb.FullName = path2 or path3 Then Exit Sub which seems to cause the problem. using two if statments is not a solution as obviously the exit sub of the first negates the second. I could just write two subs but is there a better way? "PBcorn" wrote: to open a workbook if it is not already open: dim path as string path = " letter:\...\\workbook.xls" If Workbooks(path).IsOpen = False Then workbooks.open filename:=path Gives subscript out of range. The solutions i found by searching the forum are a bit long-winded - why doesn't the above work? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
error 9 - simple code
Dim myPath as string
dim myFileName as string dim wkbk as workbook mypath = "C:\something\" if right(mypath,1) < "\" then mypath = mypath & "\" end if myfilename = "workbook.xls" '<-- don't include the path here! set wkbk = nothing on error resume next set wkbk = workbooks(myfilename) on error goto 0 if wkbk is nothing then on error resume next set wkbk = workbooks.open(filename:=mypath & myfilename) on error goto 0 end if if wkbk is nothing then msgbox "Not open and can't find it!" else msgbox wkbk.fullname end if This assumes that C:\something\ PBcorn wrote: to open a workbook if it is not already open: dim path as string path = " letter:\...\\workbook.xls" If Workbooks(path).IsOpen = False Then workbooks.open filename:=path Gives subscript out of range. The solutions i found by searching the forum are a bit long-winded - why doesn't the above work? -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
error 9 - simple code
Rather than "If wb.FullName = path2 or path3 Then Exit Sub"
use "If wb.FullName = path2 or wb.fullname = path3 Then Exit Sub" "PBcorn" wrote: I omitted to mention that i had added an "or" as follows: If wb.FullName = path2 or path3 Then Exit Sub which seems to cause the problem. using two if statments is not a solution as obviously the exit sub of the first negates the second. I could just write two subs but is there a better way? "PBcorn" wrote: to open a workbook if it is not already open: dim path as string path = " letter:\...\\workbook.xls" If Workbooks(path).IsOpen = False Then workbooks.open filename:=path Gives subscript out of range. The solutions i found by searching the forum are a bit long-winded - why doesn't the above work? |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
error 9 - simple code
I was going to say that "This assumes that C:\something\workbook.xls" exists.
Then I changed the code to try to open it and report back if it fails. Ignore that line in the original post. Dave Peterson wrote: Dim myPath as string dim myFileName as string dim wkbk as workbook mypath = "C:\something\" if right(mypath,1) < "\" then mypath = mypath & "\" end if myfilename = "workbook.xls" '<-- don't include the path here! set wkbk = nothing on error resume next set wkbk = workbooks(myfilename) on error goto 0 if wkbk is nothing then on error resume next set wkbk = workbooks.open(filename:=mypath & myfilename) on error goto 0 end if if wkbk is nothing then msgbox "Not open and can't find it!" else msgbox wkbk.fullname end if This assumes that C:\something\ PBcorn wrote: to open a workbook if it is not already open: dim path as string path = " letter:\...\\workbook.xls" If Workbooks(path).IsOpen = False Then workbooks.open filename:=path Gives subscript out of range. The solutions i found by searching the forum are a bit long-winded - why doesn't the above work? -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Slight Error in my Simple code | Excel Programming | |||
Application-Defined or Object-Defined Error on simple code | Excel Programming | |||
Application-Defined or Object-Defined Error on simple code | Excel Programming | |||
Out of space stack error, simple code? | Excel Programming | |||
Simple code error statement | Excel Programming |