Run-time Error '5' - Invalid procedure call or argument

[Trefor]

I am not sure what is going on here and am interested in any feedback I can
get please.

I am using some code to open an excel workbook that is password protected. I
also use code to save the file with the password.

On one particular file I am getting this run-time error in the following code:

DataFile = Application.GetOpenFilename("Data Collect Automation - Data
Files (*.DCA), *.DCA", , "Data Collect Automation - Data Files")
If DataFile <> "" And DataFile <> "False" Then
On Error Resume Next
Workbooks.Open DataFile, 0, False, , Password:="mypassword"
<--error
here

Two things have me beat, why does this not get trapped in the error trap?

Secondly, the file does get opened so the password must be correct, I tried
opening the same file the same way but without a password and I get an error
1004 and it gets trapped ok. So if the file gets opened as normal (although
the code crashes), what is the error actually referring to? It can't be the
code because it works fine on all the other files and has been working fine
for years.

I have an import routine routine so I imported all the data from the old
file to a new fiile, saved it and re-opened without any code change and it
works fine. So it must be related to this particualar file. I didn't want to
waste to much time finding out why it occured, but without an error trap to
handle it I am forced to fix one or the other.

Anyone?

 

[Dave Peterson]

Just a guess--maybe the .DCA file that you're opening has a workbook_open event
that has a problem. You could avoid that event (and all events) by turning off
..enableevents before you try to open the other workbook.

Second (and this is just a personal perference), I wouldn't mix positional
parameters with named parameters. I find code using named parms much easier to
use--especially when some parms are omitted.

And if you declare DataFile as a variant, you could just check to see if it was
false (not the string "False").

Dim DataFile As Variant
Dim wkbk As Workbook
DataFile = Application.GetOpenFilename _
(filefilter:="Data Collect Automation - Data Files, *.DCA", _
Title:="Data Collect Automation - Data Files")
If DataFile <> False Then
Application.EnableEvents = False
On Error Resume Next
Set wkbk = Workbooks.Open(Filename:=DataFile, _
UpdateLinks:=0, ReadOnly:=False, Password:="mypassword")
On Error GoTo 0
Application.EnableEvents = True
End If

But this is more of a next test for you than a real suggestion.

Good luck.

 

[Trefor]

Dave,

I wish I could guess as good as you . Spot on and I will implement your
suggestions. MAny thanks.