VBA - to select and open the correct file needed

[Starryblue]

Hi all,

As I have to compare two spreadsheets on a daily basis, I have automated the opening of the files to be copied and pasted into a working sheet. For one of the files, it will be chucked into a backup folder over the weekend, hence on Monday, when I had to do comparisons with the Friday's file, my script is unable to call out from the backup folder..can someone help?

My current script is as below:

'
***Workbooks.OpenText Filename:="C:\My docs\xyz.xls"

How do I make it so that on Mondays, it will open up the file from the backup folder? (above path is default ) TIA!

 

[bertie]

Hi and welcome to the board.

Here is a test routine set up for today, Wednesday.

[COLOR=darkblue]Sub[/COLOR] test()
   
   [COLOR=darkblue]If[/COLOR] Weekday(Now) = [COLOR=Red]vbWednesday[/COLOR] [COLOR=darkblue]Then[/COLOR]
      MsgBox "Wednesday"
   [COLOR=darkblue]Else[/COLOR]
      MsgBox "Another Day"
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
   
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

For your needs change vbWednesday to vbMonday.
And replace the message boxes with paths to the files.

 

[bertie]

Here is an alternative metohod for when the Monday is a holiday and the file is not processed.

This allows the user to select the file.

[COLOR=darkblue]Sub[/COLOR] test2()
   [COLOR=darkblue]Dim[/COLOR] sFile [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] wb [COLOR=darkblue]As[/COLOR] Workbook
   
   [COLOR=green]'in case no file is selected[/COLOR]
   [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
   
   sFile = Application.GetOpenFilename(sFile)
   [COLOR=darkblue]Set[/COLOR] wb = Workbooks.Open(sFile)
   
   [COLOR=green]'with wb[/COLOR]
   [COLOR=Green]'   do something[/COLOR]
   [COLOR=green]'end with[/COLOR]
   
   wb.Close SaveChanges:=[COLOR=darkblue]False[/COLOR]
   [COLOR=darkblue]Set[/COLOR] wb = [COLOR=darkblue]Nothing[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[Starryblue]

Hi bertie,

Thanks for your assistance, but that's not quite what I'm looking for. I actually want the file to be opened automatically instead of prompting..


Edited: It's okie, I think I got what you meant! Thanks dude!!! =)

 

[Starryblue]

Hi bertie,

Thanks for your assistance, but that's not quite what I'm looking for. I actually want the file to be opened automatically instead of prompting..


Edited: It's okie, I think I got what you meant! Thanks dude!!! =)

Oops sorry, one more question... What if I needed to take into consideration the public holidays as well..?

 

[bertie]

See post 3.

 

[Starryblue]

See post 3.

Does that allow taking into consideration all the public holidays throughout the year?? I mean I want to make it so that the script will auto detect the public holidays..

 

[bertie]

The problem you have to consider are that some holidays are not fixed and can change from year to year.

One approach could be to have a list of holidays in a seperate worksheet and loop through the list to determine if the date falls on a holiday and process files accordingly.

There is also a function in the Analysis Toolpack called Workdays you may find useful.
Tools => Add ins => Analysis Tool Pack

See this microsoft article.
http://msdn.microsoft.com/en-us/library/aa227592%28v=vs.60%29.aspx

 

[Starryblue]

Eek..I'm too much of a newbie to understand what's on the site..

If I were to have the dates of the Public Holidays, how can I include them so that the script can read (on top of the test routine from earlier)?

 

[Starryblue]

Eek..I'm too much of a newbie to understand what's on the site..

If I were to have the dates of the Public Holidays, how can I include them so that the script can read (on top of the test routine from earlier)?

Is anyone able to help me out here?? TIA...