Open multiple files based on cell value

user_19250096 0
Viewed 61

I want to open one or more Excel files from a folder.

The macro should check if the value from three different cells are not zero.
If not, macro should open the file based on the cell value.

How do I 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
vba excel
2 Answers

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
user_98140690

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.

user_154401380
Related