?
Solved

Rename a excel file via VB script

Posted on 2014-01-05
5
Medium Priority
?
308 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 52

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 52

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 52

Accepted Solution

by:
Rgonzo1971 earned 2000 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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

If you get continual lockouts after changing your Active Directory password, there are several possible reasons.  Two of the most common are using other devices to access your email and stored passwords in the credential manager of windows.
By default the complete memory dump option is disabled in windows . If we want to enable the complete memory dump for a diagnostic purpose, we have a solution for it. here we are using the registry method to enable this.
Windows 8 comes with a dramatically different user interface known as Metro. Notably missing from the new interface is a Start button and Start Menu. Many users do not like it, much preferring the interface of earlier versions — Windows 7, Windows X…
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…
Suggested Courses

777 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