Update Batch
 

Hi

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

I think it would be the better solution to have those Company-Names i
a kind of ini-File (this could may be an Excel Sheet or an TXT (?) wit
Folder/Company-Names). What do you think would be the best solution? Ho
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\AB
AG\ABB.xls", UpdateLinks:=0
If i = 2 Then Workbooks.Ope
"R:\APS\AMR\REBR\SwissCompanies\Actelion\Actelion_ new.xls"
UpdateLinks:=0
If i = 3 Then Workbooks.Ope
"R:\APS\AMR\REBR\SwissCompanies\Adecco\AdeccoEquit yNote.xls"
UpdateLinks:=0
If i = 4 Then Workbooks.Ope
"R:\APS\AMR\REBR\SwissCompanies\Affichage\Affichag e.xls"
UpdateLinks:=0


Chart_format_2Years
ChartFormating
DefineScale

ActiveWorkbook.Save
ActiveWorkbook.Close

Next i

End Su

--
MichaelS
-----------------------------------------------------------------------
MichaelS_'s Profile: http://www.excelforum.com/member.php...fo&userid=2640
View this thread: http://www.excelforum.com/showthread.php?threadid=50633

Update Batch
 

Wouldn't the easiest method be to simply have a sheet within th
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 Su

--
dspence
-----------------------------------------------------------------------
dspencer's Profile: http://www.excelforum.com/member.php...fo&userid=3096
View this thread: http://www.excelforum.com/showthread.php?threadid=50633

Update Batch
 

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

Thank you very much
Michae

--
MichaelS
-----------------------------------------------------------------------
MichaelS_'s Profile: http://www.excelforum.com/member.php...fo&userid=2640
View this thread: http://www.excelforum.com/showthread.php?threadid=50633