exporting CSV from IE

Hi i am writing a code to simplify some tasks at work. there is a website that we visit regularly to get rainfall data from and its wastes allot of time filling out irrelevant data. i have written the code to open IE fill out requested data and generate tables. there is a button which opens a downloadable CSV file but for the life of me i cant get it working. my code looks like this so far

Function FillInternetForm()
  Dim ie As Object
  Set ie = CreateObject("InternetExplorer.Application")
'create new instance of IE. use reference to return current open IE if
'you want to use open IE window. Easiest way I know of is via title bar.
  ie.navigate "https://hirds.niwa.co.nz/"
'go to web page listed inside quotes
  ie.Visible = True
  
  While ie.busy
    DoEvents  'wait until IE is done loading page.
  Wend
  
  Application.Wait (Now + TimeValue("00:00:05"))
  
  ie.Document.All("address").Value = ThisWorkbook.Sheets("sheet1").Range("b1")
   
  While ie.busy
    DoEvents  'wait until IE is done loading page.
  Wend
  
  ie.Document.All("SubmitForm").Click
  
  While ie.busy
    DoEvents  'wait until IE is done loading page.
  Wend
  
  ie.Document.All("sitename").Value = ThisWorkbook.Sheets("sheet1").Range("b2")
   
  While ie.busy
    DoEvents  'wait until IE is done loading page.
  Wend
  
  ie.Document.All("tempchange1").Value = ThisWorkbook.Sheets("sheet1").Range("b3")
   
  While ie.busy
    DoEvents  'wait until IE is done loading page.
  Wend
  
  ie.Document.All("tempchange2").Value = ThisWorkbook.Sheets("sheet1").Range("b3")
   
  While ie.busy
    DoEvents  'wait until IE is done loading page.
  Wend
  
  ie.Document.All("tempchange3").Value = ThisWorkbook.Sheets("sheet1").Range("b3")
   
  While ie.busy
    DoEvents  'wait until IE is done loading page.
  Wend
  
  ie.Document.All("calculation").Value = "IFD"
   
   Application.Wait (Now + TimeValue("00:00:05"))
    
  ie.Document.getElementByid("create").Click
  
End Function

i have attached 2 files showing what my spreadsheet looks like at the moment and what the hirds website looks like (showing the CSV download link) Hirds.JPG spreadsheet.JPG

if anyone could point me in the right direction that would be great.

Originally Posted by Tinbendr

This doesn't answer your question directly, but, you know, you can pull that data right into Excel, without code, using Data->From Web, paste in the link 'https://hirds.niwa.co.nz/hirds_data/work/NZMG/6480256/2667674/2.1,0,0/DDF', click the four yellow arrows, click Import, point to starting cell and hit enter. Voila, all the data appears.

When you need to update, just right click, Refresh.

You can also record the web query and have the link built from the sheet data, if it changes.

Sub Macro1()
'
' Macro1 Macro
'

'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;https://hirds.niwa.co.nz/hirds_data/work/NZMG/6480256/2667674/2.1,0,0/DDF" _
        , Destination:=Range("$A$1"))
        .CommandType = 0
        .Name = "DDF"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .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
End Sub

thanks for the reply, but unfortunately the URL changes according to the address by adding the coordinates for the site to the URL as shown below in red

https://hirds.niwa.co.nz/hirds_data/work/NZMG/6480256/2667674/2.1,0,0/DDF

so unless I can pull the URL which my macro ends on into the workbook I cant use it your way.

Originally Posted by Tinbendr

This answers your original question.

Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

Public Function DownloadFile(URL As String, LocalFilename As String) As Boolean
Dim lngRetVal As Long
lngRetVal = URLDownloadToFile(0, URL, LocalFilename, 0, 0)
If lngRetVal = 0 Then
    If Dir(LocalFilename) <> vbNullString Then
        DownloadFile = True
    End If
End If
End Function

I've uploaded a file that has either solution.

Sheet1 just d/ls the file.

Sheet2 pulls the data in through a querytable to the sheet.

Your first post got me going in the right direction (i'm just allot slower at coding), here you nailed it on the head! thanks mate! and thanks to everyone else who posted!