Dear access experts, here a good issue that is annoying me a lot. I am not a vba expert, I only need to rename a txt file created by a dos procedure invoked by an Access macro through the same macro. What happens is that the txt file just created seems to be held by the macro, because if I try to rename it through a VBA module inside Access database by runnig it after the creation during the macro, nothing happens, conversely if I let the macro finish and execute the VBA renaming module inside a new macro, it works fine. Maybe I need to create an external exe vba file to rename the txt file to be invoked by the main macro? How can I do that? Thank you guys!!!
renaming external file from within a macro
- Thread starter beamer
- Start date
Thanks Dave, I am not able, I do not know how to do. As I said, I do not know how to manage vba. Anyway, If someone could send to me this module converted in an exe file, I could call it from my macro and see what happens.
Public Sub rinomina()
Dim FileName As String
Dim NewFileName As String
On Error Resume Next
FileName = "C:\SCONTRIN.INP"
NewFileName = "C:\SCONTRINO.INP"
Name FileName As NewFileName
End Sub
This works if I executed it with an isolated Access macro, but it doesn't works if I call it from inside the macro that creates the file to be renamed one step before.
Public Sub rinomina()
Dim FileName As String
Dim NewFileName As String
On Error Resume Next
FileName = "C:\SCONTRIN.INP"
NewFileName = "C:\SCONTRINO.INP"
Name FileName As NewFileName
End Sub
This works if I executed it with an isolated Access macro, but it doesn't works if I call it from inside the macro that creates the file to be renamed one step before.
Must it be an EXE file? Here's a simple vbscript (just paste it into Wordpad and then rename the saved file with .vbs extension):
Dim fso
SET fso = CreateObject("Scripting.FileSystemObject")
fso.MoveFile "C:\SCONTRIN.INP", "C:\SCONTRINO.INP"
Set FSO = Nothing
Msgbox "Finished renaming file."
Dim fso
SET fso = CreateObject("Scripting.FileSystemObject")
fso.MoveFile "C:\SCONTRIN.INP", "C:\SCONTRINO.INP"
Set FSO = Nothing
Msgbox "Finished renaming file."
dear jal, sounds very good.
right now I do not have the pc with my access database on, tomorrow I will check. The only doubt is that kind of lock that the macro that create the file seems to get till is over. Maybe that your simple vbs, called as external, could skip this problem. Hope so! Can you believe I used a Foxbase DOS small procedure (1980 stuff) to create a txt file to be sent to a cash counter to print the ticket? And the counter wait for a file named scontrino.inp and scontrino is 9 digits long, and DOS only accept 8 digits! That's why my need of renaming scontrin.inp to scontrino.inp.
right now I do not have the pc with my access database on, tomorrow I will check. The only doubt is that kind of lock that the macro that create the file seems to get till is over. Maybe that your simple vbs, called as external, could skip this problem. Hope so! Can you believe I used a Foxbase DOS small procedure (1980 stuff) to create a txt file to be sent to a cash counter to print the ticket? And the counter wait for a file named scontrino.inp and scontrino is 9 digits long, and DOS only accept 8 digits! That's why my need of renaming scontrin.inp to scontrino.inp.
I ran into a similar problem (a file lock) writing code in Visual Basic .Net. My own solution (don't know why it worked) was to flash up a popup form and immediately close it. Then someone gave me this code as an alternative (which also worked, but again I don't understand it).
GC.WaitForPendingFinalizers
GC.Colllect
Trouble is I doubt there is a VBA equivalent to this code. On the other hand "the popup form" idea can be tried, at least, in VBA, I would think.
