PDA

View Full Version : Download and open Excel file



Svmaxcel
05-13-2018, 11:48 AM
I have made below code to extract dataI go to our intranet site, select the date and click on the button to export raw data.After clicking on raw data, I get a prompt to download the file.I want to make it automated, so that it opens the downloaded file and copy data to my original file.Please help me with code.Sub test()Dim ie As New SHDocVw.InternetExplorerDim htmldoc As MSHTML.HTMLDocumentDim htmlin As MSHTML.IHTMLElementie.Visible = Trueie.navigate "https://example.com"Do While ie.readyState READYSTATE_COMPLETELoopSet htmldoc = ie.documentSet htmlin = htmldoc.getElementById("FromDate")htmlin.Value = Range("A1")Set htmlin = htmldoc.getElementById("ToDate")htmlin.Clickhtmlin.Value = Range("A2")Set htmlin = htmldoc.getElementById("Refreshdata")htmlin.ClickDo While ie.readyState READYSTATE_COMPLETELoopSet htmlin = htmldoc.getElementById("Download_Raw")htmlin.ClickEnd Sub

mancubus
05-14-2018, 06:47 AM
http://www.vbaexpress.com/forum/faq.php?faq=new_faq_item#faq_contrib_faq_item



When creating or replying to a post, do the following:

1. paste your VBA code into the text area
2. highlight (select) the code
3. click the # button
The button is found just above the text area where you write your question. This will automatically wrap the [CODE] tags around the selected code.

VBA tags make your code appear in your thread in the same way that you see it in your code pane in the Visual Basic Editor (VBE) window, thus making it far easier for others to read.

As an alternative, you may surround your code manually by writing "[ CODE ]" before your code, and "[ /CODE ]" after the code sample.


Please only post the relevant section(s) of your code. Don't post 50 lines of code if you only need help debugging one variable. Create a test case to demonstrate an issue.

Svmaxcel
05-15-2018, 07:23 AM
Wrapping code...




Sub test()

Dim ie As New SHDocVw.InternetExplorer
Dim htmldoc As MSHTML.HTMLDocument
Dim htmlin As MSHTML.IHTMLElement

ie.Visible = True
ie.navigate "https://example.com"

Do While ie.readyState <> READYSTATE_COMPLETE
Loop

Set htmldoc = ie.document
Set htmlin = htmldoc.getElementById("FromDate")
htmlin.Value = Range("A1")


Set htmlin = htmldoc.getElementById("ToDate")
htmlin.Click
htmlin.Value = Range("A2")

Set htmlin = htmldoc.getElementById("Refreshdata")
htmlin.Click

Do While ie.readyState <> READYSTATE_COMPLETE
Loop

Set htmlin = htmldoc.getElementById("Download_Raw")
htmlin.Click

End sub

mancubus
05-16-2018, 02:43 AM
i understand export button on the intranet just downloads the file to some folder.
what is the folder and name of the downloaded file?

when you hit the "export" button multiple times, is the file downloaded into the same folder with the same name?

if this is the case, insert a few lines to your code in order to open, copy data from and close this "known" file.