Re: rename files according to file list in excel by Ray
Ray
Thu Jan 06 14:24:13 CST 2005
In that case, I'd definitely make a spreadsheet with both the old
names and the new names. Code like this in the module should work.
Option Explicit
Const FILE_PATH = "C:\somefiles\"
Sub RenameFiles()
Dim sFilename As String
Dim sNewname As String
Dim i As Integer: i = 1
Do
sFilename = Sheet1.Cells(i, 1).Value
sNewname = Sheet1.Cells(i, 2).Value
If sFilename = "" Or sNewname = "" Then Exit Do
FileSystem.FileCopy FILE_PATH & sFilename, FILE_PATH & sNewname
FileSystem.Kill FILE_PATH & sFilename
i = i + 1
Loop
End Sub
--
Ray at work
Microsoft ASP/ASP.NET MVP
"Frederik De Fruyt" <FrederikDeFruyt@discussions.microsoft.com> wrote
in message news:A9694AFD-324D-4033-9897-82766CA67F54@microsoft.com...
> Ray,
>
> If it's the same for you, yes, in an excel module it would be
better also
> for me.
>
> I can use 1 excel file with the column A completed with the
filenames and a
> small button next to it that I can click to activate the script.
>
> Thanks in advance for your answers & help.
>
> Frederik.
>
> "Ray Costanzo [MVP]" wrote:
>
> > Do you want to run this code in a module within Excel? If so,
it'll
> > be totally different then. That would make things a bit cleaner
to
> > run it within Excel however. Is that your prefence?
> >
> > The code that I posted is VB Script, which is typically run by
the
> > Windows Script Host from a .vbs file.
> >
> > --
> >
> > Ray at work
> > Microsoft ASP/ASP.NET MVP
> >
> >
> > "Frederik De Fruyt" <FrederikDeFruyt@discussions.microsoft.com>
wrote
> > in message
news:D2BE4B41-872F-4CE9-A392-430EFAD49941@microsoft.com...
> > > Thanks for the code.
> > >
> > > I have pasted the code into a module of excel (a different
workbook
> > then
> > > files.xls), it divided itself into 4 parts.
> > >
> > > First it gave a message that variable was not defined
"WScript". I
> > made an
> > > additional line "Dim WScript".
> > >
> > > When run sub main I get a run time error '-2147217887,
(80040e21)
> > > Multiple-step operation generated errors. Check each status
value.
> > >
> > > When I click debug the yellow marker is on the line:
> > > oRS.AddNew Array("Filename", "NumericPart"), Array(oFile.Name,
> > > Split(oFile.Name, ".")(0))
> > >
> > > Actually I had to make 1 line out of it otherwise it showed in
> > error in
> > > excel vb (red).
> > >
> > > Thanks for your help on this. We are almost their I think.
> > >
> > > I am running Win XP SP2 with office 2002 SP3.
> > >
> > >
> > > "Ray Costanzo [MVP]" wrote:
> > >
> > > > Making a second column would probably make for a bit more of
an
> > > > efficient script, but if it'll save human time by not making
a
> > second
> > > > column, sorting the files collection would work as well.
With
> > the
> > > > code sample below, there is no verification that the number
of
> > files
> > > > in the directory matches the number of new file names or
anything
> > > > like that.
> > > >
> > > > <Single column spreadsheet method>
> > > > Excel file path: C:\file.xls
> > > > Test path: C:\somefiles
> > > > Files:
> > > > 2.txt
> > > > 12.txt
> > > > 38.txt
> > > > 39.txt
> > > > 103.txt
> > > >
> > > > Spreadsheet column A
> > > > two.pdf
> > > > twelve.pdf
> > > > thirty-eight.pdf
> > > > thirty-nine.pdf
> > > > one-hundred-three.pdf
> > > >
> > > > Code:
> > > >
> > > >
> > > > Option Explicit
> > > >
> > > > Const XLS_PATH = "C:\file.xls"
> > > > Const FILE_PATH = "C:\somefiles\"
> > > >
> > > > Call Main()
> > > >
> > > > Sub Main()
> > > > Dim aFilelist, aNewNames
> > > > Dim oFSO, oFile
> > > > Dim i
> > > >
> > > > aFilelist = GetFilelist()
> > > > aNewNames = GetNewNames()
> > > >
> > > > Set oFSO = CreateObject("Scripting.Filesystemobject")
> > > >
> > > > For i = 0 To UBound(aFilelist, 2)
> > > > WScript.Echo "would rename " & aFilelist(0, i) & " to
" &
> > > > aNewNames(0, i)
> > > > 'Set oFile = oFSO.GetFile(FILE_PATH & aFilelist(0,
i))
> > > > 'oFile.Name = aNewNames(0, i)
> > > > Next
> > > >
> > > > Set oFile = Nothing
> > > > Set oFSO = Nothing
> > > > End Sub
> > > >
> > > >
> > > > Function GetFilelist()
> > > > Dim oFSO, oDir, oFiles, oFile
> > > > Dim oRS
> > > >
> > > > Set oFSO = CreateObject("Scripting.FileSystemObject")
> > > > Set oDir = oFSO.GetFolder(FILE_PATH)
> > > > Set oFiles = oDir.Files
> > > >
> > > > ''create disconnected recordset of files - easy sorting
> > > > Set oRS = CreateObject("ADODB.Recordset")
> > > > oRS.CursorLocation = 3 'adUseClient
> > > > oRS.Fields.Append "Filename", 200, 255 '200 = adVarChar
> > > > oRS.Fields.Append "NumericPart", 3 'adInteger
> > > >
> > > > oRS.Open
> > > > For Each oFile In oFiles
> > > > oRS.AddNew Array("Filename", "NumericPart"),
> > > > Array(oFile.Name, Split(oFile.Name, ".")(0))
> > > > Next
> > > > Set oFiles = Nothing: Set oDir = Nothing: Set oFSO =
Nothing
> > > >
> > > > oRS.Sort = "NumericPart ASC"
> > > > GetFilelist = oRS.GetRows(, , "Filename")
> > > > oRS.Close: Set oRS = Nothing
> > > > End Function
> > > >
> > > > Function GetNewNames()
> > > > Dim oADO, oRS
> > > > Set oADO = CreateObject("ADODB.Connection")
> > > > oADO.ConnectionString =
> > "Provider=Microsoft.Jet.OLEDB.4.0;Data
> > > > Source=C:\file.xls;Extended Properties=""Excel
> > 8.0;HDR=No;IMEX=1"""
> > > > oADO.Open
> > > > Set oRS = oADO.Execute("SELECT * FROM [Sheet1$]")
> > > > If Not oRS.EOF Then GetNewNames = oRS.GetRows
> > > > oRS.Close: Set oRS = Nothing
> > > > oADO.Close: Set oADO = Nothing
> > > > End Function
> > > > </Single column spreadsheet method>
> > > >
> > > > --
> > > >
> > > > Ray at work
> > > > Microsoft ASP/ASP.NET MVP
> > > >
> > > >
> > > > "Frederik De Fruyt" <Frederik De
Fruyt@discussions.microsoft.com>
> > > > wrote in message
> > > > news:6E4E9167-8DE3-49AA-A09B-07AB33F39A85@microsoft.com...
> > > > > Ray,
> > > > >
> > > > > Thanks for your quick answer.
> > > > >
> > > > > You are right, I should I made it clear that the files are
> > > > generated by a
> > > > > virtual print driver that gives a numerical name to the pdf
> > files.
> > > > Like
> > > > > 56.pdf, 57.pdf, 58...59
> > > > >
> > > > > So the files should be sorted numerical. On the one hand I
have
> > a
> > > > list with
> > > > > invoice numbers (not always sequential 30, 31, 34, 35) and
on
> > the
> > > > other hand
> > > > > I have a list with sequential numerical files. They are
always
> > > > printed in the
> > > > > correct order.
> > > > >
> > > > > So your suggestion to make a second column is not at all
bad as
> > I
> > > > could
> > > > > easily make that second column based on the first number
used
> > as
> > > > file and
> > > > > then fill down + add 1 to it.
> > > > >
> > > > > Do you have any idea how I should adjust the code?
> > > > >
> > > > > Thanks in advance.
> > > > > Frederik.
> > > > >
> > > > > "Ray Costanzo [MVP]" wrote:
> > > > >
> > > > > > Before getting into the code, the one thing that you
should
> > know
> > > > is
> > > > > > that the files collection will not necessarily return the
> > files
> > > > in
> > > > > > the order that you consider being first to last. Who is
to
> > say
> > > > what
> > > > > > file is first? I suggest one of the two:
> > > > > >
> > > > > > 1. Add a column to your spreadsheet that has the old
file
> > name
> > > > next
> > > > > > to each new file name.
> > > > > > 2. Tell us what you consider to be the "first" file -
the
> > first
> > > > that
> > > > > > was created (by date), the first alphabetically, the one
most
> > > > > > recently modified, etc.
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Ray at work
> > > > > > Microsoft ASP/ASP.NET MVP
> > > > > >
> > > > > >
> > > > > > "Frederik De Fruyt" <Frederik De
> > Fruyt@discussions.microsoft.com>
> > > > > > wrote in message
> > > > > >
news:75B8DFA3-0045-4A6C-B07B-A7932B96B53B@microsoft.com...
> > > > > > > Dear,
> > > > > > >
> > > > > > > I would like to rename all files in a directory,
starting
> > with
> > > > the
> > > > > > first
> > > > > > > file in that directory. The new name of the file should
be
> > the
> > > > > > first name vb
> > > > > > > finds in an excel list starting from Cell A1 to end of
> > list.
> > > > > > >
> > > > > > > I have starting with someting like this:
> > > > > > >
> > > > > > > Function RENAME()
> > > > > > > Dim oldName, newName
> > > > > > > Set fso = CreateObject("Scripting.FileSystemObject")
> > > > > > > Set f = fso.GetFolder("C:\data")
> > > > > > > For Each file In f.Files
> > > > > > >
> > > > > > > Name oldName As newName
> > > > > > >
> > > > > > > Next
> > > > > > > End Function
> > > > > > >
> > > > > > > But as you can see I still need the loop through cell
A1...
> > > > > > >
> > > > > > > Can somebody help me?
> > > > > > >
> > > > > > > Thanks
> > > > > > > Frederik.
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >