Solved

Rename a excel file via VB script

Posted on 2014-01-05
5
299 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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying 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

Suggested Solutions

Title # Comments Views Activity
Windows Server 2016 Terminal Server - can't change DPI 2 199
80072efd error while checking for updates. 20 87
Need Remote Registry Editor 3 44
Program files permissions 1 40
OfficeMate Freezes on login or does not load after login credentials are input.
Possible fixes for Windows 7 and Windows Server 2008 updating problem. Solutions mentioned are from Microsoft themselves. I started a case with them from our Microsoft Silver Partner option to open a case and get direct support from Microsoft. If s…
This Micro Tutorial will give you a introduction in two parts how to utilize Windows Live Movie Maker to its maximum capability. This will be demonstrated using Windows Live Movie Maker on Windows 7 operating system.
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…

736 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