I run VB code to cycle through a group of excel files and chenge their
properties using the below code:

For i = 1 To .FoundFiles.Count
Set wb = Workbooks.Open(Filename:=.FoundFiles(i))

Sometimes a file opens with the following:

Password
Enter password for write access, or open read only.
Password:_________
Cancel
Read Only

Is there a way for VB to hit cancel if this dialogue box appears?

Maybe:

For i = 1 To .FoundFiles.Count
set wb = nothing
on error resume next
Set wb = Workbooks.Open(Filename:=.FoundFiles(i), password:="")
on error goto 0
if wb is nothing then
'don't do anything, it wasn't opened!
else
'do your real work here
end if
next i


Shimmess wrote:

I run VB code to cycle through a group of excel files and chenge their
properties using the below code:

For i = 1 To .FoundFiles.Count
Set wb = Workbooks.Open(Filename:=.FoundFiles(i))

Sometimes a file opens with the following:

Password
Enter password for write access, or open read only.
Password:_________
Cancel
Read Only

Is there a way for VB to hit cancel if this dialogue box appears?

--

Dave Peterson

Didn't work. Still prompted me and when I hit cancel I get the following:

Run-Time error '1004':
Application-defined or object-defined error

Any suggestions?

"Dave Peterson" wrote:

Maybe:

For i = 1 To .FoundFiles.Count
set wb = nothing
on error resume next
Set wb = Workbooks.Open(Filename:=.FoundFiles(i), password:="")
on error goto 0
if wb is nothing then
'don't do anything, it wasn't opened!
else
'do your real work here
end if
next i


Shimmess wrote:

I run VB code to cycle through a group of excel files and chenge their
properties using the below code:

For i = 1 To .FoundFiles.Count
Set wb = Workbooks.Open(Filename:=.FoundFiles(i))

Sometimes a file opens with the following:

Password
Enter password for write access, or open read only.
Password:_________
Cancel
Read Only

Is there a way for VB to hit cancel if this dialogue box appears?

--

Dave Peterson

I think you changed the suggested code.

If you did, you'll want to post your new version--or try it again with the
suggested code.



Shimmess wrote:

Didn't work. Still prompted me and when I hit cancel I get the following:

Run-Time error '1004':
Application-defined or object-defined error

Any suggestions?

"Dave Peterson" wrote:

Maybe:

For i = 1 To .FoundFiles.Count
set wb = nothing
on error resume next
Set wb = Workbooks.Open(Filename:=.FoundFiles(i), password:="")
on error goto 0
if wb is nothing then
'don't do anything, it wasn't opened!
else
'do your real work here
end if
next i


Shimmess wrote:

I run VB code to cycle through a group of excel files and chenge their
properties using the below code:

For i = 1 To .FoundFiles.Count
Set wb = Workbooks.Open(Filename:=.FoundFiles(i))

Sometimes a file opens with the following:

Password
Enter password for write access, or open read only.
Password:_________
Cancel
Read Only

Is there a way for VB to hit cancel if this dialogue box appears?

--

Dave Peterson


--

Dave Peterson

Thanks for your help Dave.

The below works so that when I press 'read only' it closes the file and move
on. I want to be able to suppress the dialogue box completely so the code
doesn't stop:

For i = 1 To .FoundFiles.Count
Set wb = Workbooks.Open(Filename:=.FoundFiles(i), ReadOnly:=False,
Password:="")
On Error GoTo 0
If wb.ReadOnly Then
wb.Close
RO = RO + 1
Else
'do work here
end if
next i

"Dave Peterson" wrote:

I think you changed the suggested code.

If you did, you'll want to post your new version--or try it again with the
suggested code.



Shimmess wrote:

Didn't work. Still prompted me and when I hit cancel I get the following:

Run-Time error '1004':
Application-defined or object-defined error

Any suggestions?

"Dave Peterson" wrote:

Maybe:

For i = 1 To .FoundFiles.Count
set wb = nothing
on error resume next
Set wb = Workbooks.Open(Filename:=.FoundFiles(i), password:="")
on error goto 0
if wb is nothing then
'don't do anything, it wasn't opened!
else
'do your real work here
end if
next i


Shimmess wrote:

I run VB code to cycle through a group of excel files and chenge their
properties using the below code:

For i = 1 To .FoundFiles.Count
Set wb = Workbooks.Open(Filename:=.FoundFiles(i))

Sometimes a file opens with the following:

Password
Enter password for write access, or open read only.
Password:_________
Cancel
Read Only

Is there a way for VB to hit cancel if this dialogue box appears?

--

Dave Peterson


--

Dave Peterson

Maybe you can use this:

For i = 1 To .FoundFiles.Count
Set wb = Nothing
On Error Resume Next
Set wb = Workbooks.Open(Filename:=.FoundFiles(i), _
Password:="", writerespassword:="")
On Error GoTo 0
If wb Is Nothing Then
'don't do anything, it wasn't opened!
MsgBox "not opened"
Else
'do your real work here
MsgBox "it's open"
End If
Next i

Don't delete those lines at the top ("set wb = nothing" and "On error resume
next"). They're important.



Shimmess wrote:

Thanks for your help Dave.

The below works so that when I press 'read only' it closes the file and move
on. I want to be able to suppress the dialogue box completely so the code
doesn't stop:

For i = 1 To .FoundFiles.Count
Set wb = Workbooks.Open(Filename:=.FoundFiles(i), ReadOnly:=False,
Password:="")
On Error GoTo 0
If wb.ReadOnly Then
wb.Close
RO = RO + 1
Else
'do work here
end if
next i

