Does anyone have any suggestions on how to solve the problem of having too
long coding for Macro? I don't want to split the coding into 2 macro
separately.
Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

Coding is shown as below:

Sub Temp()

Application.DisplayAlerts = False

Workbooks.Open Filename:="C:\File1.xls", UpdateLinks:=3
Workbooks("File1.xls").Close savechanges:=True

Workbooks.Open Filename:="C:\File2.xls", UpdateLinks:=3
Workbooks("File2.xls").Close savechanges:=True

Workbooks.Open Filename:="C:\File3.xls", UpdateLinks:=3
Workbooks("File3.xls").Close savechanges:=True

....

Workbooks.Open Filename:="C:\File1000.xls", UpdateLinks:=3
Workbooks("File1000.xls").Close savechanges:=True

Workbooks("UpdateFiles").Close savechanges:=True

End Sub

You could build an array of names in your code--but even better, you could put
your names in a worksheet.


Option Explicit
Sub Temp2()

Dim myRng as Range
dim myCell as range
dim wkbk as workbook

with worksheets("sheet9999")
set myRng = .range("a2",.cells(.rows.count,"A").end(xlup))
end with

for each mycell in myrng.cells
set wkbk = Workbooks.Open (Filename:=mycell.value, UpdateLinks:=3)
wkbk.Close savechanges:=True
next mycell

Workbooks("UpdateFiles").Close savechanges:=True

End Sub



Eric wrote:

Does anyone have any suggestions on how to solve the problem of having too
long coding for Macro? I don't want to split the coding into 2 macro
separately.
Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

Coding is shown as below:

Sub Temp()

Application.DisplayAlerts = False

Workbooks.Open Filename:="C:\File1.xls", UpdateLinks:=3
Workbooks("File1.xls").Close savechanges:=True

Workbooks.Open Filename:="C:\File2.xls", UpdateLinks:=3
Workbooks("File2.xls").Close savechanges:=True

Workbooks.Open Filename:="C:\File3.xls", UpdateLinks:=3
Workbooks("File3.xls").Close savechanges:=True

...

Workbooks.Open Filename:="C:\File1000.xls", UpdateLinks:=3
Workbooks("File1000.xls").Close savechanges:=True

Workbooks("UpdateFiles").Close savechanges:=True

End Sub

--

Dave Peterson

Thank you very much for your reply
The For loop does not work for my case, could you please give me any
suggestions on how to update the files one by one? because the filename is
not structured in this format file + number.
Thank you for any suggestions
Eric


"Dave Peterson" wrote:

You could build an array of names in your code--but even better, you could put
your names in a worksheet.


Option Explicit
Sub Temp2()

Dim myRng as Range
dim myCell as range
dim wkbk as workbook

with worksheets("sheet9999")
set myRng = .range("a2",.cells(.rows.count,"A").end(xlup))
end with

for each mycell in myrng.cells
set wkbk = Workbooks.Open (Filename:=mycell.value, UpdateLinks:=3)
wkbk.Close savechanges:=True
next mycell

Workbooks("UpdateFiles").Close savechanges:=True

End Sub



Eric wrote:

Does anyone have any suggestions on how to solve the problem of having too
long coding for Macro? I don't want to split the coding into 2 macro
separately.
Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

Coding is shown as below:

Sub Temp()

Application.DisplayAlerts = False

Workbooks.Open Filename:="C:\File1.xls", UpdateLinks:=3
Workbooks("File1.xls").Close savechanges:=True

Workbooks.Open Filename:="C:\File2.xls", UpdateLinks:=3
Workbooks("File2.xls").Close savechanges:=True

Workbooks.Open Filename:="C:\File3.xls", UpdateLinks:=3
Workbooks("File3.xls").Close savechanges:=True

...

Workbooks.Open Filename:="C:\File1000.xls", UpdateLinks:=3
Workbooks("File1000.xls").Close savechanges:=True

Workbooks("UpdateFiles").Close savechanges:=True

End Sub

--

Dave Peterson

The code I suggested doesn't rely on the names of the files looking like what
you posted.

