+ Reply to Thread
Results 1 to 4 of 4

Copy/Rename Multiple files using Excel values in columns

  1. #1
    Registered User
    Join Date
    08-25-2017
    Location
    USA
    MS-Off Ver
    Microsoft Office Professional Plus 2010
    Posts
    7

    Cool Copy/Rename Multiple files using Excel values in columns

    Hello Everyone,
    I am trying to copy/rename .jpg files where the name of each file is comprised of 5 columns "concatenated". So far, I am able to extract one column, copy/rename old file into a new directory. If you can please help me concatenate columns B through E separated by hyphens (-), I would greatly appreciate it.

    Below is code I am currently using and I've attached an image of the excel file I am extracting the info from:
    Capture.JPG

    'Copies/Renames files from column B. Name needs to be formed from columns B through E
    Option Explicit
    Sub CopyRenameFile()

    Dim src As String, dst As String, fl As String
    Dim rfl As String
    Dim lngMyRow As Long
    Dim lngLastRow As Long

    Application.ScreenUpdating = False

    lngLastRow = Range("A:B").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row + 1
    'Source directory
    src = "J:\My Art Work\UncategorizedArtWork\TitledArtWork"
    'Destination directory
    dst = "C:\Users\felizm\Desktop\temp"

    For lngMyRow = 2 To lngLastRow
    'File Name
    fl = Range("A" & lngMyRow)
    'Rename File
    rfl = Range("B" & lngMyRow)
    On Error Resume Next
    FileCopy src & "\" & fl, dst & "\" & rfl & ".jpg"
    'If Err.Number <> 0 Then
    'MsgBox "Copy Error:" & src & "\" & rfl
    'End If
    On Error GoTo 0
    Next lngMyRow

    Application.ScreenUpdating = True

    MsgBox "Done"

    End Sub

  2. #2
    Valued Forum Contributor
    Join Date
    03-16-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    371

    Re: Copy/Rename Multiple files using Excel values in columns

    Simply:

    Please Login or Register  to view this content.
    or if you don't like having to have the hypen typed out so many times, I have a string join function you can have:

    Please Login or Register  to view this content.
    So now you would have:
    Please Login or Register  to view this content.
    Or if you're lazy you could directly write a loop instead of typing out or using the function (essentially what my function does):
    Please Login or Register  to view this content.
    Design everything to be as simple as possible, but no simpler.

  3. #3
    Registered User
    Join Date
    08-25-2017
    Location
    USA
    MS-Off Ver
    Microsoft Office Professional Plus 2010
    Posts
    7

    Re: Copy/Rename Multiple files using Excel values in columns

    Wow next level knowledge!! Much appreciated my friend.

  4. #4
    Valued Forum Contributor
    Join Date
    03-16-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    371

    Re: Copy/Rename Multiple files using Excel values in columns

    If that takes care of your original question, please select Thread Tools from the menu link above to mark this thread as SOLVED.
    To say thanks to the user(s) who contributed towards the solution, you can use the "Add Reputation" button on their helpful post(s).
    Thanks!

+ 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] Bulk rename excel files based on values of two different cells
    By Czarny in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 01-20-2015, 10:47 AM
  4. 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
  5. Replies: 1
    Last Post: 08-14-2013, 07:55 AM
  6. Replies: 0
    Last Post: 08-13-2013, 12:32 PM
  7. Copy-Paste Macro of 3 columns from multiple excel files into one summary file
    By jpmaster53 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-15-2012, 07:10 AM

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