I am trying to copy and pasting some values from a worksheet which will be changing name according to different dates. Ideally, the code should open it, activate it and copy a table from a sheet called "Geographic bond distribution" and then pasting it to the current workbook.

When debugging the code, the last line returns a Run-time error '9' as probably I am messing up something when opening / calling the workbook. In fact, if I manually open the file the code works .

Many thanks!

 Sub GEO_Newton()



    Dim fileName As String
fileName = Dir(ThisWorkbook.Path & "bnymgf_sustainable_global_dynamic_bond_summary*.*")

If fileName <> "" Then
    Workbooks.Open fileName:=ThisWorkbook.Path & "\Underlying\" & fileName
End If
   
'Declare iteration object variable
    Dim iWorkbook As Workbook
     
'Loop through all open workbooks
    For Each iWorkbook In Application.Workbooks
         
        'Do the following:
            '(1) Test whether the first/leftmost 9 letters of the name of the workbook (the loop is currently iterating through) spell "Excel VBA"
            '(2) If the condition is met, activate the workbook (the loop is currently iterating through)
        If Left(iWorkbook.Name, 18) = "bnymgf_sustainable" Then iWorkbook.Activate
     
    Worksheets("Geographic bond distribution").Activate

CodePudding user response：

Several issues:

• The filepath is missing an & Application.PathSeparator &.
• Dir doesn't return the full path, so you have to "rebuild" it.
• Use a Workbook variable:

Dim filepath As String
filepath = ThisWorkbook.Path & Application.PathSeparator & "bnymgf_sustainable_global_dynamic_bond_summary*.*"

Dim fname As String
fname = Dir(filepath)

If fname = "" Then 
    ' file not found
    Exit Sub
End If

filepath = ThisWorkbook.Path & Application.PathSeparator & fname

Dim wb As Workbook
Set wb = Workbooks.Open(filepath)

wb.Worksheets("Geographic bond distribution").Activate

CodePudding user response：

Loop Through Files in a Folder

• Adjust the values in the constants section.
• Add your copy code.

Option Explicit

Sub GEO_Newton()
    
    Const sSubFolderPath As String = "\Underlying\"
    Const sFilePattern As String _
        = "bnymgf_sustainable_global_dynamic_bond_summary*.*"
    Const sName As String = "Geographic bond distribution"
    Const dName As String = "Sheet1" ' here the destination worksheet name
    
    Dim sFolderPath As String: sFolderPath = ThisWorkbook.Path & sSubFolderPath
    Dim sFileName As String: sFileName = Dir(sFolderPath & sFilePattern)
    If Len(sFileName) = 0 Then
        MsgBox "No files found.", vbExclamation
        Exit Sub
    End If
    
    Dim dwb As Workbook: Set dwb = ThisWorkbook
    Dim dws As Worksheet: Set dws = dwb.Worksheets(dName)
    
    Application.ScreenUpdating = False
    
    Dim swb As Workbook
    Dim sws As Worksheet
    Dim sFilePath As String
    
    Do
        sFilePath = sFolderPath & sFileName
        Set swb = Workbooks.Open(sFilePath)
        Set sws = swb.Worksheets(sName)
        
        ' Continue to copy or do something else, e.g.:
        Debug.Print swb.Name, sws.Name
        
        
        swb.Close SaveChanges:=False
        sFileName = Dir
    Loop Until Len(sFileName) = 0
    
    'dwb.Save
    
    Application.ScreenUpdating = True
    
    MsgBox "Table data copied.", vbInformation
    
End Sub