Renaming a file with VBA

[dgillz]

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:

'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:

http://www.gainfocus.biz/exceladdin.html

 

[combo]

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

combo

 

[dgillz]

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:

http://www.gainfocus.biz/exceladdin.html

 

[combo]

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

 

[dgillz]

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:

http://www.gainfocus.biz/exceladdin.html

 

[Andrzejek]

I just tried this:

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 [tt]stFileName[/tt] ?

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.

 

[combo]

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

Name stFileName As stImportedFileName
Msgbox "Change name of " & stImportedFileName & " executed"

and run your full code.

combo

 

[dgillz]

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:

http://www.gainfocus.biz/exceladdin.html

 

[combo]

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

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

 

[dgillz]

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:

http://www.gainfocus.biz/exceladdin.html

 

[combo]

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