Almost got it, but now I really need help

[DawnTreader]

Hello

i have a project where i am trying to open a folder to which my app will
only know the first five characters of.

here is my code for the function:

Public Function fsFoldersearch(strProdType As String, strFolderSerial As
String)

Dim strFolder As String
Let strFolder = "C:\WO\" & strProdType & "\1 Completed\" &
strFolderSerial & "*"
Shell """C:\WINDOWS\EXPLORER.EXE""" & " " & Chr(34) & strFolder &
Chr(34), vbNormalFocus

End Function

i thought maybe a wildcard would work, but alas... it does not.
unfortunately that means i need to search for the folder and when i find it
put that in as the full folder name in place of the strFolderSerial variable
i have in the code.

can someone please help me with searching for the folder name based on the
known first 5 characters.

i also posted this problem elsewhere with a more full description, check
this out:

"i have need of a little help. i have looked at a few resources on the
internet regarding making lists of files and folders and either returning
them to a table or listbox, as well as doing other things with the returned
data. i even had a coworker jog my brain as to how i would open explorer to
where i want it. thats all great.

my problem is that i am unsure where to beging or what functions to use to
do what i would like to create.

here is the scenario. i have a table with products, each product has a
serial number which correspond to part of the name a folder on a drive where
all sorts of information is stored. i was using hyperlinks to make the
folders open on a click. the problem is that people are constantly changing
the name or placement of these folders. i know they shouldnt be, but i dont
care about that anymore. i know there is a way to do what i want. i just dont
know how.

i want to use the serial number to find the corresponding folder and then
open that folder with windows explorer.

here is what i know has to happen. the command button that i create has to
call a routine to take the field where the serial number is stored, it will
have to be trimmed to 5 character from the left, i know how to do that and
then compare that to a search through all the folders starting at the
specified folder (C:\WO\) and look at the first 5 characters of the folder
name. then it should store the folder name and use that as an argument to
open an explorer window.

what function would best serve this purpose? can anyone suggest some code to
get this started? has someone already done this before?"

and

"i dont want to store paths in a table. my db is bloated enough as it is.

all i need is to get the first 5 characters of a folder name into a variable
and compare that with the first 5 characters of a field on my form. if the 2
match then i want to grab the whole folder name and cause it to open an
explorer window. putting all the paths in a table is unnecessary. can we not
just loop until the variable with the first 5 folder name characters match
the 5 field characters?

another thing i see in this code is that it is written to find files. i dont
want a file. i want folders. is there a function that will call the name of
just the folder and allow me to store it in a string variable so i can
compare it to a field?"

the function works upto the subfolder name that i only have the first 5
characters of. without knowing how to find the full folder name and return
that to put in the shell command i am dead in the water.

 

[Ken Snell \(MVP\)]

Perhaps this:

Public Function fsFoldersearch(strProdType As String, strFolderSerial As
String)
Const strPath As String = "C:\WO\" & strProdType & "\1 Completed\"
Dim strFolder As String
strFolder = Dir(strPath & strFolderSerial & "*", vbDirectory)
If strFolder <> "" Then _
Shell """C:\WINDOWS\EXPLORER.EXE""" & " " & Chr(34) & _
strPath & strFolder & Chr(34), vbNormalFocus
End Function

 

[DawnTreader]

Hello

Wow. thanks!

i get anerror on the:

Const strPath As String = "C:\WO\" & strProdType & "\1 Completed\"

it says that a constant expression is required.

does that mean we have to do one of those "type" bits of code?

i dont quite understand what it is looking for?

 

[DawnTreader]

whoopps

it specifically highlights the strProdType when i do a debug.

 

[DawnTreader]

Hello again

one other thing i am wondering in looking over the code, are we going to be
missing a trailing slash?

 

[Ken Snell \(MVP\)]

Sorry - error on my part. Try this:

Public Function fsFoldersearch(strProdType As String, strFolderSerial As
String)
Const strPathF As String = "C:\WO\"
Const strPathL As String = "\1 Completed\"
Dim strFolder As String
strFolder = Dir(strPathF & strProdType & strPathL & _
strFolderSerial & "*", vbDirectory) & "\"
If strFolder <> "" Then _
Shell Chr(34) & "C:\WINDOWS\EXPLORER.EXE" & " " & _
strPath & strFolder & Chr(34), vbNormalFocus
End Function

 