It relies on the names being in a column in a worksheet.

Eric wrote:

Thank you very much for your reply
The For loop does not work for my case, could you please give me any
suggestions on how to update the files one by one? because the filename is
not structured in this format file + number.
Thank you for any suggestions
Eric

"Dave Peterson" wrote:

You could build an array of names in your code--but even better, you could put
your names in a worksheet.


Option Explicit
Sub Temp2()

Dim myRng as Range
dim myCell as range
dim wkbk as workbook

with worksheets("sheet9999")
set myRng = .range("a2",.cells(.rows.count,"A").end(xlup))
end with

for each mycell in myrng.cells
set wkbk = Workbooks.Open (Filename:=mycell.value, UpdateLinks:=3)
wkbk.Close savechanges:=True
next mycell

Workbooks("UpdateFiles").Close savechanges:=True

End Sub



Eric wrote:

Does anyone have any suggestions on how to solve the problem of having too
long coding for Macro? I don't want to split the coding into 2 macro
separately.
Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

Coding is shown as below:

Sub Temp()

Application.DisplayAlerts = False

Workbooks.Open Filename:="C:\File1.xls", UpdateLinks:=3
Workbooks("File1.xls").Close savechanges:=True

Workbooks.Open Filename:="C:\File2.xls", UpdateLinks:=3
Workbooks("File2.xls").Close savechanges:=True

Workbooks.Open Filename:="C:\File3.xls", UpdateLinks:=3
Workbooks("File3.xls").Close savechanges:=True

...

Workbooks.Open Filename:="C:\File1000.xls", UpdateLinks:=3
Workbooks("File1000.xls").Close savechanges:=True

Workbooks("UpdateFiles").Close savechanges:=True

End Sub

--

Dave Peterson


--

Dave Peterson

Hi Dave Peterson:
Thank you very much for your suggestions
There is a minor problem on closing file.

The following coding is working, when the specific file is opened, there is
no need for the drive and directory for closing the file.

Workbooks.Open Filename:="E:\dir\File1.xls", UpdateLinks:=3
Workbooks("File1.xls").Close savechanges:=True

For the following code, it may contains the drive and directory, and cause
not able to close the file. Do you have any suggestions on how to fix the
code please?

for each mycell in myrng.cells
set wkbk = Workbooks.Open (Filename:=mycell.value, UpdateLinks:=3)
wkbk.Close savechanges:=True
next mycell

I look forward to your reply
Thank you very much for any suggestions
Eric

"Dave Peterson" wrote:

You could build an array of names in your code--but even better, you could put
your names in a worksheet.


Option Explicit
Sub Temp2()

Dim myRng as Range
dim myCell as range
dim wkbk as workbook

with worksheets("sheet9999")
set myRng = .range("a2",.cells(.rows.count,"A").end(xlup))
end with

for each mycell in myrng.cells
set wkbk = Workbooks.Open (Filename:=mycell.value, UpdateLinks:=3)
wkbk.Close savechanges:=True
next mycell

Workbooks("UpdateFiles").Close savechanges:=True

End Sub



Eric wrote:

Does anyone have any suggestions on how to solve the problem of having too
long coding for Macro? I don't want to split the coding into 2 macro
separately.
Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

Coding is shown as below:

Sub Temp()

Application.DisplayAlerts = False

Workbooks.Open Filename:="C:\File1.xls", UpdateLinks:=3
Workbooks("File1.xls").Close savechanges:=True

Workbooks.Open Filename:="C:\File2.xls", UpdateLinks:=3
Workbooks("File2.xls").Close savechanges:=True

Workbooks.Open Filename:="C:\File3.xls", UpdateLinks:=3
Workbooks("File3.xls").Close savechanges:=True

...

Workbooks.Open Filename:="C:\File1000.xls", UpdateLinks:=3
Workbooks("File1000.xls").Close savechanges:=True

Workbooks("UpdateFiles").Close savechanges:=True

End Sub

--

Dave Peterson

There's nothing in that code that closes the workbook that relies on the name or
path of the file.

