locked
Want PDF files Rename and copy path RRS feed

  • Question

  • Dear expert's

    i have a sheet1, in column A i have the recent PDF File name and in column B i have name which want to swap with recent(which in column A) and also want in column C path of all saving each files...??

    & all PDF files are save in path:  C:\Users\Pervaiz Iqbal\Desktop\pdf folder

    Adeel


    • Edited by Adeeeel Thursday, February 23, 2017 5:42 PM
    Thursday, February 23, 2017 5:40 PM

Answers

  • Hi Adeeeel,

    you had mentioned that in column C you want a file location.

    you just need to add line below , after renaming the file.

     Cells(lngR, "C").Value = strP & Cells(lngR, "B").Value

    modified code:

    Sub Test()
         Dim lngR As Long
         Dim strP As String
    
         strP = "C:\Users\v-padee\Desktop\pdf files\"
    
         For lngR = 2 To Cells(Rows.Count, "A").End(xlUp).Row
             Name strP & Cells(lngR, "A").Value As strP & Cells(lngR, "B").Value
             Cells(lngR, "C").Value = strP & Cells(lngR, "B").Value
         Next lngR
    
     End Sub

    Output:

    if you have any further question then let us know about that.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Adeeeel Friday, February 24, 2017 6:55 AM
    Friday, February 24, 2017 5:08 AM

