What's wrong with this sub?

[Travis]

This is a sub to open the File Open dialog box.

I've copied and pasted this out of an Access VBA book (Microsoft Access
VBA programming for the Absolute Beginner), and when I run it Access
stops at lstFiles.AddItem complaining "Compile Error: variable not
defined".

I've looked up listFiles.Additem in Google and it does appear to be a
legit VBA object and method. So why is Access 2003 telling me it
hasn't heard of it?

Private Sub Command15_Click()

'Declare a FileDialog object variable.
Dim myFileDialog As FileDialog
'Declare a variant to hold each file selected.
Dim vFileSelected As Variant
'Create a FileDialog object as an Open dialog window.
Set myFileDialog = Application.FileDialog(msoFileDialogOpen)
'If the user didn't press Cancel, process each selection.
If myFileDialog.Show = -1 Then

For Each vFileSelected In myFileDialog.SelectedItems
lstFiles.AddItem vFileSelected
Next vFileSelected
Else
' The user pressed Cancel.
MsgBox "No files selected."
End If
'Set the myFileDialog object variable to Nothing.
Set myFileDialog = Nothing
End Sub

Travis

 

[Tom Wickerath]

Hi Travis,

You make reference to lstFiles.AddItem in your code, yet you wrote "I've
looked up listFiles.Additem...". I suspect that you named your listbox
"listFiles" instead of "lstFiles". Verify that the name of the control
matches what your code indicates.

Does your code module include the very important "Option Explicit" as the
second line of code? If so, you may receive a compile error when you click on
Debug > Compile ProjectName in the VBA editor. For more information on why
this is important, and how to ensure that it is automatically added to all
newly created modules, please see the following article:

Always use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

This is a sub to open the File Open dialog box.

I've copied and pasted this out of an Access VBA book (Microsoft Access
VBA programming for the Absolute Beginner), and when I run it Access
stops at lstFiles.AddItem complaining "Compile Error: variable not
defined".

I've looked up listFiles.Additem in Google and it does appear to be a
legit VBA object and method. So why is Access 2003 telling me it
hasn't heard of it?

Private Sub Command15_Click()

'Declare a FileDialog object variable.
Dim myFileDialog As FileDialog
'Declare a variant to hold each file selected.
Dim vFileSelected As Variant
'Create a FileDialog object as an Open dialog window.
Set myFileDialog = Application.FileDialog(msoFileDialogOpen)
'If the user didn't press Cancel, process each selection.
If myFileDialog.Show = -1 Then

For Each vFileSelected In myFileDialog.SelectedItems
lstFiles.AddItem vFileSelected
Next vFileSelected
Else
' The user pressed Cancel.
MsgBox "No files selected."
End If
'Set the myFileDialog object variable to Nothing.
Set myFileDialog = Nothing
End Sub

Travis

 

[Travis]

Hi Travis,

You make reference to lstFiles.AddItem in your code, yet you wrote "I've
looked up listFiles.Additem...". I suspect that you named your listbox
"listFiles" instead of "lstFiles". Verify that the name of the control
matches what your code indicates.

I've looked up lstFiles.Additem, cut and pasted straight from the book
into Google. My typo was in my discussion of this in the newsgroup.

and by the way, I've looked it up the other way as well, while there
are some references to listFiles, there aren't many and they don't
appear to be what I'm after.

Does your code module include the very important "Option Explicit" as the
second line of code? If so, you may receive a compile error when you click on
Debug > Compile ProjectName in the VBA editor. For more information on why
this is important, and how to ensure that it is automatically added to all
newly created modules, please see the following article:

THanks for the tip, I've now checked the appropriate box. But it
hasn't made any difference.

So the bottom line is, is lstFiles.Additem a legit VBA statement or
not?

And if not, what should it be?

Or alternatively, perhaps you could point me to some simple code to
achieve the same thing.

I want to press a button and have it open the Windows File Open dialog
box.

That's all the VBA code listed here was supposed to do.

Ultimately I am trying to do more with it of course, I want to select
files, identify the type of file I'm importing and then import data
from them.

I regularly get statements showing commissions paid from various
providers. The statements are all different so I want to be able to
open the file dialog to select a particular file, use a combo box to
identify which payer this file is from, and run an appropriate routine
according to the payer.

But before I get to that, I've got to figure out how to launch the Open
File dialog box!

Travis

 

[Jim]

Travis,

