VBA : Open multiple files based on cell value-CodePudding

I want to write a macro that will open one or more excel files from a folder.

The macro should check if the value from 3 different cells are not zero.

If not, macro should open the file based on the cell value.

How do I write VBA code to open multiple files based on cell value ?

Public Sub OpenFile1(MyRow, MyCol 1)

If Cells(MyRow, MyCol 1).Value <> "" Then
    Workbooks.Open Path & Name
End If

End Sub

Public Sub OpenFile2(MyRow, MyCol 2)

If Cells(MyRow, MyCol 2).Value <> "" Then
    Workbooks.Open Path & Name
End If
End Sub

Public Sub OpenFile3(MyRow, MyCol 3)

If Cells(MyRow, MyCol 3).Value <> "" Then

    Workbooks.Open Path & Name 
End If
End Sub

Sub openbutton1()

Call OpenFile1(6, 36)

Call OpenFile2(6, 36)
Call OpenFile3(6, 36)

End Sub

CodePudding user response：

Create a list with the three checks, -if list empty do nothing -else foreach item in list open corresponding book?

I might have misunderstood something though.

CodePudding user response：

Open Workbooks From a List (Range)

Sub OpenFilesTEST()
    
    Const RangeAddress As String = "AK6:AM6"
    Const FolderPath As String = "C:\Test\"
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    Dim rg As Range: Set rg = ws.Range(RangeAddress)
    
    OpenFiles rg, FolderPath

End Sub

Sub OpenFiles(ByVal rg As Range, ByVal FolderPath As String)
    Const ProcName As String = "OpenFiles"
    On Error GoTo ClearError
    Dim cFilesCount As Long
    
    If Right(FolderPath, 1) <> "\" Then FolderPath = FolderPath & "\"
    
    Application.ScreenUpdating = False
    
    Dim Cell As Range
    Dim cString As String
    Dim cFileName As String
    
    For Each Cell In rg.Cells
        cString = CStr(Cell.Value)
        If Len(cString) > 0 Then ' not blank
            cFileName = Dir(FolderPath & cString)
            If Len(cFileName) > 0 Then ' file found
                Workbooks.Open FolderPath & cFileName
                cFilesCount = cFilesCount   1
            'Else ' file not found; do nothing
            End If
        'Else ' blank; do nothing
        End If
    Next Cell
    
ProcExit:
    Application.ScreenUpdating = True
    MsgBox "Number of files opened: " & cFilesCount, vbInformation
    
    Exit Sub
ClearError:
    Debug.Print "'" & ProcName & "' Run-time error '" _
        & Err.Number & "':" & vbLf & "    " & Err.Description
    Resume ProcExit
End Sub