Justincut
asked on
What is the code for Business Day in VB code
Hi Guys, I currently have a Macro that imports a text file with the Previous Business day in the title. I use this code by naming a range in Excel with the Formula =IF(Now(Weekday (Now)=2, Now-3,Now-1). How can I substitute this in VB code? Here's my current code:
The Range ("AM1") IS THE pREVIOUS wORK DAY FORMULA
Dim target As Range, target1 As Range, target2 As Range, target3 As Range, target4 As Range, target5 As Range, target6 As Range, target7 As Range, target8 As Range, target9 As Range, target10 As Range, target11 As Range
Dim PrevDay, Prevday2 As String
PrevDay = Worksheets("Rec").Range("A M1").Value
PrevDay = Format(PrevDay, "DDMMYY")
Prevday2 = Worksheets("Rec").Range("A M1").Value
Prevday2 = Format(Prevday2, "YYYYMMDD")
Workbooks.OpenText Filename:= _
"V:\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS\StructNotesBSRec_D aily_" & "*.txt" _
, Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=Fals e, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15 _
, 1), Array(16, 1), Array(17, 1), Array(18, 1)), TrailingMinusNumbers:=True
Workbooks.OpenText Filename:= _
The Range ("AM1") IS THE pREVIOUS wORK DAY FORMULA
Dim target As Range, target1 As Range, target2 As Range, target3 As Range, target4 As Range, target5 As Range, target6 As Range, target7 As Range, target8 As Range, target9 As Range, target10 As Range, target11 As Range
Dim PrevDay, Prevday2 As String
PrevDay = Worksheets("Rec").Range("A
PrevDay = Format(PrevDay, "DDMMYY")
Prevday2 = Worksheets("Rec").Range("A
Prevday2 = Format(Prevday2, "YYYYMMDD")
Workbooks.OpenText Filename:= _
"V:\Treasury Finance Controls\Ledger v SS Recs\EOD Recs\BS\StructNotesBSRec_D
, Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=Fals
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15 _
, 1), Array(16, 1), Array(17, 1), Array(18, 1)), TrailingMinusNumbers:=True
Workbooks.OpenText Filename:= _
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.