Sub Procedures Working Independently but NOT Consecutively

[mjb123]

I currently have two sub procedures:

[1] The first sub procedure logs in to a password protected website, navigates that website, clicks a link that download's a *.csv file, opens the *.csv file, logs out of the website, and then closes Internet Explorer.

[2] The second sub procedure figures out which of the open workbooks contains the *.csv file information, determines how many rows and columns of data are in Sheet1 of that file, and then copies all of the information to the original Excel spreadsheet.

If I run the first sub procedure and then the second sub procedure, one at a time, the application works as intended. However, if I create a sub procedure that calls the two sub procedures consecutively the second sub procedure won't execute because it doesn't realize that the *.csv file is open because it doesn't open until I manually press ESC to exit the VBA code.

I thought that by employing Application.Wait I could get around this problem. However, when I do that the application hangs, as it should, but the *.csv doesn't open until, again, I manually exit the application since the program gets stuck. It's as if the Application.Wait is telling Excel and VBA to wait, but this just causes the *.csv to delay opening, which is not the intended result.

Any ideas?

 

[mjb123]

Here are the two sub procedures referenced by the first post. Thanks!

Sub mintDotCom()

Dim newHour As Byte
Dim newMinute As Byte
Dim newSeOcond As Byte
Dim waitTime As Variant

Set masterFile = ActiveWorkbook.Sheets("Sheet1")

initializeIE False
ie.navigate "

https://wwws.mint.com/login.event"

frmPassword.Show

un = frmPassword.txtUsername.Text
pw = frmPassword.txtPassword.Text

ie.Visible = False

waitForLoad

saveFile ThisWorkbook.path & "\mint.html", ie.document.all(1).outerHTML
ie.document.all("form-login-username").Value = ""
ie.document.all("form-login-username").Value = un
ie.document.all("form-login-password").Value = pw
ie.document.forms(0).submit

waitForLoad

ie.navigate "

https://wwws.mint.com/transaction.event"

waitForLoad

ie.document.all("transactionExport").Click

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 2
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

'open the *.csv file
Application.SendKeys "O", True

'logs out of mint.com
ie.document.all("hdr-links-logout").Click

waitForLoad

'closes internet explorer
ie.Quit

End Sub


Sub transferData()

Dim openWorkbooks As Byte
Dim numberPosition As Byte
Dim transferFile As Worksheet
Dim counter As Byte
Dim lastRow As Integer
Dim lastColumn As Byte
Dim x As Integer
Dim y As Byte


openWorkbooks = Workbooks.Count


For counter = 1 To openWorkbooks
If numberPosition = InStr(Workbooks(counter).Name, "transactions") = 0 Then
Exit For
End If
Next


Set transferFile = Workbooks(counter).Sheets(1)

lastRow = transferFile.Range("A65536").End(xlUp).Row
Debug.Print lastRow

lastColumn = transferFile.Range("IV1").End(xlToLeft).Column
Debug.Print lastColumn


For y = 1 To lastColumn
For x = 1 To lastRow
Workbooks(1).Worksheets(1).Cells(x, y).Value = transferFile.Cells(x, y).Value
Next
Next

Workbooks(counter).Close

End Sub

 

[soccercook44]

Why not send an esc keystroke before calling the second procedure?

 

[SkipVought]

Hi,

I'm not sure I fully understand your process, but when you state that you are OPENING the .csv, it ran up a flag for me.

I'd look at IMPORTING the .csv into your Excel sheet, using Data > Get external Data > Import Data... instead of OPENING.

Once your have an import QueryTable added to your sheet, all you need do is modify the connection string for the current .csv you have downloaded.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mjb123]

soccercook44 and SkipVought,

Thanks for the feedback.

soccercook44, I failed to mention that I actually get an error at line:

Set transferFile = Workbooks(counter).Sheets(1)

because the counter is actually set to 2 (the original Excel workbook is 1 and the *.csv file is 2) by this time but the *.csv file is actually not open yet so VBA doesn't know what to do. And, obviously, I don't either. I did try to send the ESC keystroke before calling the second procedure but it didn't work because I get the error message that I failed to mention.

SkipVought, I initially wanted to do this, but the data I want is contained on pages 1 through 9 of my account on the web page and linking up to the website only returned the first page of information.

 

[soccercook44]

The .csv should be able to be imported through excel without writing code for the action. I think this is what SkipVought suggested. Why would that have an issue because of the data being on other pages? This should import whatever is in the .csv file.

 

[SkipVought]

Pages?

Thought you were getting a FILE! Where are the PAGES?


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mjb123]

Both. Each page, numbered 1-9, shows a number of transactions. By downloading the file, I get all the data from pages 1-9 in the .csv file.

 

[SkipVought]

Then you do NOT have only ONE .csv file!!!

.csv files do not have pages!

You must download multiple files, it seems.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]