Strange VBA speed issue based on whether overwritten file originallyexisted or not (?I think?). Excel from Access VBA
 

Excel has a Kill command that can be used to delete files.

I bet that Access has a similar command(???).

Maybe instead of overwriting the file, you could just delete the existing file:

In Excel's VBA, I'd do:

on error resume next
kill ExportFileName
on error goto 0

(on error resume next--just in case that file doesn't exist)



A C wrote:

Hello

We are copying some data into Excel (2000) via VBA code in an Access
database (2000). The general idea is we copy the data into an existing
template spreadsheet, and as such we first open the existing template, and
then do a SaveAs to generate the desired results spreadsheet, and then in
later code we start chucking the data into it and doing various things,
followed by the final save.

For some reason we are getting very *very* different speeds based on whether
the SaveAs is overwritting an existing file (fast) cf the SaveAs making a
brand new file. The speed differences are ~10times greater.

As an example if I had an existing file called c:\Example\myFile.xls, the
VBA code would open the template, execute the .SaveAs method which posts a
warning checking we want to overwrite the existing file, then we do our
processing and data manipulation etc and do a final .Save and close. This
takes ~10secs.
If however we did not have an existing file, the VBA code would open the
template, execute the .SaveAs method which will not post a warning about
overwriting the existing file as the existing file does not exist, then we
do our processing and data manipulation etc and do a final .Save and close.
This takes ~100secs

There is NO difference in execution paths in the code based on whether the
files exist or not, the .SaveAs is executed regardless of whether the file
existed or not. The only perceivable difference is that Excel pops a
warning if the file already existed. The code is posted below

Set xlApp = CreateObject("Excel.Application")
Set xlWorkbook = xlApp.WorkBooks.Open(templateFile)
xlApp.Calculation = xlManual
If bPassword Then
tThisPassWord = GThisPassWord
Else
tThisPassWord = ""
End If

With xlWorkbook
.SaveAs FileName:=ExportFileName, PassWord:=tThisPassWord
End With

<Copy our data over and do some processing and formatting etc

With xlWorkbook
.Save
.Close
End With

We are also getting the very slow execution if we do the following,
*regardless* of whether the file existed previously or not:
xlApp.DisplayAlerts = False
With xlWorkbook
.SaveAs FileName:=ExportFileName, PassWord:=tThisPassWord
End With
xlApp.DisplayAlerts = True
So if we turn off alerts the code execution is slow as well...

Can anyone help us understand what the issue here is? At this stage we are
not looking for workarounds (such as not doing the first saveAs until the
end), we are looking to trace the cause so that we can eliminate it from
this and any other apps we have.

Please reply to group, email is bogus due to spam killing my NG email
address

Thanks for help
Andrew

--

Dave Peterson

Strange VBA speed issue based on whether overwritten file originally existed or not (?I think?). Excel from Access VBA
 

"Dave Peterson" wrote in message
...
Excel has a Kill command that can be used to delete files.

I bet that Access has a similar command(???).

Maybe instead of overwriting the file, you could just delete the existing
file:

In Excel's VBA, I'd do:

on error resume next
kill ExportFileName
on error goto 0

(on error resume next--just in case that file doesn't exist)



Thanks Mr Peterson, but unfortunately that simply results in the slow
situation appearing ie by deleting the file first we are now in the "file
did not exist" situation which is the slow one. (and we tried this idea
already :) )

I am at a loss as to what is going on here... Anyone???
The resulting file is ~3-4Meg, the template prior to entry is ~250kb. Is
there some memory/chache optimisation going on here whereby overwriting a
larger file with a smaller one and then editing the smaller one (which will
eventually become larger ~3-4Mb) is faster...?

Regards
A


A C wrote:

Hello

We are copying some data into Excel (2000) via VBA code in an Access
database (2000). The general idea is we copy the data into an existing
template spreadsheet, and as such we first open the existing template,
and
then do a SaveAs to generate the desired results spreadsheet, and then
in
later code we start chucking the data into it and doing various things,
followed by the final save.

For some reason we are getting very *very* different speeds based on
whether
the SaveAs is overwritting an existing file (fast) cf the SaveAs making
a
brand new file. The speed differences are ~10times greater.

As an example if I had an existing file called c:\Example\myFile.xls,
the
VBA code would open the template, execute the .SaveAs method which posts
a
warning checking we want to overwrite the existing file, then we do our
processing and data manipulation etc and do a final .Save and close.
This
takes ~10secs.
If however we did not have an existing file, the VBA code would open the
template, execute the .SaveAs method which will not post a warning about
overwriting the existing file as the existing file does not exist, then
we
do our processing and data manipulation etc and do a final .Save and
close.
This takes ~100secs

There is NO difference in execution paths in the code based on whether
the
files exist or not, the .SaveAs is executed regardless of whether the
file
existed or not. The only perceivable difference is that Excel pops a
warning if the file already existed. The code is posted below

