VBA coding problem. Can anybody help?

Question

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.

Answer 1

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