execute code in personal.xls from Access

[Matilda]

Hi,

This is round 2 with this problem. Having read all the near-to problems and
solutions in this forum, (and having sorted out the development environment
issues), I think I need to re-state the problem because I just CANNOT get it
to work. When it comes to the crunch line I get a " Macro [name] cannot be
found" error.

Here's what I'm trying to do:

In Access
1. Output recordset to excel temporary file
2. Execute some code from global store (personal.xls)
(code creates and saves new file)
3. kill the temporary file
4. close excel

Here's my code:

( temporary file with recordset is generated and sitting there open at this
point)

Sub newcode()

Dim xlApp As Variant

Set xlApp = Excel.Application

xlApp.Workbooks.Open "C:\[pathname]\personal.xls"
xlApp.Run ("myBrilliantCode") ' here is where I get the error msg

' rest of code here

End Sub

Clearly I cannot execute code from personal.xls like this, but

I do not want to use a template for the recordset, and store the code in
that, because I will not have control over the sheetnames when a new sheet is
added, and that will break my code .


Is it NOT possible to use personal.xls in this way?

Many thanks in advance,

Matilda

 

[Alex Dybenko]

Hi Matilda,
first - try to remove brackets:
xlApp.Run "myBrilliantCode"

then try to specify a sheet with you code explicitly (if code is in a
separate sheet)

xlApp.Run "personal.xls!myBrilliantCode"

if no - then you can just more the code to Access

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Matilda]

Hi Alex,

No, removing the brackets had no effect.

Since Excel is open when the data is exported there by Access, then
Personal.xls is already open. It just wants to hide from Access, that's all!
It simply refuses to reveal itself, or its functions, to Access. I tried
opeing it in Read Only mode - no diff.

I think this is one of those things that it's just not possible to do in the
MS Office environment. It does seem stupid though.

The other option is out also, as I do not have the syntax knowledge (or
reference texts to hand) that would allow me to convert my VBA from the Excel
application to Access.

Am going to sleep on it and see if I dream up a workable solution. I will
not give in.

Many thanks for your help,


Matilda

Hi Matilda,
first - try to remove brackets:
xlApp.Run "myBrilliantCode"

then try to specify a sheet with you code explicitly (if code is in a
separate sheet)

xlApp.Run "personal.xls!myBrilliantCode"

if no - then you can just more the code to Access

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Hi,

This is round 2 with this problem. Having read all the near-to problems
and
solutions in this forum, (and having sorted out the development
environment
issues), I think I need to re-state the problem because I just CANNOT get
it
to work. When it comes to the crunch line I get a " Macro [name] cannot be
found" error.

Here's what I'm trying to do:

In Access
1. Output recordset to excel temporary file
2. Execute some code from global store (personal.xls)
(code creates and saves new file)
3. kill the temporary file
4. close excel

Here's my code:

( temporary file with recordset is generated and sitting there open at
this
point)

Sub newcode()

Dim xlApp As Variant

Set xlApp = Excel.Application

xlApp.Workbooks.Open "C:\[pathname]\personal.xls"
xlApp.Run ("myBrilliantCode") ' here is where I get the error msg

' rest of code here

End Sub

Clearly I cannot execute code from personal.xls like this, but

I do not want to use a template for the recordset, and store the code in
that, because I will not have control over the sheetnames when a new sheet
is
added, and that will break my code .


Is it NOT possible to use personal.xls in this way?

Many thanks in advance,

Matilda

 

[Alex Dybenko]

Hi Matilda,
try to ask at excel group about Run method, perhaps there is some trick
about it

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Hi Alex,

No, removing the brackets had no effect.

Since Excel is open when the data is exported there by Access, then
Personal.xls is already open. It just wants to hide from Access, that's
all!
It simply refuses to reveal itself, or its functions, to Access. I tried
opeing it in Read Only mode - no diff.

I think this is one of those things that it's just not possible to do in
the
MS Office environment. It does seem stupid though.

The other option is out also, as I do not have the syntax knowledge (or
reference texts to hand) that would allow me to convert my VBA from the
Excel
application to Access.

Am going to sleep on it and see if I dream up a workable solution. I will
not give in.

Many thanks for your help,


Matilda

Hi Matilda,
first - try to remove brackets:
xlApp.Run "myBrilliantCode"

then try to specify a sheet with you code explicitly (if code is in a
separate sheet)

xlApp.Run "personal.xls!myBrilliantCode"

if no - then you can just more the code to Access

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Hi,

This is round 2 with this problem. Having read all the near-to problems
and
solutions in this forum, (and having sorted out the development
environment
issues), I think I need to re-state the problem because I just CANNOT
get
it
to work. When it comes to the crunch line I get a " Macro [name] cannot
be
found" error.

Here's what I'm trying to do:

In Access
1. Output recordset to excel temporary file
2. Execute some code from global store (personal.xls)
(code creates and saves new file)
3. kill the temporary file
4. close excel

Here's my code:

( temporary file with recordset is generated and sitting there open at
this
point)

Sub newcode()

Dim xlApp As Variant

Set xlApp = Excel.Application

xlApp.Workbooks.Open "C:\[pathname]\personal.xls"
xlApp.Run ("myBrilliantCode") ' here is where I get the error
msg

' rest of code here

End Sub

Clearly I cannot execute code from personal.xls like this, but

I do not want to use a template for the recordset, and store the code
in
that, because I will not have control over the sheetnames when a new
sheet
is
added, and that will break my code .


Is it NOT possible to use personal.xls in this way?

Many thanks in advance,

Matilda