none
Get value from the File name in Word VBA RRS feed

  • Question

  • Everyone,

      I would like to get a field from the folder name of the file, so it can be used in the output filename.  When I go to rename/move the file, then it will be removed.  The folder name structure is as follows:

    C:\Cloud Data\Testing\Acura\Acura of Orange Park\1st Response Retail\PDF_Files

    A.  I need to get the string from FDlr

         A. FDisk = C:\,  FCld = Cloud Data\,  FRegion = Testing\,  FMfgr = Acura\, FDlr = Acura of Orange Park, FEcatg = 1st Rep..

    B.  Then I want to use FDlr in my output file name as follows:

    OutputFileName:= strDirectory & i & " " & Cur_Month & " " & DokName & " ." & "FDlr" & ".pdf"

    strDirectory is going to the set at: C:\Cloud Data\Testing\PDF_FIles\

    The output file name result will be as follows:

    9 Apr 2018 Acura TLX   SH-AWD  w Technology Package Sedan.Acura of Orange Park.pdf

      I have all the over values, just do not know how to grab the FDlr value from the folder name.  Can someone plz dirct me on how to get this value?

    Jerry

    • Edited by ballj_351 Sunday, April 29, 2018 12:46 PM Needed to update
    Sunday, April 29, 2018 12:36 PM

Answers

  • You should be able to parse a path using the Split Function with the backslash "\" as the delimiter.

    https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/split-function

    So if the path to parse was C:\Cloud Data\Testing\Acura\Acura of Orange Park\1st Response Retail\PDF_Files

    then the 0 based string array returned by split would contain -

    C:
    Cloud Data
    Testing
    Acura
    Acura of Orange Park
    1st Response Retail
    PDF_Files

    where the Acura of Orange Park is element 4

    • Edited by RLWA32 Sunday, April 29, 2018 1:56 PM added info
    • Marked as answer by ballj_351 Sunday, April 29, 2018 5:22 PM
    Sunday, April 29, 2018 1:38 PM

