Set passwords to files names in an excel column RRS feed

  • Question

  • Good morning all and Merry Christmas,

    I am new to VBA and am having trouble with this simple task. I have an excel 2013 sheet that has the following:

    Column A=folder path, Column B=file name (files exist in this path\name already), Column C=file format and Column D=nominated password

    I have referenced the Office 15.0 Object Library, I know how to loop through the sheet and set range values and check to see if the files exist however, my question is how do I save or set the files nominated with their corresponding password.

    Any and all help would be greatly appreciated.


    Monday, December 8, 2014 1:28 AM


  • Hello Laurie,

    Not quite sure what you want. Do you mean you want to save the file with a new filename which includes the password? You want to do this in VBA for Excel right? You don't need to add reference to Office Object Library if you're using VBA for Excel. If you use VBA for Word or Access, then you need to add reference to Excel Object Library to automate Excel workbooks.

    To rename a file on the disk with VBA, you could use the Name statement, for example:

    Sub RenameFile()
        Dim OldName, NewName
        OldName = Cells(2, 2).Value & Cells(2, 1).Value & "." & Cells(2, 3).Value
        NewName = Cells(2, 2).Value & Cells(2, 1).Value & Cells(2, 4).Value & "." & Cells(2, 3).Value
        Name OldName As NewName ' rename the filename, add the password into the filename
    End Sub
    Loop through all the files and rename them, but make sure the files exist, and you need to process the path name since it's not standard path.

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, December 9, 2014 5:54 AM