[Ken Snell \(MVP\)]

Or, this should be better:

Public Function fsFoldersearch(strProdType As String, strFolderSerial As
String)
Const strPathF As String = "C:\WO\"
Const strPathM As String = "\1 Completed\"
Dim strFolder As String
strFolder = Dir(strPathF & strProdType & strPathL & _
strFolderSerial & "*", vbDirectory) & "\"
If strFolder <> "" Then _
Shell Chr(34) & "EXPLORER.EXE" & " " & _
strPath & strFolder & Chr(34), vbNormalFocus
End Function

--

Ken Snell
<MS ACCESS MVP>

Sorry - error on my part. Try this:

Public Function fsFoldersearch(strProdType As String, strFolderSerial As
String)
Const strPathF As String = "C:\WO\"
Const strPathL As String = "\1 Completed\"
Dim strFolder As String
strFolder = Dir(strPathF & strProdType & strPathL & _
strFolderSerial & "*", vbDirectory) & "\"
If strFolder <> "" Then _
Shell Chr(34) & "C:\WINDOWS\EXPLORER.EXE" & " " & _
strPath & strFolder & Chr(34), vbNormalFocus
End Function

<snipped>

 

[DawnTreader]

Hello

Realy realy close!

i have been tinkering with the code you posted. i am really starting to put
my head around it, but i am having trouble with one other thing i need.

first here is the completed working code:

Const strPathF As String = "P:\WO\"
Const strPathM As String = "\1 Completed\"
Dim strFolder As String

strFolder = Dir(strPathF & strProdType & strPathM & strFolderSerial &
"*", vbDirectory) '& "\"

If strFolder <> "" Then
Shell """C:\WINDOWS\EXPLORER.EXE""" & " " & Chr(34) & strPathF &
strProdType & strPathM & strFolder & Chr(34), vbNormalFocus
Else
MsgBox "Folder does not exist or project not completed", vbOKOnly,
"No Folder Found"
End If

you will notice a few differences then what you posted. let me explain them.
when i copied and pasted i found a typo. strPathL wasnt declared, strPathM
was so i changed the Dir() function accordingly. the next thing i found was
that we dont really need the trailing slash for the shell command. i put the
double quotes back in, but i think i could do the chr(34) thing before and
after the explorer.exe part. the other problem i was having is that the path
created in the strFolder string kept being cut back to just the last part,
the strFolderSerial and the actual name of the folder. eg "c:\wo\compressor\1
completed\08090 Bonet PK" was becoming "08090 Bonet PK", which was unuseable
for the shell command. therefore i put the other parts back in by creating
the strfolder path and using the strfolder at the end.

this works exactly the way i want, but i would like to know why the Dir()
function turns strFolder into "08090 Bonet PK".

basically the way i think of what i am doing with this code is creating a
desktop shortcut, activating and deleting it all in one stroke. the problem
is that the path from "c:\" to the folder i want is not retained. why?

the other thing i would like to be able to do is figure out a way to have it
look for the folder, then if the folder isnt there, look somewhere else. the
somewhere else is just one step up from the "1 completed" folder. basically i
need to understand why the code below isnt working:

Public Function fsFoldersearch(strProdType As String, strFolderSerial As
String)

Const strPathF As String = "P:\WO\"
Const strPathM As String = "\1 Completed\"
Const strPathDiv As String = "\"
Dim strFolder As String

strFolder = Dir(strPathF & strProdType & strPathDiv & strFolderSerial &
"*", vbDirectory) '& "\"
If FolderExists(strPathF & strProdType & strPathDiv & strFolderSerial) =
True Then
MsgBox "The Product has not been completed.", vbOKOnly, "Not
Completed"
Shell """C:\WINDOWS\EXPLORER.EXE""" & " " & Chr(34) & strPathF &
strProdType & strPathDiv & strFolderSerial & Chr(34), vbNormalFocus
Else
strFolder = Dir(strPathF & strProdType & strPathM & strFolderSerial
& "*", vbDirectory) '& "\"
If FolderExists(strPathF & strProdType & strPathM & strFolderSerial)
= True Then
Shell """C:\WINDOWS\EXPLORER.EXE""" & " " & Chr(34) & strPathF &
strProdType & strPathM & strFolder & Chr(34), vbNormalFocus
Else
MsgBox "Folder does not exist", vbOKOnly, "No Folder"
End If
End If
End Function

