Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-15-2018, 08:13 AM   #1
TobyMace
Newly Registered User
 
Join Date: Apr 2018
Posts: 55
Thanks: 26
Thanked 1 Time in 1 Post
TobyMace is on a distinguished road
Using wildcards in the middle of a file location

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...

TobyMace is offline   Reply With Quote
Old 11-15-2018, 08:18 AM   #2
theDBguy
Newly Registered User
 
theDBguy's Avatar
 
Join Date: Oct 2018
Posts: 212
Thanks: 3
Thanked 33 Times in 32 Posts
theDBguy is on a distinguished road
Re: Using wildcards in the middle of a file location

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?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 11-15-2018, 08:32 AM   #3
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,798
Thanks: 140
Thanked 1,565 Times in 1,537 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Using wildcards in the middle of a file location

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.

__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 11-15-2018, 08:55 AM   #4
TobyMace
Newly Registered User
 
Join Date: Apr 2018
Posts: 55
Thanks: 26
Thanked 1 Time in 1 Post
TobyMace is on a distinguished road
Re: Using wildcards in the middle of a file location

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 is offline   Reply With Quote
Old 11-15-2018, 09:01 AM   #5
TobyMace
Newly Registered User
 
Join Date: Apr 2018
Posts: 55
Thanks: 26
Thanked 1 Time in 1 Post
TobyMace is on a distinguished road
Re: Using wildcards in the middle of a file location

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.
TobyMace is offline   Reply With Quote
Old 11-15-2018, 07:38 PM   #6
theDBguy
Newly Registered User
 
theDBguy's Avatar
 
Join Date: Oct 2018
Posts: 212
Thanks: 3
Thanked 33 Times in 32 Posts
theDBguy is on a distinguished road
Re: Using wildcards in the middle of a file location

Quote:
Originally Posted by TobyMace View Post
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...
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 11-16-2018, 12:44 AM   #7
TobyMace
Newly Registered User
 
Join Date: Apr 2018
Posts: 55
Thanks: 26
Thanked 1 Time in 1 Post
TobyMace is on a distinguished road
Re: Using wildcards in the middle of a file location

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 is offline   Reply With Quote
Old 11-16-2018, 01:30 AM   #8
TobyMace
Newly Registered User
 
Join Date: Apr 2018
Posts: 55
Thanks: 26
Thanked 1 Time in 1 Post
TobyMace is on a distinguished road
Re: Using wildcards in the middle of a file location

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...
TobyMace is offline   Reply With Quote
Old 11-16-2018, 01:34 AM   #9
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,798
Thanks: 140
Thanked 1,565 Times in 1,537 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Using wildcards in the middle of a file location

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.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 11-16-2018, 08:32 AM   #10
theDBguy
Newly Registered User
 
theDBguy's Avatar
 
Join Date: Oct 2018
Posts: 212
Thanks: 3
Thanked 33 Times in 32 Posts
theDBguy is on a distinguished road
Re: Using wildcards in the middle of a file location

Quote:
Originally Posted by TobyMace View Post
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
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 11-16-2018, 09:28 AM   #11
theDBguy
Newly Registered User
 
theDBguy's Avatar
 
Join Date: Oct 2018
Posts: 212
Thanks: 3
Thanked 33 Times in 32 Posts
theDBguy is on a distinguished road
Re: Using wildcards in the middle of a file location

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...
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 11-20-2018, 07:27 AM   #12
TobyMace
Newly Registered User
 
Join Date: Apr 2018
Posts: 55
Thanks: 26
Thanked 1 Time in 1 Post
TobyMace is on a distinguished road
Re: Using wildcards in the middle of a file location

Quote:
Originally Posted by theDBguy View Post
Hmm, I wonder if its an OS thing. I tried the above at home where Im using Win7, but now Im 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 is offline   Reply With Quote
Old 11-20-2018, 07:29 AM   #13
TobyMace
Newly Registered User
 
Join Date: Apr 2018
Posts: 55
Thanks: 26
Thanked 1 Time in 1 Post
TobyMace is on a distinguished road
Re: Using wildcards in the middle of a file location

Quote:
Originally Posted by Minty View Post
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.
TobyMace is offline   Reply With Quote
Old 11-20-2018, 08:39 AM   #14
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,798
Thanks: 140
Thanked 1,565 Times in 1,537 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Using wildcards in the middle of a file location

Have a look here for a way to list folders and sub folders only
https://www.devhut.net/2016/07/06/ms...-into-a-table/
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 11-20-2018, 08:58 AM   #15
theDBguy
Newly Registered User
 
theDBguy's Avatar
 
Join Date: Oct 2018
Posts: 212
Thanks: 3
Thanked 33 Times in 32 Posts
theDBguy is on a distinguished road
Re: Using wildcards in the middle of a file location

Quote:
Originally Posted by TobyMace View Post
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!

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by theDBguy; 11-20-2018 at 09:13 AM.
theDBguy is online now   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Matching File Paths with WildCards nstratton Modules & VBA 4 09-16-2015 05:06 AM
storing a file location and opening the file from a form roosn Forms 4 12-09-2014 08:33 AM
How to Convert middle names to middle initials bvbrandon Tables 2 12-13-2012 12:17 PM
Wildcards in OLE Linked Object File Name chaddiesel Tables 0 05-31-2005 02:35 PM
file import and wildcards jon92 Tables 23 09-22-2004 09:24 AM




All times are GMT -8. The time now is 12:15 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World