Dynamic Files Names - From Listing

June 18, 2009 at 09:11:27
Specs: Windows XP
Hello,

Hopefully this is a quick and easy question. Thanks!

I have a large excel file that has a large listing on files, that are word documents. I was wondering if you can link the two files together-so if you changed the name of a file in the excel list, it would automatically change the name of the actual word file.

Thanks again,

Erik


See More: Dynamic Files Names - From Listing

Report •


#1
June 18, 2009 at 12:17:31
How good are you with VBA?

This code creates a new folder based on user input, copies 6 files to the new folder and then renames 6 files (5 .xls files and 1 .doc) using that same input.

With some modifications, it could be used to pick up a file name from a cell and rename a specific file, however, both the old and new names are going to have to exist on the spreadsheet (or in a VBA variable) for some period of time so the code can use the old name to find the file. Once the file is renamed, it could be deleted.

Sub MakeFiles()
Dim MyFileName As String
Dim MyFilePath As String
Dim SearchString As String
Dim F_Name As String
Dim F_Date As String
Dim x As String



'Get New File Name
  F_Name = Application.InputBox("Enter File Name", "Name Input", Type:=2)
  If F_Name = "False" Then Exit Sub
  
  F_Date = InputBox("Enter Date (e.g. 2006 11 04)", "Date Input", Default:=Format(Now(), "yyyy mm dd"))

'Create New File Folder
  MkDir "C:\Documents and Settings\username\Desktop\Workshop Spreadsheets\" & F_Name

'Copy Files
  sfol = "C:\Documents and Settings\username\Desktop\Spreadsheets\Original Spreadsheets"
  dfol = "C:\Documents and Settings\username\Desktop\Spreadsheets\" & F_Name
   Set fso = CreateObject("Scripting.FileSystemObject")
    On Error Resume Next
         fso.CopyFile (sfol & "\*.*"), dfol

'Set File Path
  MyFilePath = "C:\Documents and Settings\username\Desktop\Workshop Spreadsheets\" & F_Name & "\"

'Find and Rename Each File
    SearchString = "*OldName1*.*"
      MyFileName = Dir$(MyFilePath & SearchString, vbNormal)
      Name MyFilePath & MyFileName As MyFilePath & F_Name & " " & F_Date & " A.xls"

    SearchString = "*OldName2*.*"
      MyFileName = Dir$(MyFilePath & SearchString, vbNormal)
      Name MyFilePath & MyFileName As MyFilePath & F_Name & " " & F_Date & " B.xls"

    SearchString = "*OldName3*.*"
      MyFileName = Dir$(MyFilePath & SearchString, vbNormal)
      Name MyFilePath & MyFileName As MyFilePath & F_Name & " " & F_Date & " C.xls"

    SearchString = "*OldName4*.*"
      MyFileName = Dir$(MyFilePath & SearchString, vbNormal)
      Name MyFilePath & MyFileName As MyFilePath & F_Name & " " & F_Date & " ERTI.xls"

    SearchString = "*OldName5*.*"
      MyFileName = Dir$(MyFilePath & SearchString, vbNormal)
      Name MyFilePath & MyFileName As MyFilePath & F_Name & " " & F_Date & " D.xls"

    SearchString = "*OldName6*.*"
      MyFileName = Dir$(MyFilePath & SearchString, vbNormal)
      Name MyFilePath & MyFileName As MyFilePath & F_Name & " " & F_Date & " Expense.xls"
      
   SearchString = "*OldName7*.*"
      MyFileName = Dir$(MyFilePath & SearchString, vbNormal)
      Name MyFilePath & MyFileName As MyFilePath & F_Name & " " & F_Date & " Notes.doc"

'Reset Directory Variable
  MyFileName = Dir



End Sub



Report •

#2
June 18, 2009 at 14:59:42
Oh wow, thanks so much for the detailed response!!

I am not able to implement that, I will be doing them by hand.

Thanks though!


Report •

Related Solutions


Ask Question