Update Batch

[MichaelS_]

Hi

We got well over 100 Excel-Sheets. During the night there is a scheduled task which updates the entire sheets with actual data. The Company-Names of those 100 Excel-Sheets is coded directly in VBA (see below). We use these Company-Names in several modules. This makes change very time consuming.

I think it would be the better solution to have those Company-Names in a kind of ini-File (this could may be an Excel Sheet or an TXT (?) with Folder/Company-Names). What do you think would be the best solution? How should the VBA Code look like to read this "Company-Name-File"?

Thanks a lot
Michael


Sub Auto_Open()

Dim i As Integer

For i = 1 To 4

If i = 1 Then Workbooks.Open "R:\APS\AMR\REBR\SwissCompanies\ABB AG\ABB.xls", UpdateLinks:=0
If i = 2 Then Workbooks.Open "R:\APS\AMR\REBR\SwissCompanies\Actelion\Actelion_new.xls", UpdateLinks:=0
If i = 3 Then Workbooks.Open "R:\APS\AMR\REBR\SwissCompanies\Adecco\AdeccoEquityNote.xls", UpdateLinks:=0
If i = 4 Then Workbooks.Open "R:\APS\AMR\REBR\SwissCompanies\Affichage\Affichage.xls", UpdateLinks:=0


Chart_format_2Years
ChartFormating
DefineScale

ActiveWorkbook.Save
ActiveWorkbook.Close

Next i

End Sub

[dspencer]

Wouldn't the easiest method be to simply have a sheet within the workbook with the filenames (and paths)?

That way you could do a loop;


Sub Auto_Open()

Dim xfilename as String
Dim i as integer

Do Until xfilename = ""

i=i+1
xfilename = Workbooks("Workbook with names on it").Range("A" & i)

Workbooks.Open xfilename, UpdateLinks:=0

Chart_format_2Years
ChartFormating
DefineScale

ActiveWorkbook.Save
ActiveWorkbook.Close

Loop

End Sub

[MichaelS_]

I did it the way you recommended and it worked very well.

Thank you very much
Michael