CODEWITHSUNDEEP
excelvbaloops
Kael
Kael

Reputation: 11

Pause loop in Excel VBA until another Sub is finished

I'm trying to write a macro to achieve the following:

I have achieved the first two steps, but I can't find a way to loop it so that it then downloads the rest of the symbols. It seems that all my attempts at looping either:

My code works fine as long as I don't have the For ... Next i loop, but as soon as I add that it navigates to the site but when prompted to open the CSV file it doesn't get opened.

Below is a simplified example of my code. I navigate to Google to simulate the login step (which should only happen once). Then I navigate to the actual address of the CSV, and then call on the MoveIt Sub to place it where I want it.

As it is it works fine, but the moment I add the For...Loop (Currently marked as comment in the code), it stops opening the file.

I also tried a Do.While loop in the first Sub (also marked as comment) so that it wouldn't do anything until the second Sub is done, but it also doesn't work.

Public i As Integer, j As Integer

Sub GetHistoricalCSV()

Dim ieApp As InternetExplorer
Dim web As String

i = 1


Set ieApp = New InternetExplorer 'create a new instance of ie
ieApp.Visible = True

ieApp.Navigate "https://google.com" 'go to login page
    Do While ieApp.Busy: DoEvents: Loop
    Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop

'For i = 1 To 3 'This is the start of the loop

j = 0

web = "http://www.global-view.com/forex-trading-tools/forex-history/exchange_csv_report.html?CLOSE_" & i & "=ON&start_date=09/16/2018&stop_date=09/16/2018&Submit=Get Daily Stats"

ieApp.Navigate web

    'Wait to make sure IE has navigated and is prompting to open or save the CSV
    Application.Wait (Now + TimeValue("0:00:03"))
    SendKeys "(%o)" 'Open the CSV


    'I need to wait for the file to open, so I schedule the rest of the code on a new sub
    Application.OnTime Now() + TimeSerial(0, 0, 1), "MoveIt"

'Do Until i = j 'This is telling the code to wait until then next sub has finished
'    DoEvents
'Loop

'Next i 'This is the end of the loop

End Sub

The next Sub moves the opened sheet to a workbook named "Text.xlsx"

Public Sub MoveIt()
Dim Ct As Integer
Ct = 0
For Each wb In Application.Workbooks
    If wb.Name Like "exchange*" Then
        Ct = Ct + 1
        wb.Activate
        Set wbook = ActiveWorkbook
        Exit For
    End If
Next wb

If Ct = 0 Then MsgBox "File not open"

ActiveSheet.Move After:=Workbooks("Test.xlsx").sheets(Workbooks("Test.xlsx").sheets.Count)
ActiveSheet.Name = "Exchange" & i

j = i

End Sub

Upvotes: 0

Views: 1115

Answers (2)

Patrick Powell
Patrick Powell

Reputation: 1

Have you looked into using Get & Transform to solve your problem?

It looks like you may need to set up some parameters based on the dates, but that can be done fairly easily. I struggled to figure out a solution to this for awhile, found some help on reddit and ended up with the following query in this Workbook on GitHub to pull information from the iex API. 

// If you need more context see the workbook posted above
let
  Parameter = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],
  URL = Parameter{0}[Value],
  Source = Json.Document(Web.Contents(URL)),
  #"Converted to Table" = Record.ToTable(Source)
in
  #"Converted to Table"

Let me know if you're able to use this or if you need any more help!

Upvotes: 0

QHarr
QHarr

Reputation: 84465

As you are not selecting an element to initiate the download you should be able to try a binary file download/urlmon download using your constructed url direct. Provided you have already logged in earlier in the code a sessionid/cookie will hopefully be in place and still valid. Use a function to download that returns the download full path and collect those to later open downloaded files.

Upvotes: 0

Related Questions