[Solved] How do I loop over Excel files in a shared drive and make/save changes on each of them? [duplicate]

by


Do you have a list of the names of the files you want to open?
If you have it you can loop trough it setting a workbook as the name of each file.

Es. 

Dim x as integer
Dim selection, nameofthefile as variant
Dim wk1 As Workbook
Dim sh1 As Worksheet    
Dim wk2 As Workbook
Dim sh2 As Worksheet

Set wk1 = Workbooks("this file.xlsx")
Set sh1 = wk1.Worksheet("name of the sheet with the list")
For x = 1 To 900 
If Cells(x , 1) <> "" Then
 selection = Cells(x , 1)
 nameofthefile = "Your path" & ".xlsx"
 Workbooks.Open Filename:=nameofthefile
 Set wk2 = Workbooks(selection & ".xlsx")
 Set sh2 = wk2.Worksheets("Name of the sheet you want to use")
 sh1.activate
 Range("B45:B51").Select
Application.CutCopyMode = False
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("M45:M48").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("C45:C51").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("N45:N48").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Application.CutCopyMode = False
wk1.Close SaveChanges:=True
End If
Next
Set wk2 = Nothing
Set sh2 = Nothing

it’s only an example but it should work with a list. Tell us more

solved How do I loop over Excel files in a shared drive and make/save changes on each of them? [duplicate]