部分文件名打开工作簿VBA

Question

我想用VBA一个接一个地打开更多的文档，并在我用以下名称从sap中提取它们之后对它们进行一些更改:‘sap id name of the client“(例如. "546930 XXX”)这些文档是称为“客户端的sap id名称”的xls文件。如果我定义了两个变量: sap id=单元格(i，2)和name=单元(i，3)，我如何打开它们？

你能给我个主意吗？谢谢。

Answer 1

循环遍历文件夹中的文件

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