Solved Open a specific folder using the first x letters

[dim]

Hi,

I need to open from a form a specific folder where will match only the first 3 letters.
Example:
On the form I have a field named [CompanyName] and in this example [CompanyName]="ABC Customer"
On the server, the folder name for this Customer is only ABC (without Customer)
To open this folder I tried this, but doesn't work:

strFoldername = Left(Me.CompanyName, 3)
If Dir("\\datasrv\serverdata\Customer's Documents\PO\" & strFoldername, vbDirectory) = "" Then
MsgBox "Missing folder with this Company name"
Else
FollowHyperlink "\\datasrv\serverdata\Customer's Documents\PO\" & strFoldername & "*"
End If

Can you help please?

Thanks

 

[Minty]

Do you get an error or what does "Doesn't work" actually mean?

 

[dim]

Presently, don't find any folder which start with ABC, so appear the message "Missing folder with this Company name"

 

[Minty]

Okay so try hard coding it in the immediate window: Type

? Dir("\\datasrv\serverdata\Customer's Documents\PO\ABC" , vbDirectory)

And see what it gives you.
Then try

? Dir("\\datasrv\serverdata\Customer's Documents\PO\ABC\" , vbDirectory)

 

[dim]

I tried and I I get a runtime error 490 "Cannot open the specified file"

strFoldername = Left(Me.CompanyName, 3)
If Dir("\\datasrv\serverdata\Customer's Documents\PO\ABC\", vbDirectory) = "" Then
MsgBox "Missing folder with this Company name or rename it to match the exactlly full name"
Else
FollowHyperlink "\\datasrv\serverdata\Customer's Documents\PO\" & strFoldername & "*"
End If

 

[Minty]

Did you try in the immediate window - exactly the two things I typed.
They are different. One has a trailing slash one doesn't.

 

[sxschech]

Are you looking to open a folder dialog and see/select the files that it lists?

 

[dim]

Sorry, I don't understand. You mind to put this code:
? Dir("\\datasrv\serverdata\Customer's Documents\PO\ABC" , vbDirectory)
under the click event of my button?
I tried anyway, but the question mark (?) is not recognized by vba and will be converted to Print
Can you explain please?

 

[Minty]

In the immediate window (open the VBA editor and press ctrl + G together) type the two lines I suggested, then press enter after each one.

? Dir("\\datasrv\serverdata\Customer's Documents\PO\ABC" , vbDirectory)
? Dir("\\datasrv\serverdata\Customer's Documents\PO\ABC\" , vbDirectory)

What results do you get?

 

[dim]

This is what I get:

? Dir("\\datasrv\serverdata\Customer's Documents\PO\ABC" , vbDirectory)
ABC
? Dir("\\datasrv\serverdata\Customer's Documents\PO\ABC\" , vbDirectory)
.

 

[Gasman]

You do not use * just use

FollowHyperlink "\\datasrv\serverdata\Customer's Documents\PO\" & strFoldername

 

[Minty]

As @Gasman said just open the directory - I missed the * on the end.
That won't work.

 

[dim]

Ok, so I tried as you said, but I get a runtime error 490 "Cannot open the specified file"

Private Sub Command361_Click()

strFoldername = Left(Me.CompanyName, 3)
FollowHyperlink "\\datasrv\serverdata\Customer's Documents\PO\" & strFoldername

End Sub

 

[Gasman]

Well test what strFolderName is?

 

[Minty]

Use the immediate window to test the correct syntax
Simply type

FollowHyperlink "\\datasrv\serverdata\Customer's Documents\PO\ABC"

And hit return.
The immediate window can save you hours of messing around in code!

 

[Gasman]

Use the immediate window to test the correct syntax
Simply type

FollowHyperlink "\\datasrv\serverdata\Customer's Documents\PO\ABC"

And hit return.
The immediate window can save you hours of messing around in code!

I just tested just that and it works fine.?

tt="DB"
FollowHyperlink "c:\temp\" & tt

providing of course the data is correct?

 

[Minty]

@Gasman Of course!

According to the OP's report of the Dir() command above it was returning something.

So maybe something else is up.
I'm not a fan of the directory naming with an apostrophe in it. ( \Customer's )

 

[Gasman]

Nor I, but I just tried this

tt="DB"
FollowHyperlink "c:\temp\Customer's\" & tt

and that works fine as well?

 

[dim]

Let's take another example:
If the name of the company is TERRA LAND and the folder name is TERRA, this code doesn't work:

strFoldername = Left(Me.CompanyName, 3)
FollowHyperlink "\\datasrv\serverdata\Customer's Documents\PO\" & strFoldername

If I use the first 5 letters of the entire word Terra is working, but I need to use only the first 3 letters (TER)
For this I tried too:

strFoldername = Left(Me.CompanyName, 3)
FollowHyperlink "\\datasrv\serverdata\Customer's Documents\PO\" & strFoldername & "*"

but doesn't work.

Any others idea?

 

[Gasman]

Yes, try googling, now you are giving the full picture

https://stackoverflow.com/questions/35689951/how-to-get-folder-path-using-partial-folder-name

From https://www.google.com/search?q=fin...ome..69i57.18437j0j7&sourceid=chrome&ie=UTF-8

Use the Dir() to get the correct name then hyperlink.

Not sure what you are going to do when the company TERMINATOR or a similar name comes on board?