Sub Open_Peru201_Sheet()
‘
‘ Open Peru2011 Sheet Macro
‘ Macro recorded 07/06/2008 by home
‘
Dim wbPeruMoney As Workbook
Dim wbPeruMoney_Open As Boolean
Dim wbPeruMoneyString As String
wbPeruMoney_Open = False
wbPeruMoneyString = “C:\Documents and Settings\home\My Documents\My accounts 08\Money for Peru 2011.xls”
For Each wbPeruMoney In Application.Workbooks
If wb.Name = wbPeruMoneyString Then
wbPeruMoney_Open = True
wb.Activate
MsgBox “Workbook is open!”
End If
Next
If wbPeruMoney_Open = False Then
Workbooks.Open wbPeruMoneyString
MsgBox “Workbook is open!”
End If
End Sub
I go to Peru occasionally. I’m developing a spreadsheet to monitor my savings for each vist. So, I’ve compiled this VBA code to allow my program to check whether the worksheet is open or not when I press the activate button. However, I’m having problem with the line below. Can anybody offer suggestions for overcoming this problem please?
If wb.Name = wbPeruMoneyString Then
I think the wb.Name must by a system variable, because I haven’t created it anywhere.
I know the code isn’t perfect yet, but I’m still building it.
The WorkBooks collection in Excel VBA contains no system variables, but designated namespaces bound to the Excel.Application collection.
When iterating through possibly open workbooks, the namespace .Name is used to check the file name (without the path).
So in your case, assign *two* variables…
wbPeruPath = “C:\Documents and Settings\home\My Documents\My accounts 08\”
wbPeruFile = “Money for Peru 2011.xls”
this way, to verify if the file is already open, the code would be as follows:
for each wb in application.workbooks
if wb.Name = wbPeruFile then
msgbox “Workbook is open!”
wbPeruMoney_Open = true
wb.activate
exit for
end if
next
if wbPeruMoney_Open = false then
set wbPeruMoney = workbooks.open ( wbPeruPath & wbPeruFile )
msgbox “NOW it’s open!”
wbPeruMoney.activate
end if