Vba Download data file only on working days-CodePudding

I have a problem with correct formatting or method that I should use.

My program:

dtReportDateStart = Format(ThisWorkbook.Worksheets("Dashboard").Range("C2"), "YYYYMMDD")

dtReportDateEnd = Format(ThisWorkbook.Worksheets("Dashboard").Range("C3"), "YYYYMMDD")

CurrentDate = dtReportDateStart

Do While CurrentDate <> dtReportDateEnd

    Location = "http://." & CurrentDate & ".tsv.txt"

    CurrentDate = CurrentDate   1
                  
Loop

It should take two dates in "YYYYMMDD" format and then work for working days only. Right now it gives an error whenever we have a weekend. The reason why CurrentDate should be in "YYYYMMDD" format is that it downloads values from a data base (Location) which requires exactly that type of formatting.

My ideas:

(1) I printed working days on Dashboard in cells F, starting from F1 and the code could move from cell to cell.

(2) Change data formatting to use WEEKDAY function, If Weekday(CurrentDate, vbMonday) < 6 Then [...]

But in both cases I don't know how to write it

CodePudding user response：

Try using the next function, please:

Function isWD(dDbl As Double) As Boolean
     Dim d As Date: d = DateSerial(left(dDbl, 4), Mid(dDbl, 5, 2), Right(dDbl, 2))

     If Weekday(d, vbMonday) < 6 Then isWD = True
End Function

Then use it in your code as:

Do While CurrentDate <> dtReportDateEnd
  If isWD(CDbl(CurrentDate)) Then
    Location = "http://." & CurrentDate & ".tsv.txt"
  End If
    CurrentDate = CurrentDate   1                  
Loop ```