msofiledialogfilepicker error

[Fredrated]

My code compiles, but I get 'invalid procedure call or argument' when I try
to use the following code (copied directly from
http://support.microsoft.com/kb/279508 with edits):

Public Function GetFile_Dialog(ByVal starthere As String, ByVal MyTitle As
String, ByVal ftype As String, ByVal fdesc As String) As String
Dim fDialog As Office.FileDialog
Dim varFile As Variant
Dim s As String

'Set up the File Dialog.
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
'Allow user to make one selection in dialog box
.AllowMultiSelect = False

'Set the title of the dialog box.
.title = MyTitle

'Clear out the current filters, and add our own.
.Filters.Clear

.Filters.Add fdesc, ftype 'this line errors-out

'Show the dialog box. If the .Show method returns True, the
'user picked at least one file. If the .Show method returns
'False, the user clicked Cancel.
If .Show = True Then
s = .SelectedItems(1)
Else
MsgBox "You clicked Cancel in the file dialog box."
s = ""
End If
End With

GetFile_Dialog = s

End Function

The error occurs at the line:
..Filters.Add fdesc, ftype

hitting the . (dot) after .Filters brings up the methods list, of which
'add' is a member, so how could it be an invalid procedure?

Another question, possibly related, is: when I add 'Microsoft Office N
Object library' to the references list it is Office 12, but all of my other
references are to Office 10!
I have MS Access 2002 SP3 running on XP SP3.

Thanks for any help.

Fred

 

[Dirk Goldgar]

My code compiles, but I get 'invalid procedure call or argument' when I
try
to use the following code (copied directly from
http://support.microsoft.com/kb/279508 with edits):

Public Function GetFile_Dialog(ByVal starthere As String, ByVal MyTitle As
String, ByVal ftype As String, ByVal fdesc As String) As String
Dim fDialog As Office.FileDialog
Dim varFile As Variant
Dim s As String

'Set up the File Dialog.
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
'Allow user to make one selection in dialog box
.AllowMultiSelect = False

'Set the title of the dialog box.
.title = MyTitle

'Clear out the current filters, and add our own.
.Filters.Clear

.Filters.Add fdesc, ftype 'this line errors-out

'Show the dialog box. If the .Show method returns True, the
'user picked at least one file. If the .Show method returns
'False, the user clicked Cancel.
If .Show = True Then
s = .SelectedItems(1)
Else
MsgBox "You clicked Cancel in the file dialog box."
s = ""
End If
End With

GetFile_Dialog = s

End Function

The error occurs at the line:
.Filters.Add fdesc, ftype

hitting the . (dot) after .Filters brings up the methods list, of which
'add' is a member, so how could it be an invalid procedure?

What are the values of fdesc and ftype? The code works for me (using Access
2003), with suitable arguments.

Another question, possibly related, is: when I add 'Microsoft Office N
Object library' to the references list it is Office 12, but all of my
other
references are to Office 10!

Yes, it's peculiar, but I see the same thing with my Access 2003
installation. I'm going to do a little web search and see if I can find an
explanation.

 

[Fredrated]

What are the values of fdesc and ftype? The code works for me (using Access

2003), with suitable arguments.

File description and file type, these are passed by the calling procedure.
In the instance where I am trying to run this,
fdesc = "Spreadsheet"
ftype = "*.xls"

 

[Dirk Goldgar]

File description and file type, these are passed by the calling procedure.
In the instance where I am trying to run this,
fdesc = "Spreadsheet"
ftype = "*.xls"

Are you sure you haven't got them reversed in the call to the function?
Passing those arguments as you posted works fine for me, but if I reverse
them I get the error you report.

 

[Fredrated]

Are you sure you haven't got them reversed in the call to the function?

since you ask, I
- changed the order. No improvement
- renamed the parameters fdesc and ftype in case keyword conflict. No
improvement
- added extra double-quotes, so I pass ""Spreadsheet"" and ""*.xls"", no good.

directly used "Spreasheet" and "*.xls" as in:
..Filters.Add "Spreadsheet", "*.xls"
This worked! So I guess I will kludge the function, as in:

Case 1
.Filters.Add "Spreadsheet", "*.xls"
Case 2
.Filters.Add "Word Document", "*.doc"
etc.

Not the best solution but works for now.

Thanks for your help!

Fred

 

[Dirk Goldgar]

since you ask, I
- changed the order. No improvement
- renamed the parameters fdesc and ftype in case keyword conflict. No
improvement
- added extra double-quotes, so I pass ""Spreadsheet"" and ""*.xls"", no
good.

directly used "Spreasheet" and "*.xls" as in:
.Filters.Add "Spreadsheet", "*.xls"
This worked!

If that works, I can almost guarantee there's something wrong in the way
you're passing the arguments to your function, and it could easily be fixed.

So I guess I will kludge the function, as in:

Case 1
.Filters.Add "Spreadsheet", "*.xls"
Case 2
.Filters.Add "Word Document", "*.doc"
etc.

Not the best solution but works for now.

If you want to try to make it work, I'll be happy to look at the code that
you were using to call the function.

 

[David W. Fenton]

My code compiles, but I get 'invalid procedure call or argument'
when I try to use the following code (copied directly from
http://support.microsoft.com/kb/279508 with edits):

Public Function GetFile_Dialog(ByVal starthere As String, ByVal
MyTitle As String, ByVal ftype As String, ByVal fdesc As String)
As String Dim fDialog As Office.FileDialog
Dim varFile As Variant
Dim s As String

'Set up the File Dialog.
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

Why not use:

Dim fDialog As Application.FileDialog

That way you don't need a reference to the Office Object Library.

I tried it in an Access database without the Office Object Library
referenced, and I did indeed get the same ENUM as you got, but when
I chose from it, I got this message:

The library which contains this symbol is not referenced by the
current project, so the symbol is undefined. Would you like to
add a reference to the containing library now?

This is very similar to what you'd get if you did this:

Dim cnn As Connection
Set cnn = CurrentProject.Connection

The SET statement would work, returning an ADO connection object,
but without a reference to ADO, VBA knows nothing about the ADODB
Connection object type.

All the above assumes you have no reference set to the Office
library, though. If you have it, the ENUM should be visible in the
object browser and usable, and I don't know why you'd have this
problem.

But the issue is that just because you have an object available via
the top-level Application object (for instance,
Application.CurrentDB and Application.DBEngine both offer access to
databases via DAO, but without providing the strongly-typed object
classes that a reference to DAO would allow) doesn't mean you get
all the data types that the object in question supports.

 

[Fredrated]

Thanks for the useful information.
I will get back to this after I finish another job that just got pushed to
the top of my queue!