Combining macros in Excel

Question

I'm trying to combine/nest 3 different functions in Excel VBE: open, loop, and click. I have them written out separately, but am unsure of how to combine them. I've tried the "call macro" function but got a compile error returned to me.

The goal is to open a bunch of files within a certain folder and click on the URL in all of them (the URL will not always be the same, so I need a click function that targets any unknown URL within a sheet).

Open macro:

Sub openMyfile()

Dim Source As String
Dim StrFile As String

Source = "/users/kmogilevsky/Desktop/IC_new/"
StrFile = Dir("/users/kmogilevsky/Desktop/IC_new/")

Do While Len(StrFile) > 0
    Workbooks.Open Filename:=Source & StrFile
    StrFile = Dir("/users/kmogilevsky/Desktop/IC_new/")
Loop
End Sub 

Loop macro:

 Sub LoopThroughFiles()
   Dim MyObj As Object, MySource As Object, file As Variant
   Set MySource = MyObj.GetFolder("/users/kmogilevsky/Desktop/IC_new/")
   For Each file In MySource.Files
      If InStr(file.Name, "test") > 0 Then
           End If
   Next file
 End Sub


    Click macro (this needs some work):

    Private Sub CommandButton1_Click()
    Call NewSub
    End Sub

Answer 1

This code will open all Excel files in the IC_New folder on the desktop.
It will then look at each sheet and follow any hyperlinks that are on the sheet.

Sub Open_ClickHyperlinks()

    Dim sPath As String
    Dim vFiles As Variant
    Dim vFile As Variant
    Dim wrkBk As Workbook
    Dim wrkSht As Worksheet
    Dim HLink As Hyperlink

    sPath = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator & _
        "IC_New" & Application.PathSeparator

    'Return all files that have an extension starting with xls.
    vFiles = EnumerateFiles(sPath, "xls*")

    'Loop through each file.
    For Each vFile In vFiles
        'Open the file
        Set wrkBk = Workbooks.Open(Filename:=vFile, UpdateLinks:=False)
        With wrkBk
            'Loop through each worksheet in the file.
            For Each wrkSht In .Worksheets
                'Loop through each hyperlink on the worksheet.
                For Each HLink In wrkSht.Hyperlinks
                    HLink.Follow
                Next HLink
            Next wrkSht
            .Close SaveChanges:=False
        End With
    Next vFile

End Sub

'Get all files in the specified folder, default to include all subfolders as well.
Public Function EnumerateFiles(sDirectory As String, _
            Optional sFileSpec As String = "*", _
            Optional InclSubFolders As Boolean = True) As Variant

    EnumerateFiles = Filter(Split(CreateObject("WScript.Shell").Exec _
        ("CMD /C DIR """ & sDirectory & "*." & sFileSpec & """ " & _
        IIf(InclSubFolders, "/S ", "") & "/B /A:-D").StdOut.ReadAll, vbCrLf), ".")

End Function

Answer 2

Sub ReadWorkbooksInCurrentFolder()
    Dim wbDst As Workbook
    Dim wbSrc As Workbook
    Dim MyPath As String
    Dim strFilename As String

    'Stop annoying popups while macro is running
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False

    'When working with many open workbooks its good to explicitly reference all workbooks, makes sure your code works and easier to read, understand and remember which workbook is which.
    Set wbDst = ThisWorkbook

    srcSheetName = "Data"
    dstSheetName = "Results"

    'I want to loop through all .xlsx files in the folder
    MyPath = ThisWorkbook.Path
    strFilename = Dir(MyPath & "\*.xlsx", vbNormal)

    If Len(strFilename) = 0 Then
        MsgBox "No workbooks found ending in .xlsx in current folder"
        Exit Sub
    End If

    Do Until strFilename = ""

        Set wbSrc = Workbooks.Open(Filename:=MyPath & "\" & strFilename)
        Call CollectData(wbDst, wbSrc, dstSheetName, srcSheetName)
        wbSrc.Close

        strFilename = Dir()

    Loop


    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True

End Sub

Sub CollectData(ByRef wbDst as Workbook, ByRef wbSrc as Workbook, dstSheetName as String, srcSheetName as String)

    'Copy cell A1 contents in source workbook to destination workbook cell A1
    wbDst.Sheets(dstSheetName).Range("A1") = wbSrc.Sheets(srcSheetName).Range("A1")

End Sub

Please edit the subroutine CollectData() so that it suits your needs, i.e. performs the click / url open. (I am not familiar with opening urls from excel, but I loop through workbooks often)