Trimming Text File Names in VBA

[VbAaron]

Hey All,

Had a look through the forums and didn't find any mention of my specific issue.

Here goes,

I have a series of files that are being transferred across an FTP server. The file naming follows a predetermined format that was agreed upon by both parties on either side of the transfer. However, during the file transfer process, there is extra text that is added to the name of the file. How do I go about removing the 'extra text' from the file name to only have the predetermined format? IT has deemed that extra text necessary to ensure unique files are created in the destination folder, thus it cannot be removed by them.

Example of naming convention: LOWPOSITION17021601.csv - the numbers are a date plus a series number: yymmdd-01 (for future files of the same type received on the same day, the next file would have the ascending -02- series number)

The transfer process adds: JIM_JON_LOWPOSITION17021601.csv_20171216070405

I would like to remove the 'JIM_JON_' and '_20171216070405' prefix and suffix before importing the files (which have different information in each) to their respective tables. 'JIM_JON_' prefix will always be the same,for the suffix, I can identify that there is a date, the others numbers I cannot source. I will ask IT how they come up with that value.

Any ideas on where to start?

 

[MarkK]

Check out the VBA.Split() function, which breaks a string into an array at a character you specify, so the text you want to extract can be returned using...

? Split("JIM_JON_LOWPOSITION17021601.csv_20171216070405", "_")(2)

So that syntax looks a little funny, but the function is returning an array, and we are referencing the 3rd element of the zero-based array.

 

[VbAaron]

Thank you for this Mark!

My next question then is a tough one to ask due to my limited understanding of this process, so bear with me. Essentially the array is a result of that function you recommended I use, does this mean that the output array can be used to identify the table that file (used in the original function) needs to be imported into? Is the array another way of referencing the file for import? Could I use this as a way to rename the file?

 

[MarkK]

No, the Split() function takes a string and splits it into multiple strings as delimited by a character you choose--or a space by default. The function knows nothing about files or tables and cannot be used to change a file name, although, you might use Split() to modify a string, and then use that string to name or rename a file. Makes sense?

For code that may expose the Split() function more clearly, consider...

Private Sub Test174692869471()
   Dim vArray
   Dim var

   vArray = Split("JIM_JON_LOWPOSITION17021601.csv_20171216070405", "_")
   For Each var In vArray
      Debug.Print var
   Next
End Sub

Or more simply...

Private Sub Test174692869472()
   Dim var

   For Each var In Split("JIM_JON_LOWPOSITION17021601.csv_20171216070405", "_")
      Debug.Print var
   Next
End Sub

...so all it does is returns an array of strings from a string.

 

[VbAaron]

What you're saying makes sense.

My idea then is to code:

For every file that contains the 'extra text' in a folder, run the split() script for those file names and rename based on the output array.

Can I assign a variable to that output array to call it into a name loop?


Again thank you for your patience and time

 

[MarkK]

Hey there, I'm not sure what this means...

Can I assign a variable to that output array to call it into a name loop?

You can definitely assign the array to a variable, as I do in the code I posted in #4. In the first example I've declared a variant called vArray, to which I assign the result of the Split().
An alternative way to see what is in that array would be...

Private Sub Test174692869471()
   Dim vArray
   Dim i as integer

   vArray = Split("JIM_JON_LOWPOSITION17021601.csv_20171216070405", "_")
   For i = 0 to ubound(vArray)
      Debug.Print vArray(i) [COLOR="Green"]'here we reference the array element using a subscript[/COLOR]
   Next
End Sub

...so we can reference the elements just using the subscript...

   vArray = Split("JIM_JON_LOWPOSITION17021601.csv_20171216070405", "_")
   debug.print vArray(2)

...so we could write a function like...

Function GetEmbeddedFileName(OldName as string) as string
   Dim vArray
   vArray = Split(OldName, "_")
   GetEmbeddedFileName = vArray(2)
End Function

See what that does? The problem in my mind is: Is the substring you want ALWAYS going to be the third element? Or, is there a file out there called...

JIM_JON_JAK_HiPos17021701.csv_19346823

See how that defeats our function by moving the important bit to the fourth array element? That is the hard part about processing strings. You need to know all the possible structures of the strings that will be presented to you, and then write code to handle those variations.

Hope this helps,

 

[static]

"'JIM_JON_' prefix will always be the same"

so

FileName = Split(filename, "_")(2)

 

[VbAaron]

"'JIM_JON_' prefix will always be the same"

so

FileName = Split(filename, "_")(2)

This will always be the case yes.

 

[VbAaron]

Hey Mark,

Thanks for everything that you have provided! I am using the following code to successfully rename the files in my folder:

Function rename()

Const fdr As String = "C:\Users\afharris\Documents\_a_ RONA Conversion\New folder\"
Dim oldnm As String, newnm As String

oldnm = Dir(fdr & "*Rona_Lowes_*")
Do While oldnm <> ""
'deduce new fn from old fn
    newnm = Split(oldnm, "_")(2)
'rename the file, will move instead if you specify two different fdrs
    Name fdr & oldnm As fdr & newnm
    oldnm = Dir
Loop

End Function

Just curious on your thoughts on the chosen method above. Thanks again!

 

[MarkK]

You bet Aaron. That looks totally great. I was not even aware that there was a command called "Name" that would rename files, so you taught me something today. Nice work.

 

[VbAaron]

You bet Aaron. That looks totally great. I was not even aware that there was a command called "Name" that would rename files, so you taught me something today. Nice work.

Thanks again for all your help!

 

[The_Doc_Man]

MarkK, the full syntax is Name x As y for the rename. It also works correctly if x and y are longer path+file specifications and the longer path contains the shorter one. I.e. a cross-directory rename.

 

[MarkK]

Yeah, thanks Doc Man, I would have used a FileSystemObject for that. Cool to know.

 

[The_Doc_Man]

Actually, that is a holdover from the original BASIC language (Beginners All-purpose Symbolic Instruction Code). That has been legit syntax since the 1960s. Takes an old fart like me to remember it, though!