Solved

Rename a excel file via VB script

Posted on 2014-01-05
5
292 Views
Last Modified: 2014-01-06
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
Comment
Question by:theology
  • 3
  • 2
5 Comments
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 39757245
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
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 39757256
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
 

Author Comment

by:theology
ID: 39757492
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
 
LVL 49

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39758583
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
 

Author Closing Comment

by:theology
ID: 39759524
thanks its working
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Citrix XenApp, Internet Explorer 11 set to Enterprise Mode and using central hosted sites.xml file.
When you try to extract and to view the contents of a Microsoft Update Standalone Package (MSU) for Windows Vista, you cannot extract the files from the MSU. Here we are going to explain how to extract those hotfix details without using any third pa…
This Micro Tutorial will give you a basic overview of Windows DVD Burner through its features and interface. This will be demonstrated using Windows 7 operating system.
Windows 8 came with a dramatically different user interface known as Metro. Notably missing from that interface was a Start button and Start Menu. Microsoft responded to negative user feedback of the Metro interface, bringing back the Start button a…

785 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