"Dave Peterson" wrote:

I think you changed the suggested code.

If you did, you'll want to post your new version--or try it again with the
suggested code.



Shimmess wrote:

Didn't work. Still prompted me and when I hit cancel I get the following:

Run-Time error '1004':
Application-defined or object-defined error

Any suggestions?

"Dave Peterson" wrote:

Maybe:

For i = 1 To .FoundFiles.Count
set wb = nothing
on error resume next
Set wb = Workbooks.Open(Filename:=.FoundFiles(i), password:="")
on error goto 0
if wb is nothing then
'don't do anything, it wasn't opened!
else
'do your real work here
end if
next i


Shimmess wrote:

I run VB code to cycle through a group of excel files and chenge their
properties using the below code:

For i = 1 To .FoundFiles.Count
Set wb = Workbooks.Open(Filename:=.FoundFiles(i))

Sometimes a file opens with the following:

Password
Enter password for write access, or open read only.
Password:_________
Cancel
Read Only

Is there a way for VB to hit cancel if this dialogue box appears?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

ps. I still think that the first suggestion is what you want, but the
writerespassword probably won't matter????

Dave Peterson wrote:

Maybe you can use this:

For i = 1 To .FoundFiles.Count
Set wb = Nothing
On Error Resume Next
Set wb = Workbooks.Open(Filename:=.FoundFiles(i), _
Password:="", writerespassword:="")
On Error GoTo 0
If wb Is Nothing Then
'don't do anything, it wasn't opened!
MsgBox "not opened"
Else
'do your real work here
MsgBox "it's open"
End If
Next i

Don't delete those lines at the top ("set wb = nothing" and "On error resume
next"). They're important.

Shimmess wrote:

Thanks for your help Dave.

The below works so that when I press 'read only' it closes the file and move
on. I want to be able to suppress the dialogue box completely so the code
doesn't stop:

For i = 1 To .FoundFiles.Count
Set wb = Workbooks.Open(Filename:=.FoundFiles(i), ReadOnly:=False,
Password:="")
On Error GoTo 0
If wb.ReadOnly Then
wb.Close
RO = RO + 1
Else
'do work here
end if
next i

"Dave Peterson" wrote:

I think you changed the suggested code.

If you did, you'll want to post your new version--or try it again with the
suggested code.



Shimmess wrote:

Didn't work. Still prompted me and when I hit cancel I get the following:

Run-Time error '1004':
Application-defined or object-defined error

Any suggestions?

"Dave Peterson" wrote:

Maybe:

For i = 1 To .FoundFiles.Count
set wb = nothing
on error resume next
Set wb = Workbooks.Open(Filename:=.FoundFiles(i), password:="")
on error goto 0
if wb is nothing then
'don't do anything, it wasn't opened!
else
'do your real work here
end if
next i


Shimmess wrote:

I run VB code to cycle through a group of excel files and chenge their
properties using the below code:

For i = 1 To .FoundFiles.Count
Set wb = Workbooks.Open(Filename:=.FoundFiles(i))

Sometimes a file opens with the following:

Password
Enter password for write access, or open read only.
Password:_________
Cancel
Read Only

Is there a way for VB to hit cancel if this dialogue box appears?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

--

Dave Peterson

That worked great! Thanks Dave!

"Dave Peterson" wrote:

ps. I still think that the first suggestion is what you want, but the
writerespassword probably won't matter????

Dave Peterson wrote:

Maybe you can use this:

For i = 1 To .FoundFiles.Count
Set wb = Nothing
On Error Resume Next
Set wb = Workbooks.Open(Filename:=.FoundFiles(i), _
Password:="", writerespassword:="")
On Error GoTo 0
If wb Is Nothing Then
'don't do anything, it wasn't opened!
MsgBox "not opened"
Else
'do your real work here
MsgBox "it's open"
End If
Next i

Don't delete those lines at the top ("set wb = nothing" and "On error resume
next"). They're important.

Shimmess wrote:

Thanks for your help Dave.

The below works so that when I press 'read only' it closes the file and move
on. I want to be able to suppress the dialogue box completely so the code
doesn't stop:

For i = 1 To .FoundFiles.Count
Set wb = Workbooks.Open(Filename:=.FoundFiles(i), ReadOnly:=False,
Password:="")
On Error GoTo 0
If wb.ReadOnly Then
wb.Close
RO = RO + 1
Else
'do work here
end if
next i

"Dave Peterson" wrote:

I think you changed the suggested code.

If you did, you'll want to post your new version--or try it again with the
suggested code.



Shimmess wrote:

Didn't work. Still prompted me and when I hit cancel I get the following:

Run-Time error '1004':
Application-defined or object-defined error

Any suggestions?

"Dave Peterson" wrote:

Maybe:

For i = 1 To .FoundFiles.Count
set wb = nothing
on error resume next
Set wb = Workbooks.Open(Filename:=.FoundFiles(i), password:="")
on error goto 0
if wb is nothing then
'don't do anything, it wasn't opened!
else
'do your real work here
end if
next i


Shimmess wrote:

I run VB code to cycle through a group of excel files and chenge their
properties using the below code:

For i = 1 To .FoundFiles.Count
Set wb = Workbooks.Open(Filename:=.FoundFiles(i))

Sometimes a file opens with the following:

Password
Enter password for write access, or open read only.
Password:_________
Cancel
Read Only

Is there a way for VB to hit cancel if this dialogue box appears?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

--

Dave Peterson