Using wildcards in the middle of a file location (1 Viewer)

TobyMace

Registered User.
Local time
Today, 10:04
Joined
Apr 13, 2018
Messages
65
Hi All,

I am trying to open a workbook that will be in a location that is dependent on what the user defines via input boxes:

Code:
Dim assy As String
Dim job As String

assy = InputBox("Please enter assembly number in the format Dxxxx-xxx", "Assembly Number")
' i.e. D2270-502

job = InputBox("Please enter job number (6 digits)", "Works Order Number")
' i.e. 733802

If Len(assy) = 9 Or Len(job) = 6 Then

Dim lassy As String
Dim ljob As String
Dim excelloc As String

lassy = Mid(assy, 2, 4)
' i.e. 2270

ljob = "WO" & Left(job, 4)
' i.e. WO7338

excelloc = Dir("F:\CELAB\EVERYONE\Toby\DSL Database\SP" & lassy & "*" & "\DWGSET\" & ljob & ".xls")
' i.e. F:\CELAB\EVERYONE\Toby\DSL Database\SP2270 (GEW Dual Panda)\DWGSET\WO7338.xls

DoCmd.TransferSpreadsheet acImport, , "DSL_T", excelloc

End If

Now the user defined items are used later, that's why they are formatted as such, but we can use information as part of that to find the location. The hiccup I have is after the "SP2270" is an additional name, in this case "(GEW Dual Panda)". After doing various searches online for various solutions none seem to be working for me. I am probably mis-interpreting it so I was hoping someone might have a fresh new way of looking at it.
No I could define each additional file name. For example I know that "(GEW Dual Panda)" will appear if my variable "lassy" = 2270. However "lassy" could be quite a number of possibilities and to manually code all corresponding file names would be very time consuming and any additional files wouldn't need to be added to the code. If this is what I have to be then so be it but I'm hoping there is an alternative method.

Many thanks in advance to any and all help.
And apologies if the above makes no sense...
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:04
Joined
Oct 29, 2018
Messages
21,358
Hi,

How about rather than asking the user for a partial filename input, use a FileDialog Object instead to have the user select the file they want to import? If you use a wildcard to match the partial filename, how will you know which one to import if there were more than one file that matched the input?
 

Minty

AWF VIP
Local time
Today, 10:04
Joined
Jul 26, 2013
Messages
10,355
If you know what the associated values are, why not put that information in a table, and simply look that up? Or present it in a combo to remove user input error?

You then wouldn't be hard coding the values and could simply have a form to add new ones as required.
 

TobyMace

Registered User.
Local time
Today, 10:04
Joined
Apr 13, 2018
Messages
65
Hi theDBguy,

The user isn't actually defining the file name they are defining an assembly number and job number. But we are able to find the location of these files using this information. We will use the values they defined for another section of code. The information they define will lead to only one file I can guarantee that. I am trying to avoid them having to search for their own file and find the relevant information within the spreadsheet as it can be quite time consuming. If it can be done automatically for them then great!

Many thanks.
 

TobyMace

Registered User.
Local time
Today, 10:04
Joined
Apr 13, 2018
Messages
65
Hi Minty,

This was a thought of mine but the number of files are in the hundreds so would be quite time consuming. I know sometimes such tasks have to be done when creating a database but I'm always looking for the quicker but just as effective solution. And god forbid I ever have to do something similar with another database then I'd like to have a solution to hand.

Many thanks.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:04
Joined
Oct 29, 2018
Messages
21,358
Hi theDBguy,

The user isn't actually defining the file name they are defining an assembly number and job number. But we are able to find the location of these files using this information. We will use the values they defined for another section of code. The information they define will lead to only one file I can guarantee that. I am trying to avoid them having to search for their own file and find the relevant information within the spreadsheet as it can be quite time consuming. If it can be done automatically for them then great!

Many thanks.
Ah, okay. However, doing something like:


var = Dir("C:\FolderName\SubfolderName\Filename.ext")


will result in:


var = Filename.ext


which I doubt is what you want if you're using it for the TransferSpreadsheet method.


So, I might suggest you try it this way:


Code:
excelloc = Dir("F:\CELAB\EVERYONE\Toby\DSL Database\SP" & lassy & "*", vbDirectory)
excelloc = excelloc & "\DWGSET\" & ljob & ".xls"
Hope it helps...
 

TobyMace

Registered User.
Local time
Today, 10:04
Joined
Apr 13, 2018
Messages
65
Thanks for your reply.
I have tried your method however it doesn't appear to take the wildcard into account. It has "SP2270\DWGSET". And what is even stranger is that it's not giving back the location in the F:\ drive but instead giving back my documents folder in the local C:\ drive...
I 100% have the desired location in the code.
 

TobyMace

Registered User.
Local time
Today, 10:04
Joined
Apr 13, 2018
Messages
65
After fiddling around with it it seems as though it isn't picking up anything before the SP2270 and is defaulting to this new local location.
I set up a textbox that shows the path just for fault finding purposes and it's only returning "SP2270" onwards...
 

Minty

AWF VIP
Local time
Today, 10:04
Joined
Jul 26, 2013
Messages
10,355
With regard to you listing all the files in a table, you can do that in VBA.
Have a look here http://allenbrowne.com/ser-59.html and see if it might assist in this project.

You could perform some querying on the data in the file list table to give you the specific file name paths / part of file names you wanted.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:04
Joined
Oct 29, 2018
Messages
21,358
Thanks for your reply.
I have tried your method however it doesn't appear to take the wildcard into account. It has "SP2270\DWGSET". And what is even stranger is that it's not giving back the location in the F:\ drive but instead giving back my documents folder in the local C:\ drive...
I 100% have the desired location in the code.

Hi,

Not that we have any reason to doubt you but to make sure we are on the same page, could you please post how exactly you tried my suggestion? When I enter the following in the Immediate Window:

?Dir(“c:\pr*”, vbDirectory)

I get C:\Program Files
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:04
Joined
Oct 29, 2018
Messages
21,358
Hmm, I wonder if it’s an OS thing. I tried the above at home where I’m using Win7, but now I’m at work using Win10 and only getting the folder name instead of the full path. To be safe, maybe you can do it this eay:

excelloc=Dir(“F:\...\SP” & lassy & “*”, vbDirectory)
excelloc=“F:\...\” & excelloc & “\...\” & ljob & “.xls”

Sent from phone...
 

TobyMace

Registered User.
Local time
Today, 10:04
Joined
Apr 13, 2018
Messages
65
Hmm, I wonder if it’s an OS thing. I tried the above at home where I’m using Win7, but now I’m at work using Win10 and only getting the folder name instead of the full path. To be safe, maybe you can do it this eay:

excelloc=Dir(“F:\...\SP” & lassy & “*”, vbDirectory)
excelloc=“F:\...\” & excelloc & “\...\” & ljob & “.xls”

Sent from phone...

Sorry for the late reply! Not been able to get back to this until now.

I have tried this:
Code:
excelloc = Dir("F:\CELAB\EVERYONE\Toby\DSL Database\SP" & lassy & "*", vbDirectory)

excellocs = "F:\CELAB\EVERYONE\Toby\DSL Database\" & excelloc & "\DWGSET\" & ljob & ".xls"

But it still isn't taking the wildcard into account. I'm using windows 7 here so should be OK as you say. I'm just waiting for you to point out something really obvious I've missed because that's usually what I do... haha.

Many Thanks
 

TobyMace

Registered User.
Local time
Today, 10:04
Joined
Apr 13, 2018
Messages
65
With regard to you listing all the files in a table, you can do that in VBA.
Have a look here http://allenbrowne.com/ser-59.html and see if it might assist in this project.

You could perform some querying on the data in the file list table to give you the specific file name paths / part of file names you wanted.

Hi Minty,

I love this! Looks very useful for the future. Unfortunately I can't get it to work for just folder names. It seems to want to limit it to files as you have to define the file type ".xls" or ".doc" for example. I've had a play around with it but with no success. I'll keep trying as it does look promising!

Many thanks.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:04
Joined
Oct 29, 2018
Messages
21,358
Sorry for the late reply! Not been able to get back to this until now.

I have tried this:
Code:
excelloc = Dir("F:\CELAB\EVERYONE\Toby\DSL Database\SP" & lassy & "*", vbDirectory)

excellocs = "F:\CELAB\EVERYONE\Toby\DSL Database\" & excelloc & "\DWGSET\" & ljob & ".xls"
But it still isn't taking the wildcard into account. I'm using windows 7 here so should be OK as you say. I'm just waiting for you to point out something really obvious I've missed because that's usually what I do... haha.

Many Thanks

Hi,

Have you tried stepping through your code to examine what the variable contains at each step? I'm at work now and have the following file (as an example): E:\CNAP\NCANALYSIS_DATA\CVN Deployment Reqs\NIMITZ (DEPLOYMENT)_05Jun17-05Jun20.xlsx So, as a test, I entered the following lines in the Immediate Window and got the result shown:
Code:
tempvars("fldr") = "E:\CNAP\" & dir("e:\cnap\nca*_data",vbDirectory) 
tempvars("fldr") = tempvars("fldr") & "\" & dir(tempvars("fldr") & "\cv*",vbDirectory) 
tempvars("fldr") = tempvars("fldr") & "\" & dir(tempvars("fldr") & "\ni*.xlsx") 
?tempvars("fldr") 
E:\CNAP\NCANALYSIS_DATA\CVN Deployment Reqs\NIMITZ (DEPLOYMENT)_05Jun17-05Jun20.xlsx
I also have my own version of a demo for listing files in a folder at the following link: http://www.accessmvp.com/thedbguy/demos/listfiles.asp You probably won't need it, but just thought I'd offer it anyway. Cheers!
 
Last edited:

Users who are viewing this thread

Top Bottom