HELP - Too long coding for Macro
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
|