All replies

  •             

    Not sure what you have/want in C  ...   This assumes that all the files are in that folder, and just need to be renamed, and that the values in A and B include the file extension   ".PDF"   Further assumes that the names start in row 2.... Run the macro with Sheet1 As the active sheet.

    Sub Test()
        Dim lngR As Long
        Dim strP As String

        strP = "C:\Users\Pervaiz Iqbal\Desktop\pdf folder\"

        For lngR = 2 To Cells(Rows.Count, "A").End(xlUp).Row
            Name strP & Cells(lngR, "A").Value As strP & Cells(lngR, "B").Value
        Next lngR

    End Sub



    Thursday, February 23, 2017 9:03 PM
  • dear sir

    it is working but file changed it should not, in column C i want location of file with file name..??

    like..C:\Users\adeel\Desktop\1

    Adeel


    • Edited by Adeeeel Friday, February 24, 2017 4:25 AM
    Friday, February 24, 2017 4:25 AM
  • Hi Adeeeel,

    you had mentioned that in column C you want a file location.

    you just need to add line below , after renaming the file.

     Cells(lngR, "C").Value = strP & Cells(lngR, "B").Value

    modified code:

    Sub Test()
         Dim lngR As Long
         Dim strP As String
    
         strP = "C:\Users\v-padee\Desktop\pdf files\"
    
         For lngR = 2 To Cells(Rows.Count, "A").End(xlUp).Row
             Name strP & Cells(lngR, "A").Value As strP & Cells(lngR, "B").Value
             Cells(lngR, "C").Value = strP & Cells(lngR, "B").Value
         Next lngR
    
     End Sub

    Output:

    if you have any further question then let us know about that.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Adeeeel Friday, February 24, 2017 6:55 AM
    Friday, February 24, 2017 5:08 AM
  • dear sir

    now i am getting the location of file but issue still there of which snap share earlier above...?? 

    Adeel

    Friday, February 24, 2017 5:45 AM
  • Hi Adeeeel,

    do you mean that the type of file is getting change when you rename it.

    like below. you can see that first file is look like your files in your output.

    it's because you did not add the ".pdf" file extension after file name. this can be the possible reason for this issue.

    you need to pass file name with extension like I passed for rest of the files. you can see in my output, other files are renamed correctly as PDF.

    try to correct your new file name to solve the issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, February 24, 2017 6:02 AM
  • dear sir

    yes, this is working now thanks for kind help

    Adeel

    Friday, February 24, 2017 6:55 AM
  • Dear sir

    i found some issues with code. 

    1-code is changing the name till only 26 cells.

    2-after running first time, second time code don't find the code showing error that "file not found" after that i need to close file and need to make new one..please look into

    For Excel File

    https://www.dropbox.com/s/k22081akvv3brv2/spl.xlsm?dl=0


    Adeel




    • Edited by Adeeeel Saturday, February 25, 2017 3:35 PM
    Saturday, February 25, 2017 2:55 PM
  • If it is possible that the files don't exist to be renamed, then use a check for existence:

    Sub Test2()
        Dim lngR As Long
        Dim strP As String

        strP = "C:\Users\Pervaiz Iqbal\Desktop\pdf folder\"

        For lngR = 2 To Cells(Rows.Count, "A").End(xlUp).Row
            If Dir(strP & Cells(lngR, "A").Value) <> "" Then
                Name strP & Cells(lngR, "A").Value As strP & Cells(lngR, "B").Value
            End If
        Next lngR

    End Sub

    Saturday, February 25, 2017 4:21 PM
  • dear sir

    all the files are there, if i put only name for replace like 1,2,3 this is working but when put names (which is mention in link) is not working & specially when run second time, code don't work and sheet need to be close

    find below folder link

    https://www.dropbox.com/sh/dif43d99w5l8pz6/AADWx544p1M9oaY9VbqHAbd6a?dl=0

    Adeel


    Saturday, February 25, 2017 5:04 PM
  • The first filename that fails has an illegal character  /    

    These characters are not allowed in filenames:     / ? < > \ : * | "

    Saturday, February 25, 2017 5:31 PM
  • sir,

    can you modify code..??

    & if i run code, its fails to run due to special character than i removed the character and run code again, code doesn't work i need to close file and rebuilt it..??after removing character it should work neither that rebuilt.

    if i run code successfully than i changed the name in column B than run code,after that code is also not working & also in this condition file need to be rebuilt.

    Adeel




    • Edited by Adeeeel Saturday, February 25, 2017 6:18 PM
    Saturday, February 25, 2017 5:56 PM
  • You should be testing this on a copy of your folder, so that you can delete all the files and start from fresh if there is an issue.

    But, try this version, with file checking and error handling:

    Sub Test3()
        Dim lngR As Long
        Dim strP As String

        strP = "C:\Users\Pervaiz Iqbal\Desktop\pdf folder\"

        On Error GoTo BadName
        
        For lngR = 2 To Cells(Rows.Count, "A").End(xlUp).Row
            If Dir(strP & Cells(lngR, "A").Value) <> "" And _
                Dir(strP & Cells(lngR, "B").Value) = "" Then
                    Name strP & Cells(lngR, "A").Value As strP & Cells(lngR, "B").Value
                    Cells(lngR, "C").Value = strP & Cells(lngR, "B").Value
            End If
    NextRow:
        Next lngR
        
        Exit Sub

    BadName:
        Resume NextRow

    End Sub


    Saturday, February 25, 2017 6:54 PM
  • sir, 

    now this is working but when i close file its occur error, find below pic and after one time running the code if i only delete the column C(which is for location and there is no change in A & B) and than run, don't showing location in column C

     




    • Edited by Adeeeel Saturday, February 25, 2017 7:33 PM
    Saturday, February 25, 2017 7:31 PM
  • You can create the values for column C with this macro:

    Sub Test4()
        Dim lngR As Long
        Dim strP As String

        strP = "C:\Users\Pervaiz Iqbal\Desktop\pdf folder\"

        For lngR = 2 To Cells(Rows.Count, "A").End(xlUp).Row
                    Cells(lngR, "C").Value = strP & Cells(lngR, "B").Value
        Next lngR
    End Sub

    Saturday, February 25, 2017 8:50 PM
  • thanks this is working but still having problem of when i close the file, already share the pic

    Adeel

    Sunday, February 26, 2017 8:20 AM
  • Hi Adeeeel,

    you had mentioned that , when you close the file excel get crashed and give message that excel has stopped working.

    I think that the issue is not related with the code mentioned above.

    code is doing nothing that can crash the Excel.

    it is a very simple code block.

    first of all I want to suggest you to understand the code.

    because from your last posts, it looks like you don't understand the code , you don't know which line of code doing what changes.

    Here I think that Excel get crashed because some other reason.

    try to check whether the same issue occurs when you try to save the file manually and try to close the Excel file. without using any code.

    you also need to understand that, when you run the code for first time. the column A contains the file names that are available in the folder. so it get replaced with the names available in column B.

    now folder contains the files names contains by the column B.

    so when you run the same code again with same data in the excel sheet.

    it will not able to find that files in the folder because file was already renamed.

    so in that case you need to change the file names in column A.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 27, 2017 5:08 AM
  • thanks Deepak for your cooperation and acknowledgment

    Adeel

    Monday, February 27, 2017 5:41 AM