IE 10 open save and save as

Question

I am currently writing some code to download a CSV file and save into a specific folder.

The current way I am downloading files from the web is:

On Error Resume Next
    With Workbooks.Open("Direct Link to the CSV or XLS File")
        .SaveAs 'Name of file destination
        .Close SaveChanges:=False 'Added this in due to a pop up appearing when downloading a CSV
    End With
On Error GoTo 0

This particular file that I am trying to download can only be done so in IE.

This is the code I have so far to open the webpage up:

Sub data()

Dim IE As Object
Dim Report As Variant


Set IE = CreateObject("INTERNETEXPLORER.APPLICATION")
IE.Navigate    "Name of CSV File URL"
IE.Visible = True

End Sub

I am then having a windows explorer pop up appear. Image link below.

http://fud.community.services.support.microsoft.com/Fud/FileDownloadHandler.ashx?fid=2062fa2a-ad09-4379-bfe0-49abeb5516fc

Is there any ideas how I would "click" the save as button and put it into a file path. I can imagine Sendkeys is a solution.

This is for IE10, running excel 2010 and windows 7.

Answer 1

So after a little experimenting I have managed to find a work around. Instead I opened it by using a web query via excel.

Here is the code:

Workbooks.add

 With ActiveSheet.QueryTables.add(Connection:= _
    "URL;http\\:thelinkiused.csv" _
    , Destination:=Range("$A$1"))
    .Name = "transaction"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlAllTables
    .WebFormatting = xlWebFormattingNone
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
End With
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=True, Space:=False, Other:=False

ActiveWorkbook.SaveAs Filepath & "\thelinkIused.csv"
ActiveWorkbook.Close savechanges:=False

This is obviously a very basic code and doesn't need all the false statements for delimiting the data.