INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Renaming a file with VBA

Renaming a file with VBA

(OP)
I have VBA project that imports a text file into a SQL database. This is working wonderfully. However at the end of that, I want to rename the text file to a .imp file so that we know that this has imported already. This should be ridiculously simple but my doesn't work and does not return any errors.

I put a watch on the variables stFileName and StImportedFileName and they are holding the proper values.

Any ideas what I am doing wrong? Here is my code:

CODE

'rename text file so it would be difficult to re-import the file by mistake
Dim stImportedFileName As String

stImportedFileName = Replace(stFileName, ".txt", ".imp")
    
Name stFileName As stImportedFileName 

Software Training, Implementation, Programming and Support for Macola Progression, Macola ES, Synergy, and Crystal Reports. Check out our Macola tools: www.gainfocus.biz/exceladdin.html

RE: Renaming a file with VBA

The file should be closed. Try with a string with full path and file name.

combo

RE: Renaming a file with VBA

(OP)
The string does include the full file path and file name. And the file is not opened either (as in opened in notepad, how I would normally open a text file) if this is what you mean. Any other ideas?

Software Training, Implementation, Programming and Support for Macola Progression, Macola ES, Synergy, and Crystal Reports. Check out our Macola tools: www.gainfocus.biz/exceladdin.html

RE: Renaming a file with VBA

Do you have any On Error Resume Next or similar statement that can skip error? Does this code execute? For me the file is either renamed or "File not found" runtime error raised.

combo

RE: Renaming a file with VBA

(OP)
I don't have "On Error Resume Next" in my code. Should I have it? I am not getting any errors. I have put a break in my code and stepped through it by hitting F8, so I know the code is executing, it just isn't doing anything.

Software Training, Implementation, Programming and Support for Macola Progression, Macola ES, Synergy, and Crystal Reports. Check out our Macola tools: www.gainfocus.biz/exceladdin.html

RE: Renaming a file with VBA

I just tried this:

CODE

Sub RenameFile()
Dim stFileName As String
Dim stImportedFileName As String

stFileName = "C:\TEMP\MytestFile.txt"

stImportedFileName = Replace(stFileName, ".txt", ".imp")
    
Name stFileName As stImportedFileName

End Sub 

Worked OK for me...
What's your value of stFileName ?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: Renaming a file with VBA

No, avoid this statement, at least in debug period. It's strange, as i wrote, this code for me either renames the file or I get an error if the file is missing. It works well with hidden extensions too. Check your permissions for the folder/file, I haven't tested the behaviour of this code for limited access to the file. Can you rename the file manually, just after executing this line?
Instead of break-point add message, for instance

CODE -->

Name stFileName As stImportedFileName
Msgbox "Change name of " & stImportedFileName & " executed" 
and run your full code.

combo

RE: Renaming a file with VBA

(OP)
Te value of stFileName is "C:\macapps\macsql\data_002\refund.txt". I put a watch on this variable while stepping through the code, so I know it is the correct value.

I can manually rename the files without issues.

Software Training, Implementation, Programming and Support for Macola Progression, Macola ES, Synergy, and Crystal Reports. Check out our Macola tools: www.gainfocus.biz/exceladdin.html

RE: Renaming a file with VBA

What happens if you add reference to Microsoft Scripting Runtime and execute:

CODE -->

Dim scrFSO As Scripting.FileSystemObject
Dim scrFile As Scripting.File
Set scrFSO = New Scripting.FileSystemObject
Set scrFile = scrFSO.GetFile("C:\macapps\macsql\data_002\refund.txt")
scrFile.Name = Replace(scrFile.Name, ".txt", ".imp") 

combo

RE: Renaming a file with VBA

(OP)
Combo,

No that does nothing. Don't we have to have a "name <filename> as <newfilename>" line of code in here? I thought we did, so I put one in, but it still isn't working.

I already had the reference to the MS scripting runtime in my project.

Software Training, Implementation, Programming and Support for Macola Progression, Macola ES, Synergy, and Crystal Reports. Check out our Macola tools: www.gainfocus.biz/exceladdin.html

RE: Renaming a file with VBA

No, the last line should assign new name to the file object. Can you at all rename a file in another place with this code? In separate procedure?

combo

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close