Alternative to if then

[Adar123]

Hi everyone,

I am using the following code to download data from a folder which is filled with files on a regular basis.

The files are supplied from Tuesday to Saturday, so on Monday I need to point not to DataX_CM_FUT1_" & dYes & "_" & dTod & "_000501UTC.csv" but to DataX_CM_FUT1_" & dFriday & "_" & dSaturday & "_000501UTC.csv"

I could create a parameter to say if today is Monday, then do this, otherwise do that. But this type of logic is a floodgate once there is more than two options to consider. Appreciate any 'smarter' solutions that will facilitate shorter code. Thank you.

Sub copyColDataOld()

Dim lastRow As Long
Dim myApp As Excel.Application
Dim wkBk As Workbook
Dim wkSht As Object

Dim dTod As String, dYes As String

dTod = Format(Date, "yyyy-mm-dd")
dYes = Format(Date - 1, "yyyy-mm-dd")

Set myApp = CreateObject("Excel.Application")
Set wkBk = myApp.Workbooks.Open("Dump\DataX_CM_FUT1_" & dYes & "_" & dTod & "_000501UTC.csv")

lastRow = wkBk.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
wkBk.Sheets(1).Range("C2:H" & lastRow).Copy

myApp.DisplayAlerts = False
wkBk.Close
myApp.Quit
Set wkBk = Nothing
Set myApp = Nothing
Set wkBk = ActiveWorkbook
Set wkSht = wkBk.Sheets("NewData")
wkSht.Activate
Range("A1").Select
wkSht.Paste

End Sub

 

[Logit]

.
What about a SELECT CASE statement ?

 

[Adar123]

Is this what you are proposing?

    Select Case Format(Now(), "DDD") = "MON"
        Case vbMon
            Set wkBk = myApp.Workbooks.Open("Monday NAME")
        Case vbNonMon
           Set wkBk = myApp.Workbooks.Open("NonMonday NAME")
    End Select

 

[Logit]

Yes. Does it work ?

 

[Joe4]

Format(Now(), "DDD") = "MON"
will actually return True or False.

So your code should look something like this:

    Select Case Format(Now(), "DDD") = "Mon"
        Case True
            Set wkBk = myApp.Workbooks.Open("Monday NAME")
        Case False
            Set wkBk = myApp.Workbooks.Open("NonMonday NAME")
    End Select

Note that upper/lower case matters in this case, so you want to use "Mon" instead of "MON".

Of course, that could all be simplified to:

If Format(Now(), "DDD") = "Mon" Then
    Set wkBk = myApp.Workbooks.Open("Monday NAME")
Else
    Set wkBk = myApp.Workbooks.Open("NonMonday NAME")
End If

 

[Adar123]

Thanks for pointing the Case sensitive issue. Both options work.

 

[Joe4]

You are welcome.
Glad it works for you!