Excel VBA macro open file with data import for specific date

Question

I want to create a macro that is able to open htm file that has the correct name. All the htm files are named only after the date. So i want the macro only to open and import the data of the htm file with the correct date. So for example i want the macro only import the data of the date today. My idea was that i specify in one cell which date i want to get imported.

Dim file As Variant file = ThisWorkbook.Sheets(5).Range("B17").Value

So here i thought i refer to the cell where i specify the date.

With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;file:///C:/Users.....
    Destination:=Range("$A$1"))

But here i am not sure how to import the date value into the link for the file?

In the end i want the link to look like

"URL;file:///C:/Users.../11.04.2017"

ASH ASH · Accepted Answer · 2017-04-12T20:21:27

All you have to do is concatenate the value from some object, such as a cell on a sheet, for instance. Here I am passing the value from Cell E1 into the URL string.

"URL;https://finance.yahoo.com/quote/IBM/financials?p=" & Range("E1").Value, Destination:= _
        Range("$A$1"))

You can pass values from an InputBox, a TextBox, a ComboBox....pretty much anyt kind of object.

Excel VBA macro open file with data import for specific date

1 Answers