0

I am trying to write a function in my code that allows me to search for a specific file based on it's name. The function is working but now the structure of the name has changed and it has caused trouble for me.

My code is in the form of macros that control a word document. This document gets information from another program and puts it in formfields on the word document. My code then takes the "value" of the formfields and uses them to search a specific folder for a .txt with a name that matches the stored value from the formfields.

Now the name of the .txt has been altered, the name will have some extra information that i do not have access to through the word document.

Example: W_X_Y_Z.txt Where Y and Z are available in the formfields of the document and can be accessed, but W and X are unknown to me.

Need to solve: So what i need to do is to figure out a way to ignore W and X and base the search on Y and Z only.

Ideas: One idea i have is to search for a file with the structre such as ??_??_Y_Z.txt but i am uncertain how this will work as in does "??" include numbers if it is in a string and does "??" mean only 2 unknown characters or any amount? If only two is there a way to define an unknown string where both the characters are unknown and the amount of characters?

Second idea is to use a delimiter on the _ and somehow figure out how i can tell the macro how to ignore everything after the second _.

Any ideas or recommended investigations i should make are welcome.

Thank you.

I will try to add some code now, but keep in mind i inheritated alot of the code in this document and therefore i do not know exactly what every detail does thats why i have avoided typing the code.

Code as of now:

ActiveDocument.FormFields("Remissnummer").Select
Remiss = Selection.Text
Selection.Collapse Direction:=wdCollapseStart

ActiveDocument.FormFields("Personid").Select
strTmpPersonnr = Selection.Text
intNumCharsPersNr = Len(strTmpPersonnr)
Selection.Collapse Direction:=wdCollapseStart

strFileName = strTmpPersonnr
p_strFileReportPath = "G:\HMC\Fysiologiska kliniken\Eko\Export\"
strImportFile = p_strFileReportPath & "*" & strFileName & "_" & Remiss & strFileExt

If Len(Dir$(strImportFile)) = 0 Then
MsgBox "Error"
Else
'starts importing values from the file

This method has worked when the file name had a know structure i.e only Y_Z.txt Now though the structure has changed as i explained above, the two new variables are unknown, can be anything from letters to numbers.

i tried with:

Ignore = "*"

strImportFile = p_strFileReportPath & Ignore & "_" & Ignore & "_" & strFileName & "_" & Remiss & strFileExt

but it just froze and when going to task manager you could see more and more memory was allocated to word... I stoped it after 10 minutes.

So im guessing i cant account for the unknown parts of the file name with "*" but im kinda lost now.

Grahagblah
  • 1
  • 1
  • 2
    Always helps to post your existing code... – Tim Williams Jun 06 '18 at 05:27
  • I know, im sorry, i thought the way i explained it was enough since the problem is not much coding based but lack of knowledge of the VBA functions, since the import is working the only thing that was missing was a way to find something that you dont know the complete name of. – Grahagblah Jun 06 '18 at 07:35
  • 1
    It's much easier to tweak a line of existing code than to try to make suggestions when we have no idea how your code is structured. Posting your code also gives people here some idea about what level to pitch their response at. – Tim Williams Jun 06 '18 at 17:10

2 Answers2

1

AFAIK there are two methods you can use in VBA to iterate over files:

If you are using the FileSystemObject, then you iterate over each file and verify the file name, using any of the followi\ng:

  • VBA string functions, such as Instr and Left
  • VBA Like operator, comparing against a pattern-match string
  • regular expressions

If you are using Dir (calling Dir each time without parameters to get the next file name), then in addition to the previous methods, you can pass in a filename pattern to the function2, and it will only return results matching the pattern.

Whether you use the Like operator or you pass in a pattern to the Dir function, ? in the pattern matches any single character, while * matches any number of characters:

  • "ab?d" matches "abcd", "abgd", but not "abcgd"
  • "ab*d" matches "abcd", "abgd", and "abcgd"

