Run time error '9' vba code; opening xlsx files without knowing name and making changes on them

user_15441167 0
Viewed 47

I'm an trying to open several xlsx files on a folder, make some operations in them (for this case, just writing "hello there"), then closing (saving is not relevant). I do not know the name of the files nor the amount. So far I have this code, which can effectively open and close all the xlsx on the folder, but at the time I want to make any operation on them (write "hello there") I get a "Run-time error '9':" Any help will be appreciated!

Sub OpenFiles()
Dim MyFolder As String
Dim MyFile As String

MyFolder = "C:\Users\frank003\Documents\Local\VBA - Test"
MyFile = Dir(MyFolder & "\*.xls")
Do While MyFile <> ""
    Workbooks.Open Filename:=MyFolder & "\" & MyFile
    MyFile = Dir
    
    Workbooks(MyFile).Sheets(1).Range("A15") = "Hello there"
    
    Workbooks.Close

Loop
End Sub
vba excel automation file
1 Answers

Loop Through Files in a Folder

Option Explicit

Sub OpenFiles()
    
    Dim MyFolder As String
    Dim MyFile As String
    
    MyFolder = "C:\Users\frank003\Documents\Local\VBA - Test"
    MyFile = Dir(MyFolder & "\*.xlsx") ' "\*.xls*"
    
    Dim wb As Workbook
    Do While MyFile <> ""
        Set wb = Workbooks.Open(Filename:=MyFolder & "\" & MyFile)
        wb.Sheets(1).Range("A15").Value = "Hello there"
        wb.Close SaveChanges:=True
        MyFile = Dir
    Loop

End Sub
user_98140690
Related