Declaring dynamic variable using if then

[smg]

Hello,

I have a problem with declaring a variable which I want to use later in my macro. The problem is that I need to first "calculate" this variable which is dependent on the file name.
I used "if then else" statement to pull out from the file name one thing that changes - week number. The statement is used to differentiate between one-digit (Week 1) and two-digit week number (Week 10).

The week number affects directory of the files which I'd like to make dynamic. For example, if one opens a file already named with week 11 and run the macro, macro will automatically choose the week-11 folder to get the files from it and extract it to the active workbook.

Dim week As String
Dim wrkb As String

wrkb = ThisWorkbook.Name

If InStr(1, wrkb, "-") = 25 Then
    week = Mid(wrkb, 17, 7)
    Else
    week = Mid(ThisWorkbook.Name, 17, 6)
    End If

How should I end this statement to get the final value of the week and be able to use it later in the macro?

Thank you in advance

Slawek

 

[wigi]

This should work:

Dim week As String
Dim wrkb As String

wrkb = ThisWorkbook.Name

If InStr(1, wrkb, "-") = 25 Then
    week = Mid(wrkb, 17, 7)
Else
    week = Mid(wrkb, 17, 6)
End If

 

[smg]

Thanks wigi for your answer.


Yep, it works but the main problem was in "ThisWorkbook". I've changed it to "ActiveWorkbook" and it works. Earlier excel was using macro workbook ("Personal" + file extension).


Thanks for your help.

Cheers
Slawek