Function FolderExists(strPath As String) As Boolean
On Error Resume Next
FolderExists = ((GetAttr(strPath) And vbDirectory) = vbDirectory)
End Function

i borrowed the folderexist function from a web site, it seemed like it would
do what i wanted. http://www.allenbrowne.com/func-11.html.

at this point i would like to understand the Dir() function a little more.

 

[Ken Snell \(MVP\)]

Answers/comments inline...
--

Ken Snell
<MS ACCESS MVP>

Hello

Realy realy close!

i have been tinkering with the code you posted. i am really starting to
put
my head around it, but i am having trouble with one other thing i need.

first here is the completed working code:

Const strPathF As String = "P:\WO\"
Const strPathM As String = "\1 Completed\"
Dim strFolder As String

strFolder = Dir(strPathF & strProdType & strPathM & strFolderSerial &
"*", vbDirectory) '& "\"

If strFolder <> "" Then
Shell """C:\WINDOWS\EXPLORER.EXE""" & " " & Chr(34) & strPathF &
strProdType & strPathM & strFolder & Chr(34), vbNormalFocus
Else
MsgBox "Folder does not exist or project not completed", vbOKOnly,
"No Folder Found"
End If

you will notice a few differences then what you posted. let me explain
them.
when i copied and pasted i found a typo. strPathL wasnt declared, strPathM
was so i changed the Dir() function accordingly.

My error... I mistakenly changed the variable name in preparation to make
another change to the code itself, then decided not to make the code
change... but forgot to change the variable name back to the "L" one. Good
catch.

the next thing i found was
that we dont really need the trailing slash for the shell command.

Yes, you don't need the trailing slash, but I left it there because you'd
earlier commented that you thought you'd need it.

i put the
double quotes back in,

No, no, no. What I posted, where the entire "string" is enclosed in just one
set of " characters, is the correct syntax for the Shell command. And, I
tested the syntax before I posted the suggested code.

but i think i could do the chr(34) thing before and
after the explorer.exe part. the other problem i was having is that the
path
created in the strFolder string kept being cut back to just the last part,
the strFolderSerial and the actual name of the folder. eg
"c:\wo\compressor\1
completed\08090 Bonet PK" was becoming "08090 Bonet PK",

This is how the Dir function works... it returns just the name of the
subfolder (directory) or file that is being "looked up".

which was unuseable
for the shell command. therefore i put the other parts back in by creating
the strfolder path and using the strfolder at the end.

Before I goofed on my repost of the code, that is why I had the strPathL,
strPathF, strFolder, and strProdType variables in the concatenated string
that is being given to the Shell command. The construction that I showed
essentially "rebuilds" the entire path to the subfolder, using the variables
again... just like the construction in the Dir command line. You are correct
to have put that back into the Shell line... I can only plead "tiredness" as
an excuse for making the errors in the repost; sorry!

this works exactly the way i want, but i would like to know why the Dir()
function turns strFolder into "08090 Bonet PK".

See answer above.

basically the way i think of what i am doing with this code is creating a
desktop shortcut, activating and deleting it all in one stroke. the
problem
is that the path from "c:\" to the folder i want is not retained. why?

the other thing i would like to be able to do is figure out a way to have
it
look for the folder, then if the folder isnt there, look somewhere else.
the
somewhere else is just one step up from the "1 completed" folder.
basically i
need to understand why the code below isnt working:

Public Function fsFoldersearch(strProdType As String, strFolderSerial As
String)

Const strPathF As String = "P:\WO\"
Const strPathM As String = "\1 Completed\"
Const strPathDiv As String = "\"
Dim strFolder As String

