Unable to open file in Online Directory using VBA

mandy123

New Member
Joined
Sep 18, 2020
Messages
4
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
I am running a macro which goes to a directory file on Internet Explorer, goes through the folder and subfolder in this directory, and searches based on based on modified date and name. I took out my hyperlink in order to add the workbook here, but I know the path is correct, as the macro opens the latest file and pulls open the subfolders I need. The file I am trying to open is an excel file and has "IMS in Excel" in the name. I have been struggling to find the code to get the file to open from my browser and open in an excel file. but it does not open the file or gives me the errors "We can't connect to <filepath location>..." Followed by "MS Excel cannot access the file at <file location>..." when I use the "datacollection" codes. Ideally, I would like to avoid saving, as the files from the directory can have large amounts of data. Any help getting this macro to the designated file would be greatly appreciated.

VBA Code:
Sub IMSINFOMacrorun()

    Dim i As Long
    Dim IE As Object
    Dim objElement As Object
    
    Dim tabCollection As Object
    Dim rowCollection As Object
    Dim dataCollection As Object
 
    ' Create InternetExplorer Object
    Set IE = CreateObject("InternetExplorer.Application")
 
    ' You can uncoment Next line To see form results
    IE.Visible = False
 
    IE.Navigate "'<insert browser path here>"
 
    ' Wait while IE loading...
    Do While IE.Busy
        Application.Wait DateAdd("s", 1, Now)
    Loop
    
    Application.StatusBar = "Reading tables. Please wait..."
 
    Set tabCollection = IE.document.GetElementsbyTagName("table")
 
    i = 1
            
        j = 0
        
        k = 4
        
        Dim latestDate As Date
        Dim latestIdx As Integer
        
        Set rowCollection = tabCollection(i).GetElementsbyTagName("tr")
        latestDate = 0
        latestIdx = 0
        
        Dim myDates() As Date
        ReDim myDates(rowCollection.Length)
        
        While j < rowCollection.Length
            
            Set dataCollection = rowCollection(j).GetElementsbyTagName("td")
            
            If k < dataCollection.Length Then
                'Debug.Print (i & "," & j & "," & k & ": ")
                
                If dataCollection(k).Children.Length > 0 Then
                    'Debug.Print ("Print Date Here (2) >>> " & dataCollection(k).Children(0).Title)
                    myDates(j) = DateValue(dataCollection(k).Children(0).Title)
                    If myDates(j) > latestDate Then
                        latestDate = myDates(j)
                        latestIdx = j
                        Debug.Print (myDates(j))
                    End If
                End If
            End If
            j = j + 1
        Wend
    
    Set dataCollection = rowCollection(latestIdx).GetElementsbyTagName("td")
    Debug.Print (dataCollection(2).innerHTML)
    Debug.Print (IE.LocationURL)
    dataCollection(2).Children(0).Children(0).Click
    
    
    ' Wait while IE re-loading...
    Do While IE.Busy
        Application.Wait DateAdd("s", 1, Now)
    Loop
    Debug.Print (IE.LocationURL)
    
    Set tabCollection = IE.document.GetElementsbyTagName("table")
    
    j = 0
       k = 2
                
        Set rowCollection = tabCollection(i).GetElementsbyTagName("tr")
            
        While j < rowCollection.Length
            
            Set dataCollection = rowCollection(j).GetElementsbyTagName("td")
            
            If k < dataCollection.Length Then
                'Debug.Print (i & "," & j & "," & k & ": ")
                
                If dataCollection(k).Children(0).Children(0).innerHTML Like "*IMS in Excel*" Then
                    latestIdx = j
                    Debug.Print (IE.LocationURL)
                End If
            End If
            j = j + 1
        Wend

    Set dataCollection = rowCollection(latestIdx).GetElementsbyTagName("td")
    Debug.Print (dataCollection(k).innerHTML)
    Debug.Print (IE.LocationURL)
    dataCollection(k).Children(0).Children(0).fireevent ("onmousedown")
    'dataCollection(k).Children(0).Children(0).fireevent ("onclick")
    'dataCollection(k).Children(0).Children(0).Click

    
        ' Wait while IE re-loading...
    Do While IE.Busy
        Application.Wait DateAdd("s", 1, Now)
    Loop
    Debug.Print (IE.LocationURL)

    ' Show IE
    IE.Visible = True

    Application.StatusBar = ""

End Sub
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top