diegoalejo15
New Member
- Joined
- Mar 23, 2017
- Messages
- 1
hi,
I need to logon to a site (www.xxxx.com.ar with an account and password), then open a link (clientes.xxxx.com.ar/cart.aspx) and get the table to excel sheet.
Below is the vba code (sorry but I´m not an expert as you may see).
The problem I have is that it works if the session is alive. I mean I have to go to WORKSHEET-DATA-FROM WEB-Login to main page.
Next, the code works for about an hour (session ttl)
What shoud I add to make it work only with credentials in the vba code?
Sub bajar_datos()
Dim ie As New SHDocVw.InternetExplorer
Dim ie2 As New SHDocVw.InternetExplorer
Dim doc As MSHTML.HTMLDocument
Dim tbl As MSHTML.HTMLTable
With ie
ie.Visible = True
.navigate "https://clientes.xxxx.com.ar/log.aspx"
Do While .ReadyState <> READYSTATE_COMPLETE
DoEvents
Loop
End With
Set doc = ie.document
With doc
.getElementById("txus").Value = "user"
.getElementById("txpa").Value = "pass"
.parentWindow.execScript "__doPostBack('btin','');"
End With
With ie2
ie2.Visible = True
.navigate "https://clientes.xxxx.com.ar/cart.aspx"
Do While .ReadyState <> READYSTATE_COMPLETE
DoEvents
Loop
End With
Worksheets("hoja1").Select
Sheets("hoja1").Range("A1:N80").Clear
With ActiveSheet.QueryTables.Add(Connection:="URL;https://clientes.xxxx.com.ar/cart.aspx", Destination:=Range("$A$1"))
.Name = "cart"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "2"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=True
End With
With ie
.navigate "https://clientes.xxxx.com.ar/cier.aspx"
End With
ie2.Quit
ie.Quit
Set ie = Nothing
Set ie2 = Nothing
End Sub
To say it in other words: I see IE explorer open, login and open the link with the table but if the session is ended (in the Data-From web menu) despite I login through vba it doesn´t copy the table to the worksheet and I get an excel message that the query does no return any data.
I need to logon to a site (www.xxxx.com.ar with an account and password), then open a link (clientes.xxxx.com.ar/cart.aspx) and get the table to excel sheet.
Below is the vba code (sorry but I´m not an expert as you may see).
The problem I have is that it works if the session is alive. I mean I have to go to WORKSHEET-DATA-FROM WEB-Login to main page.
Next, the code works for about an hour (session ttl)
What shoud I add to make it work only with credentials in the vba code?
Sub bajar_datos()
Dim ie As New SHDocVw.InternetExplorer
Dim ie2 As New SHDocVw.InternetExplorer
Dim doc As MSHTML.HTMLDocument
Dim tbl As MSHTML.HTMLTable
With ie
ie.Visible = True
.navigate "https://clientes.xxxx.com.ar/log.aspx"
Do While .ReadyState <> READYSTATE_COMPLETE
DoEvents
Loop
End With
Set doc = ie.document
With doc
.getElementById("txus").Value = "user"
.getElementById("txpa").Value = "pass"
.parentWindow.execScript "__doPostBack('btin','');"
End With
With ie2
ie2.Visible = True
.navigate "https://clientes.xxxx.com.ar/cart.aspx"
Do While .ReadyState <> READYSTATE_COMPLETE
DoEvents
Loop
End With
Worksheets("hoja1").Select
Sheets("hoja1").Range("A1:N80").Clear
With ActiveSheet.QueryTables.Add(Connection:="URL;https://clientes.xxxx.com.ar/cart.aspx", Destination:=Range("$A$1"))
.Name = "cart"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "2"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=True
End With
With ie
.navigate "https://clientes.xxxx.com.ar/cier.aspx"
End With
ie2.Quit
ie.Quit
Set ie = Nothing
Set ie2 = Nothing
End Sub
To say it in other words: I see IE explorer open, login and open the link with the table but if the session is ended (in the Data-From web menu) despite I login through vba it doesn´t copy the table to the worksheet and I get an excel message that the query does no return any data.