0

I'm trying to open multiple Excel files and add the same new cell with same name to each. They are in a folder .../desktop/excel named workbook1, workbook2, etc.

I tried this article already but I'm getting a runtime error 76 'Path not found'.

screenshot

I'm super novice with VBA, any help is appreciated! This is the script I'm running: 

Sub LoopThroughFolder()

Dim MyFile As String, Str As String, MyDir As String, Wb As Workbook
Dim Rws As Long, Rng As Range
Set Wb = ThisWorkbook
'change the address to suite
MyDir = "C:\Users\shaye\Desktop\excel" 'Your Directory
MyFile = Dir(MyDir & "*.xlsx")    'Your excel file extension
ChDir MyDir
Application.ScreenUpdating = 0
Application.DisplayAlerts = 0

Do While MyFile <> ""
    Workbooks.Open (MyFile)
        Range("G1").Value = "NewColumn" 'New Column Name
        ActiveWorkbook.Save
        ActiveWorkbook.Close True
    MyFile = Dir()
Loop

End Sub

[desktop error3]

ShayeRyan
  • 449
  • 4
  • 5
  • 1
    Does the path exist? FWIW, you can probably just omit that line entirely - it just changes the current working directory of the Excel process. You don't rely on it anywhere else in the `Sub`. – Comintern Feb 01 '19 at 21:02
  • Copy and paste the path in the address bar pf windows explorer to check if there are typos – Ricardo Diaz Feb 01 '19 at 21:48

1 Answers1

1

try this code. I think you need this "\" in your directory and "??" in your file extension to find several excel types

Sub LoopThroughFolder()

Dim MyFile As String, Str As String, MyDir As String, Wb As Workbook
Dim Rws As Long, Rng As Range
Set Wb = ThisWorkbook
'change the address to suite
MyDir = "C:\Users\shaye\Desktop\excel\" 'Your Directory need this "\"
MyFile = Dir(MyDir & "*.xl??")    'Your excel file extension
Application.ScreenUpdating = 0
Application.DisplayAlerts = 0

Do While MyFile <> ""
    Workbooks.Open (MyFile)
        Range("G1").Value = "NewColumn" 'New Column Name
        ActiveWorkbook.Save
        ActiveWorkbook.Close True
    MyFile = Dir()
Loop

End Sub
Dharmawan Raharjo
  • 11
  • 1
  • Thanks for getting back to me. When trying to run it with this new code nothing happens. No errors, no anything. I'm going to Insert Module, Pasting the code and clicking the play button to run the macro. Am I missing something? – ShayeRyan Feb 04 '19 at 18:23
  • @ricardo. Thanks, I did have a spelling error but the macro still isn't doing anything. – ShayeRyan Feb 04 '19 at 18:28
  • I'm getting a new error now. Runtime error 1004 saying the file couldn't be found but I have the exact path. Two excel files in a folder named excel on my desktop. File names are Book1.xlsx and Book2.xlsx – ShayeRyan Feb 04 '19 at 18:38