Set xlApp = CreateObject("Excel.Application")
Set xlWorkbook = xlApp.WorkBooks.Open(templateFile)
xlApp.Calculation = xlManual
If bPassword Then
tThisPassWord = GThisPassWord
Else
tThisPassWord = ""
End If

With xlWorkbook
.SaveAs FileName:=ExportFileName, PassWord:=tThisPassWord
End With

<Copy our data over and do some processing and formatting etc

With xlWorkbook
.Save
.Close
End With

We are also getting the very slow execution if we do the following,
*regardless* of whether the file existed previously or not:
xlApp.DisplayAlerts = False
With xlWorkbook
.SaveAs FileName:=ExportFileName, PassWord:=tThisPassWord
End With
xlApp.DisplayAlerts = True
So if we turn off alerts the code execution is slow as well...

Can anyone help us understand what the issue here is? At this stage we
are
not looking for workarounds (such as not doing the first saveAs until
the
end), we are looking to trace the cause so that we can eliminate it from
this and any other apps we have.

Please reply to group, email is bogus due to spam killing my NG email
address

Thanks for help
Andrew

--

Dave Peterson

Strange VBA speed issue based on whether overwritten fileoriginally existed or not (?I think?). Excel from Access VBA
 

Oops. I got it backwards!!!

If you save a dummy workbook as that name, then save the real one over it, is
that quicker?

dim dummyWkbk as workbook
set dummywkbk = workbooks.add(1) 'single sheet
dummywkbk.saveas yourfilenamevariablehere
dummywkbk.close savechanges:=false

Then save your workbook....

(I've never experienced such a thing... and I don't have a guess why it happens
or how to fix it--and you'll know if the workaround, er, works pretty quickly.)

A C wrote:

"Dave Peterson" wrote in message
...
Excel has a Kill command that can be used to delete files.

I bet that Access has a similar command(???).

Maybe instead of overwriting the file, you could just delete the existing
file:

In Excel's VBA, I'd do:

on error resume next
kill ExportFileName
on error goto 0

(on error resume next--just in case that file doesn't exist)



Thanks Mr Peterson, but unfortunately that simply results in the slow
situation appearing ie by deleting the file first we are now in the "file
did not exist" situation which is the slow one. (and we tried this idea
already :) )

I am at a loss as to what is going on here... Anyone???
The resulting file is ~3-4Meg, the template prior to entry is ~250kb. Is
there some memory/chache optimisation going on here whereby overwriting a
larger file with a smaller one and then editing the smaller one (which will
eventually become larger ~3-4Mb) is faster...?

Regards
A


A C wrote:

Hello

We are copying some data into Excel (2000) via VBA code in an Access
database (2000). The general idea is we copy the data into an existing
template spreadsheet, and as such we first open the existing template,
and
then do a SaveAs to generate the desired results spreadsheet, and then
in
later code we start chucking the data into it and doing various things,
followed by the final save.

For some reason we are getting very *very* different speeds based on
whether
the SaveAs is overwritting an existing file (fast) cf the SaveAs making
a
brand new file. The speed differences are ~10times greater.

As an example if I had an existing file called c:\Example\myFile.xls,
the
VBA code would open the template, execute the .SaveAs method which posts
a
warning checking we want to overwrite the existing file, then we do our
processing and data manipulation etc and do a final .Save and close.
This
takes ~10secs.
If however we did not have an existing file, the VBA code would open the
template, execute the .SaveAs method which will not post a warning about
overwriting the existing file as the existing file does not exist, then
we
do our processing and data manipulation etc and do a final .Save and
close.
This takes ~100secs

There is NO difference in execution paths in the code based on whether
the
files exist or not, the .SaveAs is executed regardless of whether the
file
existed or not. The only perceivable difference is that Excel pops a
warning if the file already existed. The code is posted below

Set xlApp = CreateObject("Excel.Application")
Set xlWorkbook = xlApp.WorkBooks.Open(templateFile)
xlApp.Calculation = xlManual
If bPassword Then
tThisPassWord = GThisPassWord
Else
tThisPassWord = ""
End If

With xlWorkbook
.SaveAs FileName:=ExportFileName, PassWord:=tThisPassWord
End With

<Copy our data over and do some processing and formatting etc

With xlWorkbook
.Save
.Close
End With

We are also getting the very slow execution if we do the following,
*regardless* of whether the file existed previously or not:
xlApp.DisplayAlerts = False
With xlWorkbook
.SaveAs FileName:=ExportFileName, PassWord:=tThisPassWord
End With
xlApp.DisplayAlerts = True
So if we turn off alerts the code execution is slow as well...

Can anyone help us understand what the issue here is? At this stage we
are
not looking for workarounds (such as not doing the first saveAs until
the
end), we are looking to trace the cause so that we can eliminate it from
this and any other apps we have.

Please reply to group, email is bogus due to spam killing my NG email
address

Thanks for help
Andrew

--

Dave Peterson

--

Dave Peterson