If you're having trouble, then you may want to post the details of what happens.


Eric wrote:

Hi Dave Peterson:
Thank you very much for your suggestions
There is a minor problem on closing file.

The following coding is working, when the specific file is opened, there is
no need for the drive and directory for closing the file.

Workbooks.Open Filename:="E:\dir\File1.xls", UpdateLinks:=3
Workbooks("File1.xls").Close savechanges:=True

For the following code, it may contains the drive and directory, and cause
not able to close the file. Do you have any suggestions on how to fix the
code please?

for each mycell in myrng.cells
set wkbk = Workbooks.Open (Filename:=mycell.value, UpdateLinks:=3)
wkbk.Close savechanges:=True
next mycell

I look forward to your reply
Thank you very much for any suggestions
Eric

"Dave Peterson" wrote:

You could build an array of names in your code--but even better, you could put
your names in a worksheet.


Option Explicit
Sub Temp2()

Dim myRng as Range
dim myCell as range
dim wkbk as workbook

with worksheets("sheet9999")
set myRng = .range("a2",.cells(.rows.count,"A").end(xlup))
end with

for each mycell in myrng.cells
set wkbk = Workbooks.Open (Filename:=mycell.value, UpdateLinks:=3)
wkbk.Close savechanges:=True
next mycell

Workbooks("UpdateFiles").Close savechanges:=True

End Sub



Eric wrote:

Does anyone have any suggestions on how to solve the problem of having too
long coding for Macro? I don't want to split the coding into 2 macro
separately.
Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

Coding is shown as below:

Sub Temp()

Application.DisplayAlerts = False

Workbooks.Open Filename:="C:\File1.xls", UpdateLinks:=3
Workbooks("File1.xls").Close savechanges:=True

Workbooks.Open Filename:="C:\File2.xls", UpdateLinks:=3
Workbooks("File2.xls").Close savechanges:=True

Workbooks.Open Filename:="C:\File3.xls", UpdateLinks:=3
Workbooks("File3.xls").Close savechanges:=True

...

Workbooks.Open Filename:="C:\File1000.xls", UpdateLinks:=3
Workbooks("File1000.xls").Close savechanges:=True

Workbooks("UpdateFiles").Close savechanges:=True

End Sub

--

Dave Peterson


--

Dave Peterson

There is an error 1004 pointing to the following line
set wkbk = Workbooks.Open (Filename:=mycell.value, UpdateLinks:=3)
and the file cannot be opened at all

On the other hands, if I try the following coding, the file can be opened,
but it cannot close without any pop up error message.
Workbooks.Open Filename:="E:\dir\File1.xls", UpdateLinks:=3
Workbooks("File1.xls").Close savechanges:=True

Do you have any suggestions?
I look forward to your reply
Thank you very much for any suggestions
Eric

"Dave Peterson" wrote:

There's nothing in that code that closes the workbook that relies on the name or
path of the file.

If you're having trouble, then you may want to post the details of what happens.


Eric wrote:

Hi Dave Peterson:
Thank you very much for your suggestions
There is a minor problem on closing file.

The following coding is working, when the specific file is opened, there is
no need for the drive and directory for closing the file.

Workbooks.Open Filename:="E:\dir\File1.xls", UpdateLinks:=3
Workbooks("File1.xls").Close savechanges:=True

For the following code, it may contains the drive and directory, and cause
not able to close the file. Do you have any suggestions on how to fix the
code please?

for each mycell in myrng.cells
set wkbk = Workbooks.Open (Filename:=mycell.value, UpdateLinks:=3)
wkbk.Close savechanges:=True
next mycell

I look forward to your reply
Thank you very much for any suggestions
Eric

"Dave Peterson" wrote:

You could build an array of names in your code--but even better, you could put
your names in a worksheet.


Option Explicit
Sub Temp2()

Dim myRng as Range
dim myCell as range
dim wkbk as workbook

with worksheets("sheet9999")
set myRng = .range("a2",.cells(.rows.count,"A").end(xlup))
end with