For more information about using regular expressions in VBA, see here.

1. On Windows only, because Office on Macintosh doesn't support the use of ActiveX / COM Automation.
2. On Windows only. On Macintosh, ? and * are valid filename characters, and therefore can't be used for wildcard characters.

Zev Spitz
  • 13,950
  • 6
  • 64
  • 136
  • Ahh ok, so i can use "*" and define 2 fake variables which are equal to * and then search for fake_fake_Y_Z.txt? Where fake = * – Grahagblah Jun 06 '18 at 06:52
  • @Grahagblah I'm guessing that you can search for the string expression `"*_" + Y + "_" + Z + ".txt"`, where `Y` and `Z` contain the values from the form fields. But without seeing some code, it's hard to say for certain. – Zev Spitz Jun 06 '18 at 07:32
  • I will try this and see if it works. Thank you for the help and sorry for the nature of the question. – Grahagblah Jun 06 '18 at 07:44
  • Just explaining @Zev's comment: you can compile the search pattern and test if Windows Explorer lists only the desired files. Once you have the proper format, you can go on with programming. I prefer `Dir` to `FileSystemObject` and `RegEx` (as they are way more complex), see an example here: https://stackoverflow.com/questions/50275517/using-vba-to-import-multiple-text-files-with-different-delimiters/50289246#50289246 – AcsErno Jun 06 '18 at 11:57
  • @AcsErno It's possible to do what you suggest, but the meaning of my comment was only that this string format could be used either with the VBA `Like` operator, or passed into the `Dir` function. – Zev Spitz Jun 06 '18 at 12:03
  • @Zev, what you said was absolutely correct. I just wanted to show Grahagblah an easy way to go on. – AcsErno Jun 06 '18 at 12:39
  • If i make 2 string variables: rndm = "*" rndm2 = "hey" If rndm = rndm2 Then MsgBox "Working" Else MsgBox "Not Working" This is not working, so im wondering how can i make it work? Do i have to give it point marks as in something that is similar in both cases? – Grahagblah Jun 07 '18 at 01:28
  • @Grahagblah `If rndm2 Like rndm Then MsgBox "Working" Else MsgBox "Not Working"` should work. – Zev Spitz Jun 07 '18 at 01:58
  • So now it will find W_X_Y_Z if i give tell it to search for strImportFile Where strImportFile = rndm_rndm_Y_Z. The only problem i can see which i can not test until im in the office is that the code searches for a string that is = to and not Like, not sure if "*" will work then. The search uses If Len(Dir$(strImportFile)) = 1 Then 'starts copying values from the file. – Grahagblah Jun 07 '18 at 02:49
  • @Grahagblah In order to understand how the `Like` operator works, I suggest you don't think about variables (fake, random or otherwise); think about the results which actual strings will return -- `Debug.Print "*" Like "hey"` prints `False`; `Debug.Print "hey" Like "*"` prints `True` etc. This is also a style of code you can paste into the Immediate pane, and see the results immediately. – Zev Spitz Jun 07 '18 at 11:48
  • Another possible advantage to VBA's DIR vs FileSystemObject is that the former will work on Macs, the latter won't. If you need to support Macs, wildcards won't work, if I remember correctly. – Steve Rindsberg Jun 07 '18 at 19:20
  • @SteveRindsberg Thanks; I've incorporated into my answer. – Zev Spitz Jun 07 '18 at 19:50
  • You can also issue a command line loop but that is perhaps stretching things. – QHarr Jun 07 '18 at 20:46
  • I have updated the question and added some code now to clarify the problem a bit, i hope it makes it easier to understand what i mean now. – Grahagblah Jun 08 '18 at 01:27
0

Looks like you could use RegEx. Just gooogle RegEx VBA and get familiar with the concept. With it you can create a lookup query for any regular pattern.

here you can see some examples on StackOverflow

SebastianG
  • 26
  • 1