I have the following code:

'--------------------------------------------------------
Sub TheCode()
Dim j As Integer
Dim newfile As String
Application.ScreenUpdating = False

For j = 1 To 5
Workbooks.Open Filename:="C:\main.xlsm"
newfile = "version" & CStr(j) & ".xlsm"
ActiveWorkbook.SaveAs Filename:="C:\" & newfile, _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
CreateBackup:=False
Workbooks(newfile).Sheets(1).Range("A1") = "1"
Workbooks(newfile).Save
Workbooks(newfile).Close
Next j

Application.ScreenUpdating = True
End Sub
'--------------------------------------------------------

This code requires that a blank Excel file named "main.xlsm"
exists on the C: drive. The code then creates 5 new files on
the C: drive.

Does anyone know if this code can be improved or
optimized to run faster? Would anybody code this
differently?

I'm asking for a faster solution because this code seems
to run much much slower on Excel 2013, as compared to
Excel 2010. Are there any commands here that are
inefficient for Excel 2013?

Thanx!

Hi Robert,

Am Wed, 25 Nov 2015 04:30:20 -0700 schrieb Robert Crandal:

This code requires that a blank Excel file named "main.xlsm"
exists on the C: drive. The code then creates 5 new files on
the C: drive.

without opening the source file and without writing a 1 to range("A1")
Only copying:

Sub Test()
Dim FSO As Object
Dim j As Long

Set FSO = CreateObject("Scripting.FileSystemObject")
For j = 1 To 5
FSO.CopyFile _
"C:\main.xlsm", "C:\version" & j & ".xlsm"
Next

End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Robert Crandal wrote:
I have the following code:

'--------------------------------------------------------
Sub TheCode()
Dim j As Integer
Dim newfile As String
Application.ScreenUpdating = False

For j = 1 To 5
Workbooks.Open Filename:="C:\main.xlsm"
newfile = "version" & CStr(j) & ".xlsm"
ActiveWorkbook.SaveAs Filename:="C:\" & newfile, _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
CreateBackup:=False
Workbooks(newfile).Sheets(1).Range("A1") = "1"
Workbooks(newfile).Save
Workbooks(newfile).Close
Next j

Application.ScreenUpdating = True
End Sub
'--------------------------------------------------------

This code requires that a blank Excel file named "main.xlsm"
exists on the C: drive. The code then creates 5 new files on
the C: drive.

Does anyone know if this code can be improved or
optimized to run faster? Would anybody code this
differently?

I'm asking for a faster solution because this code seems
to run much much slower on Excel 2013, as compared to
Excel 2010. Are there any commands here that are
inefficient for Excel 2013?

Thanx!



Copy files at OS level and if you have to modify them do that at XML
level.

"witek" wrote:

Copy files at OS level and if you have to modify them do that at XML
level.


How do you modify an Excel file at the "XML level"? Do you have
an example that would achieve the same effect as the code that I
provided earlier?

Robert Crandal wrote:
"witek" wrote:

Copy files at OS level and if you have to modify them do that at XML
level.


How do you modify an Excel file at the "XML level"? Do you have
an example that would achieve the same effect as the code that I
provided earlier?



search open xml SDK .
C# will be easiest way to do that.
You will find examples on the Internet.
I do not have anything ready.

"witek" wrote:

search open xml SDK .
C# will be easiest way to do that.
You will find examples on the Internet.
I do not have anything ready.


Unless this xml SDK is pre-packaged with Excel 2013,
I will not be able to use it. No external software may
be installed on these computers. Sorry I forgot to mention
that.

I remember that ADO was another option for editing
Excel files without opening the file in Excel, so I might
try that. Thanks.

Hi Robert,

Am Wed, 25 Nov 2015 12:39:35 -0700 schrieb Robert Crandal:

I remember that ADO was another option for editing
Excel files without opening the file in Excel, so I might
try that.

open workbook "main", write a 1 in sheet1 range("A1"), copy that
workbook and save it with the new name, delete the 1 in "main" and close
and save:

Sub Test()
Dim FSO As Object
Dim j As Long

Set FSO = CreateObject("Scripting.FileSystemObject")
Workbooks.Open "C:\main.xlsm"
With ActiveWorkbook
Sheets(1).Range("A1") = 1
.Save
End With

For j = 1 To 5
FSO.CopyFile _
"C:\main.xlsm", "C:\version" & j & ".xlsm"
Next
With ActiveWorkbook
Sheets(1).Range("A1").ClearContents
.Close savechanges:=True
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional