+ Reply to Thread
Results 1 to 4 of 4

Rename Multiple PDF Files using Excel VBA

Hybrid View

  1. #1
    Registered User
    Join Date
    03-06-2018
    Location
    new york
    MS-Off Ver
    2010
    Posts
    2

    Question Rename Multiple PDF Files using Excel VBA

    I have a compressed file consisting of 900+ PDF files. I have an excel reference file that has column A (account #), column B (brokerage #), column C (Entity). Currently all these pdfs are names with the account Number in their title. But I want to rename them to their brokerage # and group the accounts in folders separated by their entities (4 different types).

    Is there a macro function to rename a list of pdf's using the account # - replace it with the corresponding brokerage number and group the pdfs in their respective entity folders. I can unzip the original folder before writing any macro but preferably dont want to.

    Any help is appreciated.
    Thanks

  2. #2
    Valued Forum Contributor ranman256's Avatar
    Join Date
    07-29-2012
    Location
    Kentucky
    MS-Off Ver
    Excel 2003
    Posts
    1,189

    Re: Rename Multiple PDF Files using Excel VBA

    need the filename format of the existing pdf,
    and how you want it to look when renamed.

  3. #3
    Registered User
    Join Date
    03-06-2018
    Location
    new york
    MS-Off Ver
    2010
    Posts
    2

    Re: Rename Multiple PDF Files using Excel VBA

    Current PDF name: TaxForm_1099Comp_2017_1815_"1234567"
    New PDF Name: GSB="0000000000"+CSP="TOPACCOUNT"+DOC=M1+TT=12312016+D=2016_PPP1099PAIRED
    The values in "" in the current PDF name are dynamic based on column A in the reference file
    The values in "" in the new PDF name are dynamic based on columns B and C in the reference file

  4. #4
    Valued Forum Contributor ranman256's Avatar
    Join Date
    07-29-2012
    Location
    Kentucky
    MS-Off Ver
    Excel 2003
    Posts
    1,189

    Re: Rename Multiple PDF Files using Excel VBA

    you will need to adjust the code to fit you.
    in make new filename:
          'make new filename
      vNewN = vAcct & vBrok & "_TOPACCOUNT_" & vRemTxt & ".pdf"
    change the layout of your target filename.


    change:
    Sheets("Accounts").Activate
    to the name of your excel sheetname with the accounts

    in RenameFiles(),
    change the Folder,"c:\temp\", where your pdfs are located

    then run RenameFiles

    Public Sub RenameFiles()
      RenameFilesInDir "c:\temp\"
    End Sub
    
    
    Private Sub RenameFilesInDir(ByVal pvSrcDir)
    Dim FSO, oFolder, oFile, oRX
    Dim sCriteria As String, sSrcFile As String
    Dim iCnt As Integer, f As Integer, i As Integer
    Dim vXFmt, vNewFile, vAcct, vBrok, vEnt
    Dim wbApp As Workbook, wbFile As Workbook
    Dim wsTarg As Worksheet
    Dim vDirEnt, vRemTxt, vNewN
    
    On Error GoTo errGetFiles
    
    Sheets("Accounts").Activate
    
    Set wbApp = ActiveWorkbook
    If Right(pvSrcDir, 1) <> "\" Then pvSrcDir = pvSrcDir & "\"
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = FSO.GetFolder(pvSrcDir)
    
    Sheets.Add
    Set wsTarg = ActiveSheet
    For Each oFile In oFolder.Files
      If InStr(oFile.Name, ".pdf") > 0 Then       'pdf only
             sSrcFile = pvSrcDir & oFile.Name
                 
                 'grab acct# from filename
             If i > 0 Then
                                       'Current PDF name: TaxForm_1099Comp_2017_1815_"1234567"
                f = InStrRev(oFile.Name, "_")
                vRemTxt = Left(oFile.Name, f)
                vAcct = Mid(oFile.Name, i + 1)
                vAcct = Left(vAcct, Len(vAcct) - 4)  'remove .pdf
                FindAcct vAcct
                
                'vAcct = ActiveCell.Offset(0, 0).Value
                vBrok = ActiveCell.Offset(0, 1).Value
                vEnt = ActiveCell.Offset(0, 2).Value
                
                If pvEnt <> "" Then
                       'make entity folder
                    vDirEnt = pvDir & vEnt
                    MakeDir vDirEnt
                    
                       'make new filename
                    vNewN = vAcct & vBrok & "_TOPACCOUNT_" & vRemTxt & ".pdf"
                    vNewFile = vDirEnt & vNewN
                         
                         'rename it
                    Name sSrcFile As vNewFile
                End If
             End If
      End If
    Next
    MsgBox "Done"
    Set wbApp = Nothing
    Set wbFile = Nothing
    Set wsTarg = Nothing
    
    endit:
    Set oFile = Nothing
    Set oFolder = Nothing
    Set FSO = Nothing
    Exit Sub
    
    errGetFiles:
      MsgBox Err.Description, , Err
    End Sub
    
    Public Sub MakeDir(ByVal pvDir)
    Dim FSO
    Set FSO = CreateObject("Scripting.FileSystemObject")
    If Not FSO.FolderExists(pvDir) Then FSO.CreateFolder pvDir     'MkDir pvDir
    Set FSO = Nothing
    End Sub
    
    Public Sub FindAcct(ByVal pvAcct, pvBrok, pvEnt)
    On Error GoTo errFind
    
    Columns("A:A").Select
    Selection.Find(What:=pvAcct, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    
        'pvAcct = ActiveCell.Offset(0, 0).Value
        pvBrok = ActiveCell.Offset(0, 1).Value
        pvEnt = ActiveCell.Offset(0, 2).Value
    Exit Sub
    
    errFind:
    pvBrok = ""
    pvEnt = ""
    Debug.Print pvAcct & ": not found"
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Rename Multiple PDF files in a folder per the list in Excel spreadsheet
    By vmanil75 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-18-2017, 09:40 AM
  2. [SOLVED] Copy/Rename Multiple files in Excel
    By chris619 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-13-2017, 02:44 PM
  3. [SOLVED] rename multiple excel files as per its own cell value A2
    By raj soni in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 09-11-2017, 01:55 AM
  4. [SOLVED] Copy/Rename Multiple files using Excel values in columns
    By felizeng in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-25-2017, 10:52 AM
  5. How to copy and rename files in vba by searching multiple folders for files
    By razorace in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-04-2014, 04:21 AM
  6. VBA Rename Multiple .txt Files
    By cdowney in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-30-2013, 03:34 PM
  7. Replies: 0
    Last Post: 03-04-2013, 02:37 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1