Solved

Rename a excel file via VB script

Posted on 2014-01-05
5
303 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 51

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 51

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 51

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Question has a verified solution.

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

You may have a outside contractor who comes in once a week or seasonal to do some work in your office but you only want to give him access to the programs and files he needs and keep privet all other documents and programs, can you do this on a loca…
The Windows functions GetTickCount and timeGetTime retrieve the number of milliseconds since the system was started. However, the value is stored in a DWORD, which means that it wraps around to zero every 49.7 days. This article shows how to solve t…
The viewer will learn how to successfully create a multiboot device using the SARDU utility on Windows 7. Start the SARDU utility: Change the image directory to wherever you store your ISOs, this will prevent you from having 2 copies of an ISO wit…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

691 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