Edit Replace VBA

[odonnke]

Hello all, could anyone help me with this one, I have a macro that is run daily. the file is dropped down from our server and updated with the new date AR010405.xls The macro is currently edited manually with an edit replace in VB
ie edit replace AR010405.xls to AR010505.xls.

I would like to know a couple things,
1- at the end of the macro I would like this string to be update with the new date ie 010505
2- put this macro on self timer so that it launches every morning after the file is dropped down.
Workbooks.Open Filename:="K:\AR010405.xls"

the macro is in a seperate workbook but same network location
the whole purpose for the macro is to take a raw data file and format it for users

your help would be greatly appreciated!

 

[Smitty]

Welcome to the Board!

You might be able to use something like this to adjust the date/filename:

Sub Test()
    Dim NewDate As String
        NewDate = Format(Date, "mmddyy")
    
    Workbooks.Open "K:\AR" & NewDate & ".XLS"

End Sub

As for running it automatically, you can put your code in a Workbook_Open event. You can then use the Windows Scheduler to automatically open the workbook at a specified time each day.

Hope that helps,

Smitty

 

[odonnke]

Thanks, would I use the same code to open a file from the day before as well?
ie. I open todays file and yesterdays file and update yesterdays info onto todays file, obviously both dates need to change in the macro.

Workbooks open event, not familiar with this one could you explain?
also do you mean schedule a task?

thanks a bunch

 

[Smitty]

Just add a bit to get yesterday's date:

Sub Test()
    Dim NewDate As String
    Dim OldDate As String
        NewDate = Format(Date, "mmddyy")
        OldDate = Format(Date - 1, "mmddyy")
    
    Workbooks.Open "K:\AR" & NewDate & ".XLS"
    '   Do something
    Workbooks.Open "K:\AR" & OldDate & ".XLS"
    '   Do something else
End Sub

As for Workbook_Open event, it's invoked whenever you open a workbook. It's a great way to automate certain things. The VBA helpfile documents it pretty well.

As for Schedule, I did meant the Windows Task Scheduler, sorry.

Smitty

 

[odonnke]

U DA MAN

Thanks !
Will test this on Monday.