If WB not there; give message: What am I doing wrong?
First, it looks like the code you commented was really working with
worksheets--not workbooks.
Assuming that you really meant workbooks:
Dim wkbk as workbook
dim wkbkName as string
dim myPath as string
wkbkname = "Dec.xls" 'include the extension
'include the trailing backslash on the path
myPath = "C:\Documents and Settings\RICK\My Documents\Calendar\Y2010\"
set wkbk = nothing
on error resume next
'check to see if it's already open
set wkbk = workbooks(wbname)
on error goto 0
if wkbk is nothing then
'not open
on error resume next
'no need to change path since it's included in the .open statement
set wkbk = workbooks.open(filename:=mypath & wkbkname)
on error goto 0
if wkbk is nothing then
msgbox "File could not be opened"
else
msgbox "It's open now"
end if
else
msgbox "It's already open!"
end if
if wkbk is nothing then
exit sub???
else
application.run "'" & wkbk.name & "'!backtotop"
end if
==========
This doesn't actually check to see if the file is there. It just checks to see
if can be opened.
If you only wanted to check for the existence of the file, you could use:
Dim TestStr as string
dim wkbkName as string
dim myPath as string
wkbkname = "Dec.xls" 'include the extension
'include the trailing backslash on the path
myPath = "C:\Documents and Settings\RICK\My Documents\Calendar\Y2010\"
teststr = ""
on error resume next
teststr = dir(mypath & wkbkname)
on error goto 0
if teststr = "" then
msgbox "it's not there"
else
msgbox "yes, it is!"
end if
=======
All untested, uncompiled. Watch for typos!
RGreen wrote:
Sub DEC_NY_AutoShape22_Click()
'
' AutoShape21_Click Macro
' Macro recorded 8/31/2009 by Rick Green
'
Application.ScreenUpdating = False
'
'Dim wb As Workbook
'On Error Resume Next
'Set wb = ActiveWorkbook.Worksheets("DEC")
'On Error GoTo 0
'If wb Is Nothing Then
' MsgBox "Year not active for this month."
'Else
ChDir _
"C:\Documents and Settings\RICK\My Documents\Calendar\Y2010"
Workbooks.Open Filename:= _
"C:\Documents and Settings\RICK\My Documents\Calendar\Y2010\DEC.xls"
Application.Run "DEC.xls!backtotop"
'End If
End Sub
I have a workbook named Months:
12 months and arrows which either refers to year 2009, 2010, or 2011. I
have not work on 2011 and only started to work on few of the 2010. If these
workbooks are not in the location specified by the macro, I want it to state
that workbook for that year has not been created and go back to the workbook
call "Months".
I have searched various references and thought this maybe the correct code,
apparently I am wrong.... Can someone please help me.
Thanks,
Rick
--
Dave Peterson
|