Hello Excel Gurus,
I currently have a VBA macro in a Excel xlsm file which opens a webpage then copy&paste the content of the webpage into a cell
But it's not very practical.
I would like to modify the following code to change the copy and paste function TO a download xls from web and import function, can you
help me to do it, because for downloading a xls file, and pasting the content in a cell.. i'm lost and even with much searches on the web
I can't find the way to do it ..
The code I'm actually using :
Private Declare PtrSafe Function CloseClipboard Lib "user32" () As Long
Private Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
Private Declare PtrSafe Function EmptyClipboard Lib "user32" () As Long
Sub GetTable()
Application.DisplayAlerts = False
Dim ieApp As InternetExplorer
Dim ieDoc As Object
Dim ieTable As Object
Dim clip As DataObject
Set ieApp = New InternetExplorer
ieApp.Visible = True
ieApp.Navigate "https://website/login.jsp"
Do While ieApp.Busy: DoEvents: Loop
Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop
Set ieDoc = ieApp.Document
With ieDoc.forms(0)
.login.Value = "LOGIN"
.Password.Value = "PASSWORD"
.submit
End With
Do While ieApp.Busy: DoEvents: Loop
Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop
Application.Wait DateAdd("s", 3, Now)
Sheets("Sheet1").Delete
Sheets.Add.Name = "Sheet1"
Sheets("Sheet1").Activate
OpenClipboard 0
EmptyClipboard
CloseClipboard
Do While ieApp.Busy: DoEvents: Loop
Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop
ieApp.Navigate "https://website/htmlstat/userstat?=test"
Set ieDoc = ieApp.Document
Range("A100").Select
ieApp.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DODEFAULT
ieApp.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT
ActiveSheet.Paste
OpenClipboard 0
EmptyClipboard
CloseClipboard
Then the last part of the code ( ieApp.Navigate.... userstat) repeats for each user...
So, how I can modify the part of this code for downloading a .xls file from web then importing in a cell ?
Best regards
Endorfyne
Bookmarks