Basically, there is an excel sheet containing a list of IP's. Each ip takes you to a powerbar reading, which provides ampage readings we need to collect. Currently, we have to open every one in IE, log in (same user and password for all ip's) then make a note of the amp reading and type that in the excel sheet.

After much searching I found out that if I was to enter ftp://user: pass@199.99.99.99/data.txt in a web browser it will open the text document and display information like so:

Network Management Card AOS v3.7.0
Rack PDU APP v3.7.0

Date         Time        Name Contact Location System IP
23.08.2012 20:29:08 Test   Test     Test      199.99.99.99

Metered Rack PDU         
Date          Time       I        IMax    IMin
23.08.2012 20:25:32 2.6     2.6     2.5 
23.08.2012 20:15:32 2.6     2.6      2.5 
23.08.2012 20:05:32 2.6     2.6      2.5 
23.08.2012 19:55:32 2.6     2.6     2.5 
23.08.2012 19:45:32 2.6     2.6      2.5 
23.08.2012 19:35:32 2.6     2.6     2.5
I assume it would be easy to import said txt into excel and only take the highlighted text from above into the spreadsheet? Which is all I really need. Also, I would need to compact the data. As you can see there are 6 entries for today, I only need one per day.

I managed to find a great macro on here to download the data.txt file to my hard drive. But I don't know how to continue this macro to download 99 more text files, rename them all and import them into a new excel sheet, each file with it's own tab. The macro I am using is as follows:

'http://msdn.microsoft.com/en-us/library/ms775123(VS.85).aspx
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 Sub Example()
    Dim URL As String, LocalFilename As String
    
    URL = "ftp://user:pass@199.99.99.99/data.txt"
    
    LocalFilename = "c:\test\199.99.99.99.txt"
    
    If URLDownloadToFile(0, URL, LocalFilename, 0, 0) = 0 Then
        'downloaded
        Stop
    End If
End Sub
Is there anyway of mass importing this data? I feel I am so close to cracking this. All help is greatly appreciated.