for each mycell in myrng.cells
set wkbk = Workbooks.Open (Filename:=mycell.value, UpdateLinks:=3)
wkbk.Close savechanges:=True
next mycell

Workbooks("UpdateFiles").Close savechanges:=True

End Sub



Eric wrote:

Does anyone have any suggestions on how to solve the problem of having too
long coding for Macro? I don't want to split the coding into 2 macro
separately.
Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

Coding is shown as below:

Sub Temp()

Application.DisplayAlerts = False

Workbooks.Open Filename:="C:\File1.xls", UpdateLinks:=3
Workbooks("File1.xls").Close savechanges:=True

Workbooks.Open Filename:="C:\File2.xls", UpdateLinks:=3
Workbooks("File2.xls").Close savechanges:=True

Workbooks.Open Filename:="C:\File3.xls", UpdateLinks:=3
Workbooks("File3.xls").Close savechanges:=True

...

Workbooks.Open Filename:="C:\File1000.xls", UpdateLinks:=3
Workbooks("File1000.xls").Close savechanges:=True

Workbooks("UpdateFiles").Close savechanges:=True

End Sub

--

Dave Peterson


--

Dave Peterson

There is the coding for macro

Under the spreadsheet, in cell A1, I insert the name
C:\WIN98\Desktop\Economic.xls

================================
Option Explicit
Sub Temp2()

Dim myRng As Range
Dim myCell As Range
Dim wkbk As Workbook

With Worksheets("Lists")
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In myRng.Cells
Set wkbk = Workbooks.Open(Filename:=myCell.Value, UpdateLinks:=3)
wkbk.Close savechanges:=True
Next myCell

Workbooks("UpdateFiles").Close savechanges:=True

End Sub
================================

Do you have any suggestions?
Thank you very much for any suggestions
Eric

"Dave Peterson" wrote:

There's nothing in that code that closes the workbook that relies on the name or
path of the file.

If you're having trouble, then you may want to post the details of what happens.


Eric wrote:

Hi Dave Peterson:
Thank you very much for your suggestions
There is a minor problem on closing file.

The following coding is working, when the specific file is opened, there is
no need for the drive and directory for closing the file.

Workbooks.Open Filename:="E:\dir\File1.xls", UpdateLinks:=3
Workbooks("File1.xls").Close savechanges:=True

For the following code, it may contains the drive and directory, and cause
not able to close the file. Do you have any suggestions on how to fix the
code please?

for each mycell in myrng.cells
set wkbk = Workbooks.Open (Filename:=mycell.value, UpdateLinks:=3)
wkbk.Close savechanges:=True
next mycell

I look forward to your reply
Thank you very much for any suggestions
Eric

"Dave Peterson" wrote:

You could build an array of names in your code--but even better, you could put
your names in a worksheet.


Option Explicit
Sub Temp2()

Dim myRng as Range
dim myCell as range
dim wkbk as workbook

with worksheets("sheet9999")
set myRng = .range("a2",.cells(.rows.count,"A").end(xlup))
end with

for each mycell in myrng.cells
set wkbk = Workbooks.Open (Filename:=mycell.value, UpdateLinks:=3)
wkbk.Close savechanges:=True
next mycell

Workbooks("UpdateFiles").Close savechanges:=True

End Sub



Eric wrote:

Does anyone have any suggestions on how to solve the problem of having too
long coding for Macro? I don't want to split the coding into 2 macro
separately.
Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

Coding is shown as below:

Sub Temp()

Application.DisplayAlerts = False

Workbooks.Open Filename:="C:\File1.xls", UpdateLinks:=3
Workbooks("File1.xls").Close savechanges:=True

Workbooks.Open Filename:="C:\File2.xls", UpdateLinks:=3
Workbooks("File2.xls").Close savechanges:=True

Workbooks.Open Filename:="C:\File3.xls", UpdateLinks:=3
Workbooks("File3.xls").Close savechanges:=True

...

Workbooks.Open Filename:="C:\File1000.xls", UpdateLinks:=3
Workbooks("File1000.xls").Close savechanges:=True

