Hi guys,
I have about 200 cost sheets all set up to pull from a source page which
then pulls from a master list I would like to know if there is a way to
update the links in all the pages at once? To be more specific my company is
changing from lotus 1-2-3 to the new excel 2007 and I am in charge of
updating all of it. I have in my folder now about 200 cost sheets that need
to be updated to new excel code and the link to the source file changed, now
the code will be similar on all files, although each column has it's own
vlook function so I don't know if that would be able to be automated but I
figured to link to the source book could be changed in all of them at once
any help would be greatly appriciated.
P.S. I am talking about a MASS amount of data the master file has
first sheet with about 4000 lines times 8 columns all code, with 6 sheets
behind it with about 400 lines of all code. Then of course the 500 cost
sheets (FUN FOR ME!!!)

I don't have Lotus-123. Is thhe conversion just opening up each workbook as
wk1 and then saving the file as xls. then going back a changing the file
referrences (links) from .wk1 to .xls?

A macro could be written to do what I described.

"RHein" wrote:

Hi guys,
I have about 200 cost sheets all set up to pull from a source page which
then pulls from a master list I would like to know if there is a way to
update the links in all the pages at once? To be more specific my company is
changing from lotus 1-2-3 to the new excel 2007 and I am in charge of
updating all of it. I have in my folder now about 200 cost sheets that need
to be updated to new excel code and the link to the source file changed, now
the code will be similar on all files, although each column has it's own
vlook function so I don't know if that would be able to be automated but I
figured to link to the source book could be changed in all of them at once
any help would be greatly appriciated.
P.S. I am talking about a MASS amount of data the master file has
first sheet with about 4000 lines times 8 columns all code, with 6 sheets
behind it with about 400 lines of all code. Then of course the 500 cost
sheets (FUN FOR ME!!!)

Well half the process of that is don't I have gone through and changed all
Lotus files to excel (97) which then need to be converted to excel 2007, But
the time waister is every excel 97 page I open I have to update the line from
the Mas.wk3 to the Mas.xls Which I know might now seem like a lot but when
you got 500+ sheets to do it to... well it's a lot of time, Macros would help
but I am totaly ignorant on the subject if someone has something out there
that would help plz post :)

"Joel" wrote:

I don't have Lotus-123. Is thhe conversion just opening up each workbook as
wk1 and then saving the file as xls. then going back a changing the file
referrences (links) from .wk1 to .xls?

A macro could be written to do what I described.

"RHein" wrote:

Hi guys,
I have about 200 cost sheets all set up to pull from a source page which
then pulls from a master list I would like to know if there is a way to
update the links in all the pages at once? To be more specific my company is
changing from lotus 1-2-3 to the new excel 2007 and I am in charge of
updating all of it. I have in my folder now about 200 cost sheets that need
to be updated to new excel code and the link to the source file changed, now
the code will be similar on all files, although each column has it's own
vlook function so I don't know if that would be able to be automated but I
figured to link to the source book could be changed in all of them at once
any help would be greatly appriciated.
P.S. I am talking about a MASS amount of data the master file has
first sheet with about 4000 lines times 8 columns all code, with 6 sheets
behind it with about 400 lines of all code. Then of course the 500 cost
sheets (FUN FOR ME!!!)

I don't havve Lostuc so I'm not sure if this code will work try it

Sub replacewk1()
Const FolderName = "c:\temp\test"

First = True
Do
If First = True Then
ReadFile = Dir(FolderName & "\*.xls")
First = False
Else
ReadFile = Dir()
End If
If ReadFile < "" Then

Workbooks.Open Filename:=FolderName & "\" & ReadFile

For Each sht In ActiveWorkbook.Sheets
For Each cell In sht.UsedRange
If InStr(cell.Formula, ".wk1") Then
newformula = Replace(cell.Formula, ".wk1", ".xls")
cell.Formula = newformula
End If
Next cell
Next sht
ActiveWorkbook.Close SaveChanges:=True
End If
Loop While ReadFile < ""
End Sub

"RHein" wrote:

Well half the process of that is don't I have gone through and changed all
Lotus files to excel (97) which then need to be converted to excel 2007, But
the time waister is every excel 97 page I open I have to update the line from
the Mas.wk3 to the Mas.xls Which I know might now seem like a lot but when
you got 500+ sheets to do it to... well it's a lot of time, Macros would help
but I am totaly ignorant on the subject if someone has something out there
that would help plz post :)

"Joel" wrote:

I don't have Lotus-123. Is thhe conversion just opening up each workbook as
wk1 and then saving the file as xls. then going back a changing the file
referrences (links) from .wk1 to .xls?

A macro could be written to do what I described.

"RHein" wrote:

Hi guys,
I have about 200 cost sheets all set up to pull from a source page which
then pulls from a master list I would like to know if there is a way to
update the links in all the pages at once? To be more specific my company is
changing from lotus 1-2-3 to the new excel 2007 and I am in charge of
updating all of it. I have in my folder now about 200 cost sheets that need
to be updated to new excel code and the link to the source file changed, now
the code will be similar on all files, although each column has it's own
vlook function so I don't know if that would be able to be automated but I
figured to link to the source book could be changed in all of them at once
any help would be greatly appriciated.
P.S. I am talking about a MASS amount of data the master file has
first sheet with about 4000 lines times 8 columns all code, with 6 sheets
behind it with about 400 lines of all code. Then of course the 500 cost
sheets (FUN FOR ME!!!)