Glad I could help.
Believe me, I learned the subtleties of Automation the hard way <g>
--
Dave Hargis, Microsoft Access MVP
"Jan" wrote:
> Hi Klatuu,
> Thank you for your explanation, it helped solve the problem, plus gave
me
> extra useful tips on automation.
> --
> Jan
>
>
> "Klatuu" wrote:
>
> > 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 im****tant 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.
> > --
> > Dave Hargis, Microsoft Access MVP
> >
> >
> > "Jan" wrote:
> >
> > > 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
> > >
> > > --
> > > Jan
|