All replies

  • You should be able to parse a path using the Split Function with the backslash "\" as the delimiter.

    https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/split-function

    So if the path to parse was C:\Cloud Data\Testing\Acura\Acura of Orange Park\1st Response Retail\PDF_Files

    then the 0 based string array returned by split would contain -

    C:
    Cloud Data
    Testing
    Acura
    Acura of Orange Park
    1st Response Retail
    PDF_Files

    where the Acura of Orange Park is element 4

    • Edited by RLWA32 Sunday, April 29, 2018 1:56 PM added info
    • Marked as answer by ballj_351 Sunday, April 29, 2018 5:22 PM
    Sunday, April 29, 2018 1:38 PM
  • Works like a charm.  Thank you so very much!  I also need to write a batch script that will move the files to another dropbox folder.  What command could I use in MS-DOS for when I write the batch script?  I would like to take the filename, extract it into different fields, then use the fields to format to the output folder & name.  I look up some commands, but I am sure someone has done this before,  Any suggestions would be much appreciated.

    Sunday, April 29, 2018 5:25 PM
  • Why do you need a batch file?  You can move one or more files from one location to another with https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/movefile-method
    Sunday, April 29, 2018 5:32 PM
  • What happens is as follows:

    1.  We create pdf files from a Word macro and I needed to save the name in a format, where I can parse it to a destination folder, based on information in the file folder.  The names need to be a structured format as I will have many of them to move.

    2.  The I use a product to convert the pdf files to jpg images.  These images will be for many different manufacturers & dealers.

    3.  Once that product completes with converting all the files, then I will have a batch jobs that comes along and gets all the files in that folder.  For each file, parse the filename out into vars.  Then take the vars and construct the Dropbox folder name/structure where the images will reside for each of them.  It may be 1 image w/in a folder or I may have 20 images that go to the same folder.  And the folders will vary based on the filenames.  Yet, I need to parse each of them and then rename them to the destination folder.  Also, if any issues, write to a log file for errors.  This is why I am using a batch file for this.

      If there is a better and more efficient way of doing this, believe me, I am open to all ideas and input.  Thanx in advance.

    Jerry

    Sunday, April 29, 2018 5:42 PM
  • I suggest you look into using VBScript and the Scripting.FileSystemObject.  I think that would meet your needs better than the batch commands available in a Windows command prompt window.
    • Edited by RLWA32 Sunday, April 29, 2018 5:51 PM
    Sunday, April 29, 2018 5:49 PM
  • I had thought of that, but do not know what t make the file, so that the user can click on it and it runs w/o user interface.  With the *.bat file, I can have it run w/o a UI, but if I used VBScript, what could I code it in, and use as the file type, so it executes when clicked on?
    Sunday, April 29, 2018 5:52 PM
  • VBScript files have a .vbs file extension.  There is no user interface unless your script has an error or you provide user interaction in your script.  For example, double clicking the following simple VBScript file would make a copy of a file by a different name. -

    Dim fs
    
    set fs = CreateObject("Scripting.FileSystemObject")
    
    fs.CopyFile "aa.txt", "bb.txt"
    
    set fs = Nothing
    

    Sunday, April 29, 2018 6:11 PM
  • How do I loop through the folder and ensure I get all the filed I need to be renamed.  These would be the *.jpg files in this folder.  Thanx so much, I appreciate your guidance an dknowlwedge
    Sunday, April 29, 2018 7:07 PM
  • How do I loop through the folder and ensure I get all the filed I need to be renamed.  These would be the *.jpg files in this folder.  Thanx so much, I appreciate your guidance an dknowlwedge

    Are you renaming files, copying files, or moving files?

    Sunday, April 29, 2018 7:24 PM
  • I am going to rename them with a different name to a destination folder.  Both the revised name and he folder will be constructed from the filename.  The example is as follows:

    1.  Source Folder:  C:\Auto\JPG_Files\*

    2.  Source Filename: 1 Apr 2018 Acura ILX  FWD Sedan.Acura of Orange Park.1st_Response.jpg

         FCnt = 1; FMnth = Apr; FCyr = 2018; FMfgr = Acura; FMdl = ILX; FEgne = FWD Sedan; FDlr = Acura of Orange Park; FEcatg = 1st_Response

    3.  Destination Folder:

         C:Dropbox\%FMfgr%\%FEcatg%\%FCyr%\%FCyr%_FMfgr%_%FMdl%_%FEcatg%_%Fmnth%\

    4.  Destination Filename:

         %FCnt%%FMnth%%FCyr%%FMfgr%%FMdl%%FEgne%.jpg

      The objective.

    Sunday, April 29, 2018 7:48 PM
  • I'm happy to help, but I think you should try to read the available documentation and try to do it on your own.

    As you work and have questions , feel free to post your code and ask them.  In the end, you'll be better off and able to maintain the resultant VBScript code.

    Sunday, April 29, 2018 7:57 PM
  • Here is my vbscript code:

    Dim fso, folder, files, OutputFile
    Dim strPath

    ' Create a FileSystemObject  
    Set fso = CreateObject("Scripting.FileSystemObject")

    ' Define folder we want to list files from
    strPath = "C:\Users\JB\Documents\JB\Neal_Abel\Acura of Orange Park\JPG_Files"

    Set folder = fso.GetFolder(strPath)
    Set files = folder.Files

    ' Create text file to output test data
    Set OutputFile = fso.CreateTextFile("ScriptOutput.txt", True)

    ' Loop through each file  
    Foreach item In files

      ' Output file properties to a text file

       a=Split(item.Name,".", -1)
       OutputFile.WriteLine("Indiv Fields")
       foreach x in a
          OutputFile.WriteLine(x)
    '      OutputFile.WriteLine(x(3))
       Next
              OutputFile.WriteLine("")
          OutputFile.WriteLine("Group Fields")
      OutputFile.WriteLine(item.Name)
      OutputFile.WriteLine(item.Drive)
      OutputFile.WriteLine(item.Type)   
      OutputFile.WriteLine("")
      
    Next

    ' Close text file
    OutputFile.Close

    ----------------

    Here is my output:

    Indiv Fields
    1
    Apr
    2018 Acura ILX  FWD Sedan
    Acura of Orange Park
    1STR
    jpg

    Group Fields
    1.Apr.2018 Acura ILX  FWD Sedan.Acura of Orange Park.1STR.jpg
    C:
    JPEG image

    Indiv Fields
    1
    Apr
    2018 Acura MDX  FWD  SUV
    Acura of Orange Park
    1STR
    jpg

    Group Fields
    1.Apr.2018 Acura MDX  FWD  SUV.Acura of Orange Park.1STR.jpg
    C:
    JPEG image

    Now I am trying to save the indiv fields into some field, so that I can come back and use them in the output folder and filename as parms.  Each of these fields allow me to set my folder and filename.  When I try to reference OutputFile.WriteLine(x(3)), it gives me an error.  I thought that should be the 3rd element in the x array, but that does not seem to be true.  Any guidance or examples I could look at would be great.  Thanx in advance.


    Monday, April 30, 2018 8:36 PM
  • This thread has already been marked as answered, and so the different question about how to construct a VBScript may not be seen by many that would be able to help.  I suggest you post the VBScript question and related code in a new thread, perhaps in the "General Windows Desktop Development" or "Scripting"  forums since the subject matter is OT for this forum.  Also, use the "Insert Code Block" button, it makes posted code much easier to read.

    You said "When I try to reference OutputFile.WriteLine(x(3)), it gives me an error."  Be specific.  What error?  How many items are in the x array?  Are you saying that the script failed?  Or did you try to reference the x variable after x went out of scope in some code we have not seen?


    • Edited by RLWA32 Monday, April 30, 2018 9:04 PM
    Monday, April 30, 2018 9:03 PM
  • I have moved this to 

     > 

    Monday, April 30, 2018 9:13 PM