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

"Claus Busch" wrote:

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



Hi Claus. Indeed this improves the code that I originally
posted, but in reality I will not be writing the same number "1"
to each workbook at cell A1. Assume that a random number
will be written to A1 during each iteration of the loop.

Also, the outer For-Next loop will run as high as 1 to 100,
not 1 to 5.

I aplogize for posting a streamlined version of the code. I
was hoping to keep my code simple. I didn't think it was
necessary to post ALL of the details, but I should have
mentioned the part about the random numbers.

Also, I thought maybe Excel 2013 was the source of the
somewhat slower run time.

Hi Claus. Indeed this improves the code that I originally
posted, but in reality I will not be writing the same number "1"
to each workbook at cell A1. Assume that a random number
will be written to A1 during each iteration of the loop.

Also, the outer For-Next loop will run as high as 1 to 100,
not 1 to 5.

I aplogize for posting a streamlined version of the code. I
was hoping to keep my code simple. I didn't think it was
necessary to post ALL of the details, but I should have
mentioned the part about the random numbers.

Also, I thought maybe Excel 2013 was the source of the
somewhat slower run time.

Robert,
How are you generating the random numbers, and what is their purpose?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

"GS" wrote:

Robert,
How are you generating the random numbers, and what is their purpose?


The user will enter a number that is specific to our data. The number
chosen will be any integer greater than 1. Also, the chosen number
can never be predicted; that's why I say it's a random number.

So, suppose a user chooses number 32. That means:
version1.xlsm will have 32 in cell A1, and...
version2.xlsm will have 33 in cell A1, and...
version3.xlsm will have 34 in cell A1, and...
etcetera, etcetera...

Tomorrow, a user might choose 1648. That means:
version1.xlsm will have 1648 in cell A1, and...
version2.xlsm will have 1649 in cell A1, and...
version3.xlsm will have 1650 in cell A1, and...
etcetera, etcetera.....

Do you see in each file version how the chosen
"random" number gets incremented by 1?

I think the main problem that's hindering my program
is the combination of the Workbooks.Open(),
ActiveWorkbooks.SaveAs(), and .Save() functions, and
it must be specific to Excel 2013. It just seems slower than
before, so I was kinda just wondering if everyone else noticed
reduced speeds in Excel 2013.

"GS" wrote:

Robert,
How are you generating the random numbers, and what is their
purpose?


The user will enter a number that is specific to our data. The
number
chosen will be any integer greater than 1. Also, the chosen number
can never be predicted; that's why I say it's a random number.

So, suppose a user chooses number 32. That means:
version1.xlsm will have 32 in cell A1, and...
version2.xlsm will have 33 in cell A1, and...
version3.xlsm will have 34 in cell A1, and...
etcetera, etcetera...

Tomorrow, a user might choose 1648. That means:
version1.xlsm will have 1648 in cell A1, and...
version2.xlsm will have 1649 in cell A1, and...
version3.xlsm will have 1650 in cell A1, and...
etcetera, etcetera.....

Do you see in each file version how the chosen
"random" number gets incremented by 1?

I think the main problem that's hindering my program
is the combination of the Workbooks.Open(),
ActiveWorkbooks.SaveAs(), and .Save() functions, and
it must be specific to Excel 2013. It just seems slower than
before, so I was kinda just wondering if everyone else noticed
reduced speeds in Excel 2013.

You could use an InputBox to get the new 'starting number' for each
run, then increment that each time for however many files to be
created.

Why use a blank workbook when it will be quicker to just create new
ones?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Robert,

Am Wed, 25 Nov 2015 18:51:23 -0700 schrieb Robert Crandal:

The user will enter a number that is specific to our data. The number
chosen will be any integer greater than 1. Also, the chosen number
can never be predicted; that's why I say it's a random number.

So, suppose a user chooses number 32. That means:
version1.xlsm will have 32 in cell A1, and...
version2.xlsm will have 33 in cell A1, and...
version3.xlsm will have 34 in cell A1, and...
etcetera, etcetera...

do you want to copy "main" because of the macros? What macros do you
have in "main"? Macros in modules or event macros?
Do you overwrite the existing workbooks if you name the new ones always
"version" & i?


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

"GS" wrote:

Why use a blank workbook when it will be quicker to just create new ones?


Hi GS and Claus. I was away for a long break, and I wasn't able
to respond to your questions. Sorry about that.

In truth, my actual source workbook is not blank. It is filled
with data on hidden sheets. It also contains VBA code and
macros.

I basically run a loop that opens the source workbook, fills
in some data, saves the workbook under a new unique filename,
and then closes that workbook, etc..repeat, etc....

Anyways, I think I'm satisfied with how the current code runs.
It seems to run a bit smoother now. Maybe it was just a network
anamoly or OS glitch that caused a brief lag.

Thanks your your help. You guys are the best.