Open, Save and close Excel 2007 file with a cmd command
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 |
Open, Save and close Excel 2007 file with a cmd command
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 |
Open, Save and close Excel 2007 file with a cmd command
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 |
Open, Save and close Excel 2007 file with a cmd command
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 |
Open, Save and close Excel 2007 file with a cmd command
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 |
Open, Save and close Excel 2007 file with a cmd command
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 |
All times are GMT +1. The time now is 01:57 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com