Hello,

I would like to open, (if possible Update), Save and Close automatically
several excel 2007 files.
Actually I do this manually file by file and I lose 1/2 days in each week.
I' m looking for an process that simplify this task.

Thanks,
Hugo Pinto

Hi hugo

Maybe this page is a start
http://www.rondebruin.nl/copy4.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Hugo Pinto" wrote in message ...
Hello,

I would like to open, (if possible Update), Save and Close automatically
several excel 2007 files.
Actually I do this manually file by file and I lose 1/2 days in each week.
I' m looking for an process that simplify this task.

Thanks,
Hugo Pinto

Hi Ron,

thanks for the answer. You have an excelent site... but I'm not an expert in
programming and I've some dificult to understand and built all the code that
I need (Open, update references, save and close).

Is there any othe way to help me?

Sorry and Thanks,
Hugo Pinto

"Ron de Bruin" wrote:

Hi hugo

Maybe this page is a start
http://www.rondebruin.nl/copy4.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Hugo Pinto" wrote in message ...
Hello,

I would like to open, (if possible Update), Save and Close automatically
several excel 2007 files.
Actually I do this manually file by file and I lose 1/2 days in each week.
I' m looking for an process that simplify this task.

Thanks,
Hugo Pinto

What do you want to do in every workbook ?


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Hugo Pinto" wrote in message ...
Hi Ron,

thanks for the answer. You have an excelent site... but I'm not an expert in
programming and I've some dificult to understand and built all the code that
I need (Open, update references, save and close).

Is there any othe way to help me?

Sorry and Thanks,
Hugo Pinto

"Ron de Bruin" wrote:

Hi hugo

Maybe this page is a start
http://www.rondebruin.nl/copy4.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Hugo Pinto" wrote in message ...
Hello,

I would like to open, (if possible Update), Save and Close automatically
several excel 2007 files.
Actually I do this manually file by file and I lose 1/2 days in each week.
I' m looking for an process that simplify this task.

Thanks,
Hugo Pinto

What I wnat to do to each file is:
Open it, then answer yes to update, save and close it.


"Ron de Bruin" wrote:

What do you want to do in every workbook ?


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Hugo Pinto" wrote in message ...
Hi Ron,

thanks for the answer. You have an excelent site... but I'm not an expert in
programming and I've some dificult to understand and built all the code that
I need (Open, update references, save and close).

Is there any othe way to help me?

Sorry and Thanks,
Hugo Pinto

"Ron de Bruin" wrote:

Hi hugo

Maybe this page is a start
http://www.rondebruin.nl/copy4.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Hugo Pinto" wrote in message ...
Hello,

I would like to open, (if possible Update), Save and Close automatically
several excel 2007 files.
Actually I do this manually file by file and I lose 1/2 days in each week.
I' m looking for an process that simplify this task.

Thanks,
Hugo Pinto

Hi Hugo

We copy this macro in a standard module (untested)

See
http://www.rondebruin.nl/code.htm

Change this line in the code(path to the folder with the files) and run the macro
For testing use a folder with a few test files

MyPath = "C:\Users\Ron\test"


Sub Example()
Dim MyPath As String, FilesInPath As String
Dim MyFiles() As String, Fnum As Long
Dim mybook As Workbook
Dim CalcMode As Long
Dim sh As Worksheet
Dim ErrorYes As Boolean

'Fill in the path\folder where the files are
MyPath = "C:\Users\Ron\test"

'Add a slash at the end if the user forget it
If Right(MyPath, 1) < "\" Then
MyPath = MyPath & "\"
End If

'If there are no Excel files in the folder exit the sub
FilesInPath = Dir(MyPath & "*.xl*")
If FilesInPath = "" Then
MsgBox "No files found"
Exit Sub
End If

'Fill the array(myFiles)with the list of Excel files in the folder
Fnum = 0
Do While FilesInPath < ""
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = FilesInPath
FilesInPath = Dir()
Loop

'Change ScreenUpdating, Calculation and EnableEvents
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With

'Loop through all files in the array(myFiles)
If Fnum 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)
Set mybook = Nothing
On Error Resume Next
Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum), UpdateLinks:=3)
On Error GoTo 0

If Not mybook Is Nothing Then

mybook.Close savechanges:=True

End If

Next Fnum
End If

'Restore ScreenUpdating, Calculation and EnableEvents
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = CalcMode
End With
End Sub



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Hugo Pinto" wrote in message ...
What I wnat to do to each file is:
Open it, then answer yes to update, save and close it.


"Ron de Bruin" wrote:

What do you want to do in every workbook ?


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Hugo Pinto" wrote in message ...
Hi Ron,

thanks for the answer. You have an excelent site... but I'm not an expert in
programming and I've some dificult to understand and built all the code that
I need (Open, update references, save and close).

Is there any othe way to help me?

Sorry and Thanks,
Hugo Pinto

"Ron de Bruin" wrote:

Hi hugo

Maybe this page is a start
http://www.rondebruin.nl/copy4.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Hugo Pinto" wrote in message ...
Hello,

I would like to open, (if possible Update), Save and Close automatically
several excel 2007 files.
Actually I do this manually file by file and I lose 1/2 days in each week.
I' m looking for an process that simplify this task.

Thanks,
Hugo Pinto