Export from Access to Excel - Personal.xls issue

[Jan]

Hi,
I hope that someone can help me, I have read through heaps of other
questions and answers both from the Access side and also from the Excel
threads, but didn’t find a solution. My issue revolves around sending data
from Access to Excel. From my Access database I open an Excel template, pass
in the relevant data and then save as a new excel file. This code works fine
for all the users except 1 who uses Excel a lot and has recorded macros/code
in his Personal.xls. When this user runs the procedure from the database and
Excel is opened, the ‘Personal.xls’ is opened (as expected). Even though my
code points to a specific (named) Excel template to be opened, the data is
placed onto the Sheet in Personal.xls. How can I ensure that the data gets to
the correct Excel file (NOT personal.xls)?
Thanks in anticipation

 

[Klatuu]

In your automation code, you need to do a couple of things.
First, you need to see if Excel is already running on the computer and use
the GetObject method. Only use the CreateObject method if Excel is not
already running. If you use the GetObject, you want to be sure you don't use
the Application.Quit method when you are done as this will close the user's
other Excel files they have open and will not save them. That will make you
a very unpopular person. You only need to set your Excel Application object
to Nothing. But, if you use the CreateObject, you need to be sure you use the
Quit.

Now you also need to open the Excel file as a workbook object and select the
worksheet you want to use as a Worksheet object. The issue here is that it
is very important you be absolutely specific in your object referencing.
What will happen when using automation is if Access can't tell which object
path you are referring to, it will make one up on its own. In this case, if
multiple files are open, it may use the wrong file. It can also cause an
additional instance of Excel to be created so that even after the Quit, there
will be an instance running as a process.

 

[Jan]

Hi Klatuu,
Thank you for your explanation, it helped solve the problem, plus gave me
extra useful tips on automation.

 

[Klatuu]

Glad I could help.
Believe me, I learned the subtleties of Automation the hard way <g>