dont open a Workbook is Password protected

[John]

Hi all,

How can i error trap opening a workbook from VB if the Workbook is Password
protected? I'm using:

Workbooks.open WB, Updatelinks:=False, ReasOnly:=True
I'm thinking maybe i have to have an error handler somewhere but dont know
what to write. Any help as always greatly appreciated.

Thanks.
John

 

[Patrick Molloy]

handle the error in a function

' so in your main code ....

dim newWB as workbook

if WBopened(WB) then
'ok and newWB is the workbook object
' and do stuff
'finally
newWB.Close False ' to close without saving
set newWB = Nothing
else
'oops
end if


end sub

then add the function...
' the function is passed the workbook name and the wbobject
' if succesful , the boolrean is true and the object is set to th eworkbook
' if not succesful the boolean is false

function WBopened(WB as string, newbook as Workbook) as boolean
on error resume next
set newbook = Workbooks.open( WB, Updatelinks:=False, ReasOnly:=True)
if err.Number=0 then
err.clear
else
WBopened = true
end if
on error goto 0
end function

 

[Dave Peterson]

Dim wkbk as workbook
dim teststr as string
dim wb as string
wb = "C:\somefolder\filename.xls"

teststr = ""
on error resume next
teststr = dir(wb)
on error goto 0

if teststr = "" then
msgbox "File not found!"
exit sub
end if

set wkbk = nothing
on error resume next
set wkbk = workbooks.open(filename:=wb, readonly:=true, updatelinks:=0)
on error goto 0

if wkbk is nothing then
msgbox wb & " wasn't opened
exit sub '???
end if