Solved

Scan folder and append .xml to all files in the folder

Posted on 2006-11-27
10
296 Views
Last Modified: 2011-09-20
Using VBA code, I would like to scan a folder and append the .xml extension to the end of every file in that folder.

What would the VBA code be????

Thanks,
gdunn59
0
Comment
Question by:gdunn59
  • 6
  • 4
10 Comments
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18025048
something like this might do

Function RenameStuff()
Dim strPath As String
Dim strFile As String
Dim myFullFile As String
    strPath = "c:\output\"
    strFile = Dir(strPath, vbDirectory)
    Do While strFile <> ""
        If Right(strFile, 4) = ".snp" Then
            myFullFile = strPath & strFile
            FileCopy myFullFile, myFullFile & ".xml"
            Kill myFullFile
        End If
        strFile = Dir()
    Loop
End Function
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18025056
Sorry, I posted too quickly...do over!! lol

Function RenameStuff()
Dim strPath As String
Dim strFile As String
Dim myFullFile As String
    strPath = "c:\output\"
    strFile = Dir(strPath, vbDirectory)
    Do While strFile <> ""
        myFullFile = strPath & strFile
        FileCopy myFullFile, myFullFile & ".xml"
        Kill myFullFile
    strFile = Dir()
    Loop
End Function

If this behaves strangely after the first one, let me know. The Dir() is kind of flaky
J
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18025077
3rd time is a charm!!

Function RenameStuff()
    Dim strPath As String
    Dim strFile As String
    Dim myFullFile As String
    strPath = "c:\ShannonSave\"
    strFile = Dir(strPath, vbDirectory)
    Do While strFile <> ""
    If strFile <> "." And strFile <> ".." Then
        myFullFile = strPath & strFile
        FileCopy myFullFile, myFullFile & ".xml"
        Kill myFullFile
    End If
    strFile = Dir()
    Loop
End Function
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 34

Expert Comment

by:jefftwilley
ID: 18025079
You'll have to change the strPath to yours of course...but that should work nicely.
J
0
 
LVL 1

Author Comment

by:gdunn59
ID: 18025545
jefftwilley,

It did change the File Type to XML, but the extensions of the files are still showing the old extensions.

Do I need to do something different????

Thanks,

gdunn59
0
 
LVL 34

Accepted Solution

by:
jefftwilley earned 250 total points
ID: 18027095
Function RenameStuff()
    Dim strPath As String
    Dim strFile As String
    Dim myFullFile As String
    Dim myNewFile as string
    strPath = "c:\ShannonSave\"
    strFile = Dir(strPath, vbDirectory)
    Do While strFile <> ""
    If strFile <> "." And strFile <> ".." Then
        myFullFile = strPath & strFile
        myNewFile = left(strFile, len(strFile)-4)
        myNewFile = strPath & myNewFile & ".xml"
        FileCopy myFullFile, myNewFile
        Kill myFullFile
    End If
    strFile = Dir()
    Loop
End Function
0
 
LVL 1

Author Comment

by:gdunn59
ID: 18030061
The last one worked like a charm.  Thank you very much!!!!!!!!

gdunn59
0
 
LVL 1

Author Comment

by:gdunn59
ID: 18030578
jefftwilley,

One more thing, could you just add comments to each line, explaining what each line is doing?

Thanks,
gdunn59
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18030718
ummm...sure

Function RenameStuff()
    Dim strPath As String
    Dim strFile As String
    Dim myFullFile As String
    Dim myNewFile as string
    strPath = "c:\ShannonSave\"                     '<------Your target directory
    strFile = Dir(strPath, vbDirectory)               '<--------this defines the file name in the directory. This is a file system object
    Do While strFile <> ""                    '<--------Cycle through all the files in the directory until there aren't any more
    If strFile <> "." And strFile <> ".." Then    '<----if the file is a root dir or a parent dir...ignore it
        myFullFile = strPath & strFile                '<-----My full file name is the path and the file name
        myNewFile = left(strFile, len(strFile)-4)  '<------My new file name needs it's extension stripped off so I take the left part of the string minus 4  ie... ".doc" 4 characters
        myNewFile = strPath & myNewFile & ".xml"   '<-----Now append the required extension to the raw file name
        FileCopy myFullFile, myNewFile                   ' <----copy old file name as new file name
        Kill myFullFile                                 '<--------delete the old file
    End If
    strFile = Dir()                                   '<-------grab the next file in the directory
    Loop
End Function
0
 
LVL 1

Author Comment

by:gdunn59
ID: 18031398
Thanks again, you ROCK!
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question