strFolder = Dir(strPathF & strProdType & strPathDiv & strFolderSerial &
"*", vbDirectory) '& "\"
If FolderExists(strPathF & strProdType & strPathDiv & strFolderSerial)
=
True Then
MsgBox "The Product has not been completed.", vbOKOnly, "Not
Completed"
Shell """C:\WINDOWS\EXPLORER.EXE""" & " " & Chr(34) & strPathF &
strProdType & strPathDiv & strFolderSerial & Chr(34), vbNormalFocus
Else
strFolder = Dir(strPathF & strProdType & strPathM & strFolderSerial
& "*", vbDirectory) '& "\"
If FolderExists(strPathF & strProdType & strPathM &
strFolderSerial)
= True Then
Shell """C:\WINDOWS\EXPLORER.EXE""" & " " & Chr(34) & strPathF
&
strProdType & strPathM & strFolder & Chr(34), vbNormalFocus
Else
MsgBox "Folder does not exist", vbOKOnly, "No Folder"
End If
End If
End Function

Function FolderExists(strPath As String) As Boolean
On Error Resume Next
FolderExists = ((GetAttr(strPath) And vbDirectory) = vbDirectory)
End Function

i borrowed the folderexist function from a web site, it seemed like it
would
do what i wanted. http://www.allenbrowne.com/func-11.html.

at this point i would like to understand the Dir() function a little more.

You can read more information about the Dir function in the VBA Help file.
This website also contains information about this function:
http://msdn2.microsoft.com/en-us/library/aa262726.aspx

You do not need to use the FolderExists function from Allen's website.
Testing whether the strFolder variable is equal to an empty string ("") is
the same result as calling that extra function. The following code should
work:

Public Function fsFoldersearch(strProdType As String, strFolderSerial As
String)

Const strPathF As String = "P:\WO\"
Const strPathM As String = "\1 Completed\"
Const strPathDiv As String = "\"
Dim strFolder As String

strFolder = Dir(strPathF & strProdType & strPathDiv & -
strFolderSerial & "*", vbDirectory)
If strFolder <> "" Then
MsgBox "The Product has not been completed.", vbOKOnly, _
"Not Completed"
Shell """C:\WINDOWS\EXPLORER.EXE" & " " & strPathF & _
strProdType & strPathDiv & strFolderSerial & Chr(34), _
vbNormalFocus
Else
strFolder = Dir(strPathF & strProdType & strPathM & _
strFolderSerial & "*", vbDirectory)
If strFolder <> "" Then
Shell """C:\WINDOWS\EXPLORER.EXE" & " " & _
strPathF & strProdType & strPathM & strFolder & _
Chr(34), vbNormalFocus
Else
MsgBox "Folder does not exist", vbOKOnly, "No Folder"
End If
End If
End Function

 

[Ken Snell \(MVP\)]

Sorry.... you are correct about the need for the double quotes... I've just
rechecked my test (I must have been half asleep....!). And I've shortened
the code to use just Explorer.exe, without the full path. This code should
be good:


Public Function fsFoldersearch(strProdType As String, strFolderSerial As
String)

Const strPathF As String = "P:\WO\"
Const strPathM As String = "\1 Completed\"
Const strPathDiv As String = "\"
Dim strFolder As String

strFolder = Dir(strPathF & strProdType & strPathDiv & -
strFolderSerial & "*", vbDirectory)
If strFolder <> "" Then
MsgBox "The Product has not been completed.", vbOKOnly, _
"Not Completed"
Shell Chr(34) & "EXPLORER.EXE" & Chr(34) & " " & _
Chr(34) & strPathF & strProdType & strPathDiv & _
strFolderSerial & Chr(34), vbNormalFocus
Else
strFolder = Dir(strPathF & strProdType & strPathM & _
strFolderSerial & "*", vbDirectory)
If strFolder <> "" Then
Shell Chr(34) & "EXPLORER.EXE" & Chr(34) & " " & _
Chr(34) & strPathF & strProdType & strPathM & _
strFolder & Chr(34), vbNormalFocus
Else
MsgBox "Folder does not exist", vbOKOnly, "No Folder"
End If
End If
End Function

 

[DawnTreader]

Hello

I know the tired thing.

No problem, helps me to learn when other make mistakes i have to correct.

 

[DawnTreader]

Hello

YES! woo hoo!

thanks for your help. that is great code. it works exactly the way i need it
to.

 

[Ken Snell \(MVP\)]

Hello

YES! woo hoo!

thanks for your help. that is great code. it works exactly the way i need
it
to.

You're welcome. ... .. and again my apologies for the typos....

 

[DawnTreader]

Hello again Ken

i have a new twist...

here is the code currently

Public Function fsFoldersearch(strProdType As String, strProductSerial As
String, strWorkOrderFolder As String)

Const strPathF As String = "P:\WO\"
Const strPathM As String = "\1 Completed\"
Const strPathDiv As String = "\"
Dim strFolder As String

'finding by workorder at begining of folder name in p drive workorder
product type folder
strFolder = Dir(strPathF & strProdType & strPathDiv & strWorkOrderFolder
& "*", vbDirectory)
If strFolder <> "" Then
MsgBox "The Product has not been completed.", vbOKOnly, "Not
Completed"
Shell Chr(34) & "EXPLORER.EXE" & Chr(34) & " " & Chr(34) & strPathF
& strProdType & strPathDiv & strFolder & Chr(34), vbNormalFocus
Else
'finding by workorder at begining of folder name in p drive
workorder product type completed folder
strFolder = Dir(strPathF & strProdType & strPathM &
strWorkOrderFolder & "*", vbDirectory)
If strFolder <> "" Then
Shell Chr(34) & "EXPLORER.EXE" & Chr(34) & " " & Chr(34) &
strPathF & strProdType & strPathM & strFolder & Chr(34), vbNormalFocus
Else
'finding by first five characters of serial number at begining
of folder name in p drive workorder product type folder
strFolder = Dir(strPathF & strProdType & strPathDiv &
strProductSerial & "*", vbDirectory)
If strFolder <> "" Then
MsgBox "The Product has not been completed.", vbOKOnly, "Not
Completed"
Shell Chr(34) & "EXPLORER.EXE" & Chr(34) & " " & Chr(34) &
strPathF & strProdType & strPathDiv & strFolder & Chr(34), vbNormalFocus
Else
'finding by first five characters of serial number at
begining of folder name in p drive workorder product type completed folder
strFolder = Dir(strPathF & strProdType & strPathM &
strProductSerial & "*", vbDirectory)
If strFolder <> "" Then
Shell Chr(34) & "EXPLORER.EXE" & Chr(34) & " " & Chr(34)
& strPathF & strProdType & strPathM & strFolder & Chr(34), vbNormalFocus
Else
' 'finding by
' strFolder = Dir(strPathF & strProdType & strPathDiv &
strProductSerial & "*", vbDirectory)
' If strFolder <> "" Then
' MsgBox "The Product has not been completed.",
vbOKOnly, "Not Completed"
' Shell Chr(34) & "EXPLORER.EXE" & Chr(34) & " " &
Chr(34) & strPathF & strProdType & strPathDiv & strFolder & Chr(34),
vbNormalFocus
' Else
' strFolder = Dir(strPathF & strProdType & strPathM &
strProductSerial & "*", vbDirectory)
' If strFolder <> "" Then
' Shell Chr(34) & "EXPLORER.EXE" & Chr(34) & " "
& Chr(34) & strPathF & strProdType & strPathM & strFolder & Chr(34),
vbNormalFocus
' Else
MsgBox "Folder does not exist", vbOKOnly, "No
Folder"
' End If
' End If
End If
End If
End If
End If
End Function

some of it is remmed in prep for my next section.

there are some situations where the serial number and the workorder number
wont work because one or the other are not at the begining of the folder
name. what i want to do is create one more section that tests for both the
workorder number and then the serial within the folder name. that is why i
have remmed out sections cause i want to use those as the spots for the new
code.

the question i have is 2 fold. is this going to hit the nested limit? and
can i seperate out the if statements and just have each condition tested
seperately with out nesting?

 

[DawnTreader]

Hello

i got it to work. just put in another set of if thens with a star in front
of and behind the strFolder variable. works like a charm now.

thanks again for all your help Ken.

 

[Ken Snell \(MVP\)]

You're welcome!
--

Ken Snell
<MS ACCESS MVP>

Hello

i got it to work. just put in another set of if thens with a star in front
of and behind the strFolder variable. works like a charm now.

thanks again for all your help Ken.