how to download and save a document by VBA

Question

I'm trying to replace a document that I do the download everyday. But I don't know what I need to do after I click the download button. I need to save the document with a specific name in my documents

Dim IE As Object
    Dim n, Period1, Period2 As Double



    'retorna o internet explorer-return the correct period
    Period1 = "201612"
    Period2 = "201612"

    'abre o internet explorer
    Set IE = CreateObject("InternetExplorer.Application")
    IE.Navigate "http://www2.susep.gov.br/menuestatistica/SES/principal.aspx"
    IE.Visible = True

    Application.Wait (Now + TimeValue("00:00:02"))
    'seleciona as operações desejadas
    IE.document.getElementById("ctl00_ContentPlaceHolder1_edSelProd").SelectedIndex = "8"
    IE.document.getElementById("ctl00_ContentPlaceHolder1_btnConsultar").Click

    'seleciona o periodo
    Application.Wait (Now + TimeValue("00:00:02"))
    Set ieDoc = IE.document
        ieDoc.getElementById("ctl00_ContentPlaceHolder1_edInicioPer").Value = Period1
        ieDoc.getElementById("ctl00_ContentPlaceHolder1_edFimPer").Value = Period2

    'seleciona as empresas
    IE.document.getElementById("ctl00_ContentPlaceHolder1_edEmpresas").SelectedIndex = "0"

    ieDoc.getElementById("ctl00_ContentPlaceHolder1_Button1").Click

    Application.Wait (Now + TimeValue("00:00:02"))
    ieDoc.getElementById("ctl00_ContentPlaceHolder1_Button1").Click

I've always found it difficult to download using Internet Explorer Automation because of an IE notification that asks whether you want to save or open the file - which as far as I know cannot be turned off. Instead I'd recommend running Google Chrome or Firefox from a `Shell` command (within VBA) as they don't have such notification boxes, or if they do they can be disabled. — Jordan, Apr 10 '17 at 14:32

Vityata · Accepted Answer · 2017-04-11T16:17:27.887

1

I have managed to achieve something with the following:

Option Explicit

Sub TestMe()


    Dim IE As Object
    Dim n As Double, Period1 As Double, Period2 As Double
    Dim ieDoc As Object

    'retorna o internet explorer-return the correct period
    Period1 = "201612"
    Period2 = "201612"

    'abre o internet explorer
    Set IE = CreateObject("InternetExplorer.Application")
    IE.Navigate "http://www2.susep.gov.br/menuestatistica/SES/principal.aspx"
    IE.Visible = True

    Application.Wait (Now + TimeValue("00:00:02"))
    'seleciona as operações desejadas
    IE.document.getElementById("ctl00_ContentPlaceHolder1_edSelProd").SelectedIndex = "8"
    IE.document.getElementById("ctl00_ContentPlaceHolder1_btnConsultar").Click

    'seleciona o periodo
    Application.Wait (Now + TimeValue("00:00:02"))
    Set ieDoc = IE.document
        ieDoc.getElementById("ctl00_ContentPlaceHolder1_edInicioPer").value = Period1
        ieDoc.getElementById("ctl00_ContentPlaceHolder1_edFimPer").value = Period2

    'seleciona as empresas
    IE.document.getElementById("ctl00_ContentPlaceHolder1_edEmpresas").SelectedIndex = "0"

    ieDoc.getElementById("ctl00_ContentPlaceHolder1_Button1").Click

    Application.Wait (Now + TimeValue("00:00:03"))
    ieDoc.getElementById("ctl00_ContentPlaceHolder1_Button1").Click

    AppActivate IE.name, 1
    SendKeys "%{s}"
    SendKeys "{ENTER}"

End Sub

Play a bit with the sendkeys, in Brazil(Portuguese) they should be different than "s". Read more here: https://msdn.microsoft.com/en-us/library/office/ff821075.aspx

My idea is to navigate to the SaveAs button (in Portugese, its something else) and to use the SendKeys to get it.

Something like this should be possible:

AppActivate IE.Name, 2
SendKeys "{TAB}{TAB}"
SendKeys "{DOWN}"
SendKeys "%{a}"
SendKeys "{ENTER}"

edited Apr 11 '17 at 16:17

answered Apr 10 '17 at 14:45

Vityata

42,633
8
55
100

Then you should try to navigate to the `SaveAs` and give your own name. Somehow. Use `{TAB}{TAB}{TAB}` or something like this. – Vityata Apr 10 '17 at 15:16
1

When you say SaveAs, is It like write 'Report = Application.GetSaveAsFilename("Attrition Report.xls', "Excel Files (*.xlsx), *.xlsx") "? Can you write for me? i do not have any other idea – Vinicius Apr 10 '17 at 22:36
hey, @Vityata Can you help me? do you have any idea? – Vinicius Apr 11 '17 at 16:01
it is working in the same way if I use the Sendkeys "%{s}". I would like to do the download with specif name and in specific address. Example: name=Test.xls and address= "Bibliotecas\Documentos" Thanks to help any way – Vinicius Apr 11 '17 at 17:24
Do you know other option? @Vityata – Vinicius Apr 11 '17 at 22:22
Take a look here: http://stackoverflow.com/questions/26038165/automate-saveas-dialouge-for-ie9-vba @Vinicius – Vityata Apr 12 '17 at 10:25
1

@QHarr - if you do not explicitly tag the user, then only the person with the answer sees the message. – Vityata May 08 '19 at 20:59
1

@Vinicius - Please consider accepting the answer given – Vityata May 08 '19 at 21:00

how to download and save a document by VBA

1 Answers1