Snasui Excel Forum

Code: Select all

With Workbooks("Main.xlsm").Worksheets("Rate_BOT")
    dayOfMth = VBA.Split(VBA.Mid(.Range("A1"), VBA.InStr(.Range("A1"), "as of") + 6), " ")
    Set rngCur = .Range("B:B").Find("SGD").Offset(0, 3)
End With

Code: Select all

Sub Run_rate()

Dim dayOfMth As Variant, dataAll As Range
Dim rngCur As Range, r As Range
With Workbooks("Year 2023.xlsx").Worksheets("AVG_SGD")
    Set dataAll = .Range("B3:M33")
End With
With Workbooks("Main.xlsm").Worksheets("Rate_BOT")
    dayOfMth = VBA.Split(VBA.Mid(.Range("A1"), VBA.InStr(.Range("A1"), "as of") + 6), " ")
    Set rngCur = .Range("B:B").Find("SGD").Offset(0, 3)
End With
For Each r In dataAll
    If r.Parent.Cells(r.Row, "A").Value = "Day " & dayOfMth(0) And _
        Application.Text(r.Parent.Cells(2, r.Column), "[$- ]mmmm") = dayOfMth(1) Then
        r.Value = rngCur.Value
        Exit For
    End If
Next r
   
   
'Dim dayOfMth As Variant, dataAll As Range
'Dim rngCur As Range, r As Range
With Workbooks("Year 2023.xlsx").Worksheets("AVG_USD")
    Set dataAll = .Range("B3:M33")
End With
With Workbooks("Main.xlsm").Worksheets("Rate_BOT")
    dayOfMth = VBA.Split(VBA.Mid(.Range("A1"), VBA.InStr(.Range("A1"), "as of") + 6), " ")
    Set rngCur = .Range("B:B").Find("USD").Offset(0, 3)
End With
For Each r In dataAll
    If r.Parent.Cells(r.Row, "A").Value = "Day " & dayOfMth(0) And _
        Application.Text(r.Parent.Cells(2, r.Column), "[$- ]mmmm") = dayOfMth(1) Then
        r.Value = rngCur.Value
        Exit For
    End If
Next r
   
    Windows("Year 2023.xlsx").Activate


End Sub

Code: Select all

ie.navigate "https://www.bot.or.th/thai/statistics/financialmarkets/exchangerate/exchangerate_EN_PDF/ER_PDF_" & Format(l, "ddmmyyyy") & ".PDF"

Code: Select all

     ie.navigate "https://www.bot.or.th/thai/statistics/financialmarkets/exchangerate/exchangerate_EN_PDF/ER_PDF_" & l & ".PDF"

Code: Select all

 
Dim l As Long
k = 2
Do Until Workbooks("Base_WD.xlsx").Worksheets("WorkingDay").Cells(k, 7).Value <> ""
k = k + 1
Loop
l = Workbooks("Base_WD.xlsx").Worksheets("WorkingDay").Cells(k - 1, 1).Value2
    
    Dim ie As Object, s As String, i As Integer
    Select Case Weekday(Date, vbMonday)
        Case 7
            i = -2
        Case 1
            i = -3
        Case Else
            i = -1
    End Select
    s = Format(DateAdd("d", i, Date), "ddmmyyyy")
    
    Set ie = CreateObject("Internetexplorer.Application")
    ie.Visible = True
    ie.navigate "https://www.bot.or.th/thai/statistics/financialmarkets/exchangerate/exchangerate_EN_PDF/ER_PDF_" & l & ".PDF"
    
    MsgBox " finished "
    
End Sub
 

Code: Select all

Dim l as Long
k = 2
Do Until Workbooks("Base_WD.xlsx").Worksheets("WorkingDay").Cells(k, 7).Value <> ""
   k = k + 1
Loop
l = Workbooks("Base_WD.xlsx").Worksheets("WorkingDay").Cells(k-1, 1).Value2

Code: Select all

k = 2
Do Until Workbooks("Base_WD.xlsx").Worksheets("WorkingDay").Cells(k, 7).Value <> ""
  k = k + 1
Loop