Partial filename open workbook VBA

Question

I want to open more documents with VBA one by one and to make some changes in it after I extract them from sap with the following name :' sap id name of the client" eg. "546930 XXX" The documents are xls files that are called " sap id name of the client". How can I open them if I defined 2 variables : sap id= cells(i,2) and name= cells(i,3)?

Can you please give me an idea? Thanks.

Could you share the code you got so we can build on it? – VBasic2008 Feb 15 '22 at 10:40 — VBasic2008, Feb 15 '22 at 10:40

score 0 · Answer 1 · answered Feb 15 '22 at 11:31

Loop Through Files in a Folder

Option Explicit

Sub UpdateSapFiles()
    
    Const sName As String = "Sheet1"
    Const siCol As String = "B" ' Id
    Const snCol As String = "C" ' Name
    Const sfRow As Long = 2
    
    Const dFolderPath As String = "C:\Test\"
    Const dFileExtension As String = ".xls"
    Const dDelimiter As String = " "
    
    Dim swb As Workbook: Set swb = ThisWorkbook
    
    Dim sws As Worksheet: Set sws = swb.Worksheets(sName)
    Dim slRow As Long
    slRow = sws.Cells(sws.Rows.Count, siCol).End(xlUp).Row
    If slRow < sfRow Then Exit Sub
    Dim srg As Range
    Set srg = sws.Range(sws.Cells(sfRow, siCol), sws.Cells(slRow, siCol))
    
    Application.ScreenUpdating = False
    
    Dim sCell As Range
    Dim dwb As Workbook
    Dim dFilePath As String
    
    For Each sCell In srg.Cells
        dFilePath = dFolderPath & sCell.Value & dDelimiter _
            & sCell.EntireRow.Columns(snCol).Value & dFileExtension
        If Len(Dir(dFilePath, vbDirectory)) > 0 Then ' file exists
            Set dwb = Workbooks.Open(dFilePath)

            ' your code, e.g.:
            Debug.Print dwb.Name, dwb.Sheets(1).Name
            
        
            dwb.Close SaveChanges:=True
        'Else ' file doesn't exist; do nothing
        End If
    Next sCell
    
    Application.ScreenUpdating = True
    
    MsgBox "SAP files updated.", vbInformation
    
End Sub

Partial filename open workbook VBA

1 Answers1

Loop Through Files in a Folder