I believe that Tom was making the point that you confused the letter 'l'
with the numeral '1' as they do look very similar to humans.

I hope that helps.

Jim

 

[Tom Wickerath]

Hi Travis,

So the bottom line is, is lstFiles.Additem a legit VBA statement or not?

Yes, it is, but *only* if the name of your list box is "lstFiles". I
actually tested the code you provided, using Access 2002, and it worked fine.

Or alternatively, perhaps you could point me to some simple code to
achieve the same thing.

I can point you to alternate code, but it's not exactly simple. However, you
can use copy and paste...

Call the standard Windows File Open/Save dialog box
http://www.mvps.org/access/api/api0001.htm


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Hi Travis,

You make reference to lstFiles.AddItem in your code, yet you wrote "I've
looked up listFiles.Additem...". I suspect that you named your listbox
"listFiles" instead of "lstFiles". Verify that the name of the control
matches what your code indicates.

I've looked up lstFiles.Additem, cut and pasted straight from the book
into Google. My typo was in my discussion of this in the newsgroup.

and by the way, I've looked it up the other way as well, while there
are some references to listFiles, there aren't many and they don't
appear to be what I'm after.

Does your code module include the very important "Option Explicit" as the
second line of code? If so, you may receive a compile error when you click on
Debug > Compile ProjectName in the VBA editor. For more information on why
this is important, and how to ensure that it is automatically added to all
newly created modules, please see the following article:

THanks for the tip, I've now checked the appropriate box. But it
hasn't made any difference.

So the bottom line is, is lstFiles.Additem a legit VBA statement or
not?

And if not, what should it be?

Or alternatively, perhaps you could point me to some simple code to
achieve the same thing.

I want to press a button and have it open the Windows File Open dialog
box.

That's all the VBA code listed here was supposed to do.

Ultimately I am trying to do more with it of course, I want to select
files, identify the type of file I'm importing and then import data
from them.

I regularly get statements showing commissions paid from various
providers. The statements are all different so I want to be able to
open the file dialog to select a particular file, use a combo box to
identify which payer this file is from, and run an appropriate routine
according to the payer.

But before I get to that, I've got to figure out how to launch the Open
File dialog box!

Travis

 

[Travis]

Oh, and just for fun I tried the following:

I tried it as LstFiles, the same problem happens. I tried it as
listFiles and ListFiles. No better.

Declaring it as a variable : Dim lstFiles As String results in
"Compile Error: Invalid Qualifier"

..lstFiles produces an "invalid or Unqualified Reference" error.

Me.lstFiles produces "Compile Error: Method or data member not found"

 

[Tom Wickerath]

Travis,

Me.lstFiles produces "Compile Error: Method or data member not found"

The reason it is doing this is because the NAME of the control on the form
is not lstfiles. Open the form in design view. Press F4 to view the
Properties dialog (if it is not already displayed). Select the list box. You
should see the name of the listbox displayed in the blue title bar of the
properties dialog. Select the Other tab. Name the control: lstFiles

What did you name the control? You should not need to open the VBA editor
to answer this question.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Oh, and just for fun I tried the following:

I tried it as LstFiles, the same problem happens. I tried it as
listFiles and ListFiles. No better.

Declaring it as a variable : Dim lstFiles As String results in
"Compile Error: Invalid Qualifier"

...lstFiles produces an "invalid or Unqualified Reference" error.

Me.lstFiles produces "Compile Error: Method or data member not found"

 

[Travis]

Hi Travis,


Yes, it is, but *only* if the name of your list box is "lstFiles". I
actually tested the code you provided, using Access 2002, and it worked fine.

Ahhh. A dim light is coming on...

As suggested by the name of the book I'm reading, I'm an absolute
beginner at VBA. I've only fairly recently started using Access at all
and my VBA skills don't go much beyond editing Excel macros...

So this "lstFiles" thing is the name that I am supposed to give a
listbox. Gee I wish the author of the book had mentioned something
about this before during or after the code listing during the
all-too-brief section of the book devoted to file dialogs!

So I can quit hunting around VBA references for a standard Access
object called lstFiles.

So if you could help a hapless newbie out just a little more Tom, would
you mind telling me exactly what you did to test out the code and make
it work?

You set up a form with a listbox on it called "lstFiles". Something
else, presumably a button, or maybe the form itself on opening, was the
event trigger to run the code.

I've done that, the result is a bug, "Compile Error; User-defined type
not defined", the text myFileDialog As FileDialog is highlighted in
blue.

Now Access help shows me that FileDialog is in fact a standard Access
property and object, so NOW what is Access griping about?

I can point you to alternate code, but it's not exactly simple. However, you
can use copy and paste...

Call the standard Windows File Open/Save dialog box
http://www.mvps.org/access/api/api0001.htm

Ack!

Travis

 

[Tom Wickerath]

Hi Travis,

Don't feel bad. Everyone has to start at the beginning and struggle through
problems like this.

So I can quit hunting around VBA references for a standard Access
object called lstFiles.

Yep. I'm not surprised that you found less hits with a search for
"ListFiles". By the way, lowercase "lst" is a standard naming convention for
a list box. Here are some links to naming conventions:

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

Commonly used naming conventions
http://www.mvps.org/access/general/gen0012.htm

http://www.xoc.net/standards/default.asp
--> http://www.xoc.net/standards/rvbanc.asp#Access

So if you could help a hapless newbie out just a little more Tom, would
you mind telling me exactly what you did to test out the code and make
it work?

You set up a form with a listbox on it called "lstFiles". Yes.

Something else, presumably a button, ... Yes.

or maybe the form itself on opening, was the event trigger to run the code. No.

I've done that, the result is a bug, "Compile Error; User-defined type
not defined", the text myFileDialog As FileDialog is highlighted in
blue.

You need to set a reference to the "Microsoft Office X.0 Object Library".
For Office 2000, X=9, for Office 2002, X=10, and for Office 2003, X=11. I'll
bet you can guess what version is likely needed when the next version of
Access is released.

I'm going to get some rest now, so hopefully this answers all of your
questions on this subject. I'm not sure when I'll be back on-line.

Merry Christmas & Happy Holidays
......and Happy Access Programming!


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Hi Travis,


Yes, it is, but *only* if the name of your list box is "lstFiles". I
actually tested the code you provided, using Access 2002, and it worked fine.

Ahhh. A dim light is coming on...

As suggested by the name of the book I'm reading, I'm an absolute
beginner at VBA. I've only fairly recently started using Access at all
and my VBA skills don't go much beyond editing Excel macros...

So this "lstFiles" thing is the name that I am supposed to give a
listbox. Gee I wish the author of the book had mentioned something
about this before during or after the code listing during the
all-too-brief section of the book devoted to file dialogs!

So I can quit hunting around VBA references for a standard Access
object called lstFiles.

So if you could help a hapless newbie out just a little more Tom, would
you mind telling me exactly what you did to test out the code and make
it work?

You set up a form with a listbox on it called "lstFiles". Something
else, presumably a button, or maybe the form itself on opening, was the
event trigger to run the code.

I've done that, the result is a bug, "Compile Error; User-defined type
not defined", the text myFileDialog As FileDialog is highlighted in
blue.

Now Access help shows me that FileDialog is in fact a standard Access
property and object, so NOW what is Access griping about?

I can point you to alternate code, but it's not exactly simple. However, you
can use copy and paste...

Call the standard Windows File Open/Save dialog box
http://www.mvps.org/access/api/api0001.htm

Ack!

Travis

 

[Travis]

Yep. I'm not surprised that you found less hits with a search for
"ListFiles". By the way, lowercase "lst" is a standard naming convention for
a list box. Here are some links to naming conventions:

[snip] THanks for those. Duly bookmarked...

You need to set a reference to the "Microsoft Office X.0 Object Library".
For Office 2000, X=9, for Office 2002, X=10, and for Office 2003, X=11. I'll
bet you can guess what version is likely needed when the next version of
Access is released.

Thanks a lot!

I was banging my head in frustration since I checked and it was ticked
but still wasn't working, then I re-read your post and checked again,
it was the Access 11.0 library which was referenced, not Office. Now
it works.

Thanks heaps, and a great Christmas to you as well!

Travis

 

[Tom Wickerath]

Hi Travis,

...it was the Access 11.0 library which was referenced, not Office.
Now it works.

Yes, in fact there are two required references for all Access applications:
Visual Basic for Applications and
Microsoft Access X.0 Object Library

where X has the same values that I outlined in my previous reply. You cannot
deselect these references, even if you tried to do so. Access MVP Allen
Browne has a good paper on references:

http://allenbrowne.com/ser-38.html

Look at the table titled "Which Version?".

I'm glad to hear that you got it working!


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Yep. I'm not surprised that you found less hits with a search for
"ListFiles". By the way, lowercase "lst" is a standard naming convention for
a list box. Here are some links to naming conventions:

[snip] THanks for those. Duly bookmarked...

You need to set a reference to the "Microsoft Office X.0 Object Library".
For Office 2000, X=9, for Office 2002, X=10, and for Office 2003, X=11. I'll
bet you can guess what version is likely needed when the next version of
Access is released.

Thanks a lot!

I was banging my head in frustration since I checked and it was ticked
but still wasn't working, then I re-read your post and checked again,
it was the Access 11.0 library which was referenced, not Office. Now
it works.

Thanks heaps, and a great Christmas to you as well!

Travis

 

[Travis]

I'm glad to hear that you got it working!

Unfortunately, I spoke a little too soon.

The file dialog box comes up, which is good. Unfortunately it doesn't
select any files. Every time I run it and select a few files, it goes
through to the "no files selected" message box and nothing gets added
to the listbox.

Any ideas why?

Travis

 

[Travis]

I've put message boxes through the whole code to see where it is going,
and I'm just baffled.

The code:

Private Sub btnGetFiles_Click()
'Declare a FileDialog object variable
Dim myFileDialog As FileDialog
'Declare a variant to hold each file selected
Dim vFileSelected As Variant
'Dim nlasterror As Integer

'Create a FileDialog object as a select dialog window
Set myFileDialog = Application.FileDialog(msoFileDialogFilePicker)


'If this MsgBox says the value is -1, it should go into the If loop
shouldn't it?
MsgBox "value is " & myFileDialog.Show

'If the user didn't press Cancel, process each selection.
If myFileDialog.Show = -1 Then
MsgBox "We're inside the If then loop"
For Each vFileSelected In myFileDialog.SelectedItems

lstFiles.AddItem vFileSelected
MsgBox "the foreach loop"

Next vFileSelected

Else

'The user pressed Cancel.
MsgBox "No files selected."

End If
Set myFileDialog = Nothing
End Sub

When the message box pops up, it reports that the value is -1.
However, the message box which announces that we're inside the if loop
does not come up, nor do any of the files I pick find their way into
lstFiles, it instead goes straight to the "No files selected" message
box.

Is there some reason why my If Then would fail, even though
myFileDialog.Show was indeed -1?

Travis

 

[Travis]

Is there some reason why my If Then would fail, even though
myFileDialog.Show was indeed -1?

Actually, never mind. The problem went away after I amended the value
to 0, just to see what would happen, then after some mucking about put
it back at -1.

The only logical explanation I can think of is that the -1, which was a
line of code directly copy and pasted out of a PDF ebook, actually was
pasted as a lookalike ASCII code or something, it looked like -1 to
human eyes but actually wasn't -1 as far as the computer was concerned.
With the -1 now retyped by hand, it works fine. Clicking on the "get
files" button brings up a file open dialog, I select as many files as I
like and when I press OK they all appear in the list box and appear in
the row source of lstFiles when I check the properties.

Thanks for your help and for your patience.

Travis

 

[Tom Wickerath]

Hi Travis,

I just read your last three replies. I'm glad to hear again that you got it
working.

http://home.comcast.net/~tutorme2/index.html


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[matkivskij]

This is a sub to open the File Open dialog box.

I've copied and pasted this out of an Access VBA book (Microsoft Access
VBA programming for the Absolute Beginner), and when I run it Access
stops at lstFiles.AddItem complaining "Compile Error: variable not
defined".

I've looked up listFiles.Additem in Google and it does appear to be a
legit VBA object and method. So why is Access 2003 telling me it
hasn't heard of it?

Private Sub Command15_Click()

'Declare a FileDialog object variable.
Dim myFileDialog As FileDialog
'Declare a variant to hold each file selected.
Dim vFileSelected As Variant
'Create a FileDialog object as an Open dialog window.
Set myFileDialog = Application.FileDialog(msoFileDialogOpen)
'If the user didn't press Cancel, process each selection.
If myFileDialog.Show = -1 Then

For Each vFileSelected In myFileDialog.SelectedItems
lstFiles.AddItem vFileSelected
Next vFileSelected
Else
' The user pressed Cancel.
MsgBox "No files selected."
End If
'Set the myFileDialog object variable to Nothing.
Set myFileDialog = Nothing
End Sub

Travis