jasper005
09-25-2007, 09:13 AM
Hello everyone. First off great site, with loads of useful information.
I?m trying to rename multiple files in one folder using VBA. All the file types I would like to rename are .asc and I would like to rename them as .csv with the same file name. I know how to do this with by specifying the original and the new file name...
Dim FileName As String
Dim NewFileName As String
On Error Resume Next
FileName = "C:\XXX.asc"
NewFileName = "c:\XXX.csv"
Name FileName As NewFileName
However as there are a large number of files to rename this isn?t very effective. Could somebody recommend a more effective way of doing this?
I found a great post by mdmackillop which I can use to identify all the .asc files however in the section where it allows you to do something with the files identified I'm not sure what code I should be using. The code from mdmackillop is below (slightly edited)...
Thanks for all your help in advanced.
Jasper
Sub OneType()
Const MyPath = "c:\XXXX" ' Set the path.
Const FileType = "*.asc" ' or "*.doc"
ProcessFiles MyPath, FileType
End Sub
Sub ProcessFiles(strFolder As String, strFilePattern As String)
Dim strFileName As String
Dim strFolders() As String
Dim iFolderCount As Integer
Dim i As Integer
'Collect child folders
strFileName = Dir$(strFolder & "\", vbDirectory)
Do Until strFileName = ""
If (GetAttr(strFolder & "\" & strFileName) And vbDirectory) = vbDirectory Then
If Left$(strFileName, 1) <> "." Then
ReDim Preserve strFolders(iFolderCount)
strFolders(iFolderCount) = strFolder & "\" & strFileName
iFolderCount = iFolderCount + 1
End If
End If
strFileName = Dir$()
Loop
'process files in current folder
strFileName = Dir$(strFolder & "\" & strFilePattern)
Do Until strFileName = ""
'Do things with files here*****************
Debug.Print strFolder & "\" & strFileName
'*******************************************
strFileName = Dir$()
Loop
'Look through child folders
For i = 0 To iFolderCount - 1
ProcessFiles strFolders(i), strFilePattern
Next i
End Sub
I?m trying to rename multiple files in one folder using VBA. All the file types I would like to rename are .asc and I would like to rename them as .csv with the same file name. I know how to do this with by specifying the original and the new file name...
Dim FileName As String
Dim NewFileName As String
On Error Resume Next
FileName = "C:\XXX.asc"
NewFileName = "c:\XXX.csv"
Name FileName As NewFileName
However as there are a large number of files to rename this isn?t very effective. Could somebody recommend a more effective way of doing this?
I found a great post by mdmackillop which I can use to identify all the .asc files however in the section where it allows you to do something with the files identified I'm not sure what code I should be using. The code from mdmackillop is below (slightly edited)...
Thanks for all your help in advanced.
Jasper
Sub OneType()
Const MyPath = "c:\XXXX" ' Set the path.
Const FileType = "*.asc" ' or "*.doc"
ProcessFiles MyPath, FileType
End Sub
Sub ProcessFiles(strFolder As String, strFilePattern As String)
Dim strFileName As String
Dim strFolders() As String
Dim iFolderCount As Integer
Dim i As Integer
'Collect child folders
strFileName = Dir$(strFolder & "\", vbDirectory)
Do Until strFileName = ""
If (GetAttr(strFolder & "\" & strFileName) And vbDirectory) = vbDirectory Then
If Left$(strFileName, 1) <> "." Then
ReDim Preserve strFolders(iFolderCount)
strFolders(iFolderCount) = strFolder & "\" & strFileName
iFolderCount = iFolderCount + 1
End If
End If
strFileName = Dir$()
Loop
'process files in current folder
strFileName = Dir$(strFolder & "\" & strFilePattern)
Do Until strFileName = ""
'Do things with files here*****************
Debug.Print strFolder & "\" & strFileName
'*******************************************
strFileName = Dir$()
Loop
'Look through child folders
For i = 0 To iFolderCount - 1
ProcessFiles strFolders(i), strFilePattern
Next i
End Sub