Use a macro to edit VBA code

[mgrekstas]

I wrote a code to pull a table from a website. The website has a calendar feature and part of the web address can be edited to pull the correct table based on the preferred date. I tried to create a macro that deletes the current table, copies a cell for a new date, opens the code and pastes the date, and then runs the code to populate the new table. However, the macro won't produce the table. Is there a better way to use a macro to edit code, or is there a way to make my code dynamic and change the web address based on the date in the first place?

Code to pull table:

Sub GetWebTable()
Dim URL As String
URL = "http://www.wsj.com/mdc/public/page/2_3023-fut_metal-futures-20170905.html?mod=mdc_pastcalendar"


With ActiveSheet.QueryTables.Add(Connection:="URL;" & URL, Destination:=Range("A1"))
.WebTables = "8"
.Refresh
End With


End Sub

Macro:

Sub Test2()
'
' Test2 Macro
'


'
Columns("A:J").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("A1").Select
Selection.Copy
Application.CutCopyMode = False
Application.Run "Book3!GetWebTable"
Selection.QueryTable.Refresh BackgroundQuery:=False
End Sub

 

[JackDanIce]

Welcome to the board.

This worked for me, by changing the value in blue to any date of format "YYYYMMDD", replace all of your code and try:

Sub Test2()
        
    Dim mydate  As String
    mydate = "20170905"                

    GetWebTable mydate
                    
    MsgBox "Refreshed Data for date: " & mydate, vbOKCancel, "Data refreshed"
    
End Sub


Private Sub GetWebTable(ByRef str As String)

    Application.ScreenUpdating = False

    With ActiveSheet.QueryTables.add(Connection:="URL;" & myURL(str), Destination:=Range("A1"))
        .RefreshStyle = xlDeleteCells
        .WebTables = "8"
        .Refresh BackgroundQuery:=False
    End With
    
    Application.ScreenUpdating = True
    
End Sub

Private Function myURL(ByRef strDate As String) As String

    myURL = Replace("http://www.wsj.com/mdc/public/page/2_3023-fut_metal-futures-@DATE.html?mod=mdc_pastcalendar", "@DATE", strDate)
    
End Function

 

[mgrekstas]

Thank you for your help! Is it possible to make the date in blue a cell reference?

 

[JackDanIce]

What cell reference and what is the sheet name for that cell?

 

[D3allamerican07]

mgrekstas,

You could make the code dynamic by using something as simple as this:

todaysDate = ActiveSheet.Range("A1").Value
URL = "http://www.wsj.com/mdc/public/page/2_3023-fut_metal-futures-" & todaysDate & ".html?mod=mdc_pastcalendar"

Let me know if this works for you.

Bill

 

[mgrekstas]

Sheet is "Overview"
Cell reference is A2

 

[JackDanIce]

Try:

Sub Test2()

    With Sheets("Overview").Range("A2")
          GetWebTable .Value
          MsgBox "Refreshed Data for date: " & .Value, vbOKCancel, "Data refreshed"
     End With

End Sub

Private Sub GetWebTable(ByRef str As String)
'Refresh web data with function to generate variable date value

    Application.ScreenUpdating = False

    With ActiveSheet.QueryTables.add(Connection:="URL;" & myURL(str), Destination:=Range("A1"))
        .RefreshStyle = xlDeleteCells
        .WebTables = "8"
        .Refresh BackgroundQuery:=False
    End With
    
    Application.ScreenUpdating = True
    
End Sub

Private Function myURL(ByRef strDate As String) As String
'Function to generate URL with adjusting date value

    myURL = Replace("http://www.wsj.com/mdc/public/page/2_3023-fut_metal-futures-@DATE.html?mod=mdc_pastcalendar", "@DATE", strDate)
    
End Function

 

[mgrekstas]

I tried using that in the code but it wouldn't bring up the table. Do you see any error in my code?

Sub Test2()

Dim mydate As String
todaysDate = ActiveSheet.Range("L1").Value

Application.ScreenUpdating = False

With ActiveSheet
.Cells(1, 1).Resize(, 10).EntireColumn.Delete shift:=xlToLeft
Call GetWebTable(mydate)
Selection.QueryTable.Refresh BackgroundQuery:=False
End With

Application.ScreenUpdating = True

MsgBox "Refreshed Data for date: " & mydate, vbOKCancel, "Data refreshed"

End Sub


Private Sub GetWebTable(ByRef str As String)


With ActiveSheet.QueryTables.Add(Connection:="URL;" & myURL(str), Destination:=Range("A1"))
.WebTables = "8"
.Refresh
End With

End Sub


Private Function myURL(ByRef strDate As String) As String


Url = "http://www.wsj.com/mdc/public/page/2_3023-fut_metal-futures-" & todaysDate & ".html?mod=mdc_pastcalendar"

End Function

 

[JackDanIce]

I think you're mixing code and suggestions up.

Either apply @D3allamerican07's suggestion to your original code only or replace all of your code with that in reply #7 and then test