Workbooks("UpdateFiles").Close savechanges:=True

End Sub

--

Dave Peterson


--

Dave Peterson

The code starts processing the data in A2--I figured you'd put headers in A1.

But you could change this:
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
to:
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))

I'd add a couple of checks:

Option Explicit
Sub Temp2()

Dim myRng as Range
dim myCell as range
dim wkbk as workbook

with worksheets("sheet9999")
'still starting in row 2!
set myRng = .range("a2",.cells(.rows.count,"A").end(xlup))
end with

for each mycell in myrng.cells
set wkbk = nothing
on error resume next
set wkbk = Workbooks.Open(Filename:=mycell.value, UpdateLinks:=3)
on error goto 0

if wkbk is nothing then
mycell.offset(0,1).value = "Failed to open!"
else
wkbk.Close savechanges:=True
mycell.offset(0,1).value = "ok"
end if
next mycell

'better to include an extension
Workbooks("UpdateFiles.xls").Close savechanges:=True

End Sub

(Untested, uncompiled.)

Eric wrote:

There is the coding for macro

Under the spreadsheet, in cell A1, I insert the name
C:\WIN98\Desktop\Economic.xls

================================
Option Explicit
Sub Temp2()

Dim myRng As Range
Dim myCell As Range
Dim wkbk As Workbook

With Worksheets("Lists")
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In myRng.Cells
Set wkbk = Workbooks.Open(Filename:=myCell.Value, UpdateLinks:=3)
wkbk.Close savechanges:=True
Next myCell

Workbooks("UpdateFiles").Close savechanges:=True

End Sub
================================

Do you have any suggestions?
Thank you very much for any suggestions
Eric

"Dave Peterson" wrote:

There's nothing in that code that closes the workbook that relies on the name or
path of the file.

If you're having trouble, then you may want to post the details of what happens.


Eric wrote:

Hi Dave Peterson:
Thank you very much for your suggestions
There is a minor problem on closing file.

The following coding is working, when the specific file is opened, there is
no need for the drive and directory for closing the file.

Workbooks.Open Filename:="E:\dir\File1.xls", UpdateLinks:=3
Workbooks("File1.xls").Close savechanges:=True

For the following code, it may contains the drive and directory, and cause
not able to close the file. Do you have any suggestions on how to fix the
code please?

for each mycell in myrng.cells
set wkbk = Workbooks.Open (Filename:=mycell.value, UpdateLinks:=3)
wkbk.Close savechanges:=True
next mycell

I look forward to your reply
Thank you very much for any suggestions
Eric

"Dave Peterson" wrote:

You could build an array of names in your code--but even better, you could put
your names in a worksheet.


Option Explicit
Sub Temp2()

Dim myRng as Range
dim myCell as range
dim wkbk as workbook

with worksheets("sheet9999")
set myRng = .range("a2",.cells(.rows.count,"A").end(xlup))
end with

for each mycell in myrng.cells
set wkbk = Workbooks.Open (Filename:=mycell.value, UpdateLinks:=3)
wkbk.Close savechanges:=True
next mycell

Workbooks("UpdateFiles").Close savechanges:=True

End Sub



Eric wrote:

Does anyone have any suggestions on how to solve the problem of having too
long coding for Macro? I don't want to split the coding into 2 macro
separately.
Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

Coding is shown as below:

Sub Temp()

Application.DisplayAlerts = False

Workbooks.Open Filename:="C:\File1.xls", UpdateLinks:=3
Workbooks("File1.xls").Close savechanges:=True

Workbooks.Open Filename:="C:\File2.xls", UpdateLinks:=3
Workbooks("File2.xls").Close savechanges:=True

Workbooks.Open Filename:="C:\File3.xls", UpdateLinks:=3
Workbooks("File3.xls").Close savechanges:=True

...

Workbooks.Open Filename:="C:\File1000.xls", UpdateLinks:=3
Workbooks("File1000.xls").Close savechanges:=True

Workbooks("UpdateFiles").Close savechanges:=True

End Sub

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson