• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 316
  • Last Modified:

Rename a excel file via VB script

Hi,

I have a question on VB script
I want to rename a excel file but the problem is that every day the source excel name is changed where as Destination excel name need to be constant

I used below code:
 
Dim Fso
Set Fso = WScript.CreateObject("Scripting.FileSystemObject")
Fso MoveFile "D:\test\Databank\ResultsFile_5_Jan.xls", "D:\destination\Result2\Result.xls"

but on another day ResultsFile_5_Jan.xls changed to ResultsFile_6_Jan.xls  and so on
ResultsFile_7_Jan.xls


I tried with regular expressions and it is not working like:

Fso MoveFile "D:\test\Databank\ResultsFile*.xls", "D:\destination\Result2\Result.xls"

Please suggest
0
theology
Asked:
theology
  • 3
  • 2
1 Solution
 
Rgonzo1971Commented:
Hi,

How do determine which file is to te moved?

Is it the only one in the Folder?

Is it the newest file?

Is it the actual date?

Regards
0
 
Rgonzo1971Commented:
Hi,

if it is today pls try

Sub macro()

Dim filesys
Dim strTodayFile 
 Set filesys = CreateObject("Scripting.FileSystemObject")
 strTodayFile = "D:\test\Databank\ResultsFile_" & Format(Date, "d_mmm") & ".xls"
 If filesys.FileExists(strTodayFile) Then
 filesys.MoveFile strTodayFile, "D:\destination\Result2\Result.xls"

 End If
End Sub

Open in new window

EDIT

you can find the newest file with a function like this

Function fNewestResultsFile() as String
    Dim fso, fldr, fls, f
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fldr = fso.GetFolder("D:\test\Databank\")
    Set fls = fldr.Files
    LastDate = #1/1/1900#
    For Each f In fls
        If f.Name Like "ResultsFile*" Then
            If f.DateCreated > LastDate Then
                NewestFile = f.Path
                LastDate = f.DateCreated
            End If
        End If
    Next
    fNewestFile = NewestFile
End Function

Open in new window

Regards
0
 
theologyAuthor Commented:
Thanks
it is "Is it the only one in the Folder?"

 I tried your sub macro

can it be some other way to code it because for me sub macro is not working ..

thanks
0
 
Rgonzo1971Commented:
Hi,
pls try

Dim filesys
Dim strTodayFile 
 Set filesys = CreateObject("Scripting.FileSystemObject")
 FileName = Dir("D:\test\Databank\ResultsFile_*.xls")
 If FileName  <> "" Then
    filesys.MoveFile "D:\test\Databank\" & FileName, "D:\destination\Result2\Result.xls"
 End If

Open in new window

0
 
theologyAuthor Commented:
thanks its working
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now