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
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Watch MrExcel Video

Forum statistics

Threads
1,113,745
Messages
5,543,963
Members
410,586
Latest member
acadavid86
Top