Import Web Data to Excel using VBA Macros_2

0

Thank you for your prompt reply.

But it's not just about the stock quotes I need, it's about constantly updating the website. I do NOT want to use the excel function (update connection) because it interferes with this funktion. I'm looking for a VBA solution for this question.

For example, I can create a commmand button which I can click on automatically with the Ghoast Mouse (tool) every minute to start the web query. But that's not smart, of course. A loop function with VBA is what I'm looking for. The cells must be overwritten after a refrech each time.

The current VAB code is very good. Only the automatic Refresch I miss.

For a customized code, I would be very grateful

Answer
Discuss

Answers

0

The system you are looking for consists of a total of 5 procedures plus a publicly declared variable of double type, to hold a time value. The following 3 procedures (and the public variable) should be in a standard code module, Module1 in the attached workbook.

Option Explicit

    Public RunWhen As Double

Sub StartTimer()
    ' May 01, 2019

    RunWhen = Now + TimeSerial(0, 1, 0)             ' set hours, minutes, seconds
    Application.OnTime EarliestTime:=RunWhen, Procedure:="MyProcedure", Schedule:=True
End Sub

Sub StopTimer()
    ' May 01, 2019

   On Error Resume Next
   Application.OnTime EarliestTime:=RunWhen, Procedure:="MyProcedure", Schedule:=False
End Sub

Sub MyProcedure()
    ' May 01, 2019

    Sheet1.Cells(1, 1).Value = Now()
    Call StartTimer
End Sub

The other two procedures, listed below, must be pasted to the pre-existing class module called ThisWorkbook.

Private Sub Workbook_Open()
    ' May 01, 2019

    MyProcedure
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ' May 01, 2019

    StopTimer
End Sub

The work horse in the system is the procedure called MyProcedure. You can change both its name and its function. In this example the current time is written to cell A1 of Sheet1 in the ActiveWorkbook. Observe that the sheet is addressed by its CodeName which doesn't change when the tab is renamed. You might use the tab name instead: ThisWorkbook.Worksheets("Sheet1").Cells(1, 1).Value = Now()

Sub Workbook_Open runs automatically when the workbook is opened. It calls the procedure MyProcedure which updates A1 (you might use Range("A1") instead of Cells(1,1)) and then calls the procedure StartTimer in its last line. This procedure sets the application's OnTime method to run MyProcedure again after one minute. You can set a different interval or call a procedure of another name. 

This loop, MyProcedure - StartTime - MyProcedure - StartTime - repeats indefinitely. It would even repeat after closing the workbook. Hence the Sub Workbook_BeforeClose which runs automatically when the workbook is closed. It calls the procedure StopTimer which ends the loop. Be sure that it specifies the same procedure (here "MyProcedure") as StartTimer. Technically, StartTimer could be a Private procedure in the ThisWorkbook module because it isn't called anywhere else. In the attached working sample workbook I placed it in the standard code module next to the StartTimer procdure because they are a logical pair.

Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login