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

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

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

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:4326C271.B623D641@verizonXSPAM.net...
> 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

FWIW, this problem is *not* present using Office 2002 on an XP machine

"A C" <no@sp.am> wrote in message
news:8jrVe.10978$iM2.998806@news.xtra.co.nz...
> 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
>
>

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" <petersod@verizonXSPAM.net> wrote in message
> news:4326C271.B623D641@verizonXSPAM.net...
> > 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