search for file path, please help

[GerryE]

I need help locating a file from a form named frmInsBlock. In this form I
have a textbox named BlkName, this is where I want to type the name of the
file. There is also a button name SearchButton that will execute the code
and another textbox named BkLocation. The directory of the file is
C:\VWR-Setup.
What I'm trying to do is type in the entire or part of the file name and
have it search for the file in the subdirectory. Once a file is located I
need to display the full path of the file without the file name.
After the file path is found a list box named ListBox1 will display all the
files found in the BkLocation directory displayed and a preview of the file
will be displayed on the form. I have the code for the listbox and the
preview. I just need help finding the subdirectory. This is for a form that
will interact with AutoCAD.
I have spent two days searching and trying different codes and have not been
successful. I am relatively new to VBA but have found my way around it.
Please help. I am starting from scratch.

 

[Steve McLeod]

I am assuming you are using Office 2003. In Office 2000 this gets more
complicated. You can find more info in the object browser and help.

Private dlgOpen As FileDialog
Private varSource As Variant
Private varSaveSource As Variant
Private strIntFilePath As String

Set dlgOpen = Application.FileDialog(DialogType:=msoFileDialogOpen)

With dlgOpen
.AllowMultiSelect = False
.Filters.Add "Excel WorkBook", "*.xl*", 1
.Title = "Data Sheet Location Prompt"
.InitialFileName = varSaveSource
.Show
If .SelectedItems.Count > 0 Then
varSource = .SelectedItems(1)
varSaveSource = Left(varSource, InStrRev(varSource, "\",
Len(varSource), vbBinaryCompare))
Else
MsgBox "No Excel workbook has been selected.", vbExclamation,
"No File Selected"
' GoTo Exit_WorkBookPrompt
End If
End With

 

[Klatuu]

See this site for an API that presents the common file open dialog. It is
used instead of the ActiveX control.

http://www.mvps.org/access/api/api0001.htm

 

[GerryE]

Thanks for the help,
Unfortunately I am using office 2k. I don't want to use the file open
dialog, I just want it to search the subdirectory of C:\VWR-Setup for
whatever file name is typed in the BlkName textbox and display the full path
in the BkLocation textbox. There might be anywhere from 10-20 sub-folders in
the sub-directory and as many as 5000 files in the sub folders. For example,
if the file name 98762-123 was typed in the BlkName, then the Search button
was pressed. The BkLocation text box would display something like
"C:\VWR-Setup\Specifications\Hoods\Labconco\". The reason I don't want the
file open dialog is the user will not know where the files are located. I
don't want them to know where they are because they will modify the files,
move them or delete them. The BkLocation textbox will not be visible to them
but I need it so I will show all file names in the listbox1. Hope this makes
it a bit clearer.

 

[Klatuu]

Look in VBA Help for the FileSearch Object. I think it will do for you want
you want.

 

[GerryE]

Thanks Klatuu,
I was able to figure it out. However, I have another question. Here is
where I'm at. My form has a search button that when pressed, the code will
search a preset directory with any number of sub-directories for a file to
match the name entered in a text box. If the file is found a list box
"ListBox1" is populated with every file in that sub-directory. What I need
to do now is have the file programmatically select it. The file name would
be typed in the "BlkName" text box and the matching file in "ListBox1" be
selected. I know how to use the "Selected ()", but I cannot figure out how
to search the listbox, retreive the file number -1. Please advise.

 

[Klatuu]

You will have to set up a loop to find the file name in the list box then
select the item when you find a match:

With Me.ListBox1
For lngX = 0 to .ListCount
If .ItemData(lngX) = Me.BlkName Then
.Selected(lngX) = True
Exit For
End If
Next lngX
End With

 

[GerryE]

I got a compile error.

You will have to set up a loop to find the file name in the list box then
select the item when you find a match:

With Me.ListBox1
For lngX = 0 to .ListCount
If .ItemData(lngX) = Me.BlkName Then
.Selected(lngX) = True
Exit For
End If
Next lngX
End With

 

[Klatuu]

ok
how about posting the error message and the line on which it occurs.
I can't see it from here.

 

[GerryE]

Error message:
Compile error

line:
If .ItemData (lngX) = Me.BlkName Then

ItemData is highlighted

 

[Klatuu]

I don't see a problem. Is the name of the text box blkName ?

 

[GerryE]

yes, the text box in blkName,
for some reason my vba version does not recognize itemData

 

[Klatuu]

I just tested the code I sent earlier, and it works fine. What version of
Access are you on?
Also, it could be a Library Reference problem. Check your references (In
VBA editor, Tools, References) and make sure you have the Access Object
Library checked.

 

[GerryE]

Thank you, I got it working!!

I just tested the code I sent earlier, and it works fine. What version of
Access are you on?
Also, it could be a Library Reference problem. Check your references (In
VBA editor, Tools, References) and make sure you have the Access Object
Library checked.