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]

Sorry for taking so long to reply. I have had a power cut due to the high winds we have been having in Scotland.

I won't use the Workdays function because I don't know whether or not you intend to distrubute your worksheet, your users would need to install the add-in.

I will keep it relatively simple and loop through a list of holidays.In an empty sheet set up a list of holidays. I have used sheet2

Excel 2003<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px"><colgroup><col style="background-color: #E0E0F0" width="25px"><col><col></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th>
</th><th>A</th><th>B</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style=";">Holiday</td><td style=";">Date</td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style=";">aaa</td><td style="text-align: right;;">21/05/2011</td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style=";">bbb</td><td style="text-align: right;;">22/05/2011</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style=";">ccc</td><td style="text-align: right;;">23/05/2011</td></tr></tbody></table>

Sheet2​

The first thing the code does is check for a Monday.

   [COLOR=green]'=================[/COLOR]
   [COLOR=green]'check for monday[/COLOR]
   [COLOR=green]'=================[/COLOR]
   [COLOR=darkblue]If[/COLOR] Weekday(Now) = vbMonday [COLOR=darkblue]Then[/COLOR]
      sFilename = "C:\My docs\backup\xyz.xls"
      [COLOR=darkblue]Set[/COLOR] wb = Workbooks.Open(sFilename)
   [COLOR=darkblue]Else[/COLOR]

Then it checks for holidays against your list.
NB Note how the format of the dates have to match.
Increase the loop range if necessary.

      [COLOR=green]'=====================[/COLOR]
      [COLOR=green]'check holiday list[/COLOR]
      [COLOR=green]'=====================[/COLOR]
      dteDate = Format(Now - 1, [COLOR=Red]"dd/mm/yyyy"[/COLOR])
      [COLOR=darkblue]For[/COLOR] i =[COLOR=Red] 2 [/COLOR][COLOR=Red]To 10[/COLOR]
         dteTemp = Format(Sheets("Sheet2").Range("B" & i).Value, [COLOR=Red]"dd/mm/yyyy")[/COLOR]
         
         [COLOR=darkblue]If[/COLOR] dteTemp = dteDate [COLOR=darkblue]Then[/COLOR]
            sFilename = "C:\My docs\backup\xyz.xls"
            [COLOR=darkblue]Set[/COLOR] wb = Workbooks.Open(sFilename)
            [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]For[/COLOR]
         [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
      [COLOR=darkblue]Next[/COLOR] i

And finally it would open the file for a normal day.

     [COLOR=green]'=======================[/COLOR]
      [COLOR=green]'process normal day[/COLOR]
      [COLOR=green]'=======================[/COLOR]
      [COLOR=darkblue]If[/COLOR] sFilename = "" [COLOR=darkblue]Then[/COLOR]
         sFilename = "C:\My docs\xyz.xls"
         [COLOR=darkblue]Set[/COLOR] wb = Workbooks.Open(sFilename)
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
   End If

The fulll code is shown below.

[COLOR=darkblue]Sub[/COLOR] test()
   [COLOR=green]'==================================================[/COLOR]
   [COLOR=green]'I have assumed this is scheduled to run every day[/COLOR]
   [COLOR=green]'===================================================[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] dteDate [COLOR=green]'As Date[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] dteTemp 'As Date
   [COLOR=darkblue]Dim[/COLOR] sFilename [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] wb [COLOR=darkblue]As[/COLOR] Workbook
   [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
      
   [COLOR=green]'=================[/COLOR]
   [COLOR=green]'check for monday[/COLOR]
   [COLOR=green]'=================[/COLOR]
   [COLOR=darkblue]If[/COLOR] Weekday(Now) = vbMonday [COLOR=darkblue]Then[/COLOR]
      sFilename = "C:\My docs\backup\xyz.xls"
      [COLOR=darkblue]Set[/COLOR] wb = Workbooks.Open(sFilename)
   [COLOR=darkblue]Else[/COLOR]
      [COLOR=green]'=====================[/COLOR]
      [COLOR=green]'check holiday list[/COLOR]
      [COLOR=green]'=====================[/COLOR]
      dteDate = Format(Now - 1, "dd/mm/yyyy")
      [COLOR=darkblue]For[/COLOR] i = 2 [COLOR=darkblue]To[/COLOR] 10
         dteTemp = Format(Sheets("Sheet2").Range("B" & i).Value, "dd/mm/yyyy")
         
         [COLOR=darkblue]If[/COLOR] dteTemp = dteDate [COLOR=darkblue]Then[/COLOR]
            sFilename = "C:\My docs\backup\xyz.xls"
            [COLOR=darkblue]Set[/COLOR] wb = Workbooks.Open(sFilename)
            [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]For[/COLOR]
         [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
      [COLOR=darkblue]Next[/COLOR] i
      
      [COLOR=green]'=======================[/COLOR]
      [COLOR=green]'process normal day[/COLOR]
      [COLOR=green]'=======================[/COLOR]
      [COLOR=darkblue]If[/COLOR] sFilename = "" [COLOR=darkblue]Then[/COLOR]
         sFilename = "C:\My docs\xyz.xls"
         [COLOR=darkblue]Set[/COLOR] wb = Workbooks.Open(sFilename)
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
   
   [COLOR=green]'===================[/COLOR]
   [COLOR=green]'YOUR CODE GOES HERE[/COLOR]
   [COLOR=green]'===================[/COLOR]
   [COLOR=green]'with wb[/COLOR]
   '   rest of your code geos here
   [COLOR=green]'end with[/COLOR]
   
   wb.Close SaveChanges:=[COLOR=darkblue]True[/COLOR]
   [COLOR=darkblue]Set[/COLOR] wb = [COLOR=darkblue]Nothing[/COLOR]
End [COLOR=darkblue]Sub[/COLOR]