Run code in Excel from Access

  • Thread starter Thread starter Matilda
  • Start date Start date
M

Matilda

Hi All,

I found help in this forum in doing this, but need help to understand why it
won't work in my case!

I want my Excel output from Access to have some actions performed on it
which is in code in Personal.xls. I want to call this code from Access, and
have modified code from here to look like this:

Dim xlApp As Variant
Dim xlBookSourceCode, xlBookTarget As Variant

Set xlApp = CreateObject("Excel.Application")
Set xlBookSourceCode = xlApp.Workbooks.Open("C:\Documents and
Settings\[usrname]\Application Data\Microsoft\Excel\XLSTART\PERSONAL.XLS")
Set xlBookTarget = xlApp.Workbooks.Open("C:\Monthly
reports\TestReport\First.xls")
xlApp.Run "sub ChangeFormats()"
xlApp.Application.Visible = True
xlBookSourceCode.Close
xlBookTarget.Close '(False)
xlApp.quit
Set xlApp = Nothing
Set xlBook = Nothing

The target file (and I think Personal.xls) open but the code does not
execute.

I have OLE Automation
COM + Services Type Library
MS Excel 11.0 Object Lib
in my Access references, and

MS Access 11.0 Object Lib in Excel refs.

Also, I notice that when I open Excel from the desktop, the files are still
open and both target and Personal.xls prompt a Read Only message.

I think I have tried everything ...

Any help appreciated,


Matilda
 
Hi Alex,

I love your work on these pages , thanks for your response.

I did move that statement as you suggested, but all I saw is the open file
in xl- no evidence of the code having fired.

BTW, in my Access VBA editor, the code does not step through the way it does
in Excel - it just ran through. I put a break in, and now it simply won't run
at all!

I'm more accustomed to working in Excel, and am not used to the
idiosyncracies of the Access environment (as you can probably tell!)



Alex Dybenko said:
hi,
try to put xlApp.Application.Visible = True after createobject then step
through your code to see what it actually doing (you will see in excel)

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

Matilda said:
Hi All,

I found help in this forum in doing this, but need help to understand why
it
won't work in my case!

I want my Excel output from Access to have some actions performed on it
which is in code in Personal.xls. I want to call this code from Access,
and
have modified code from here to look like this:

Dim xlApp As Variant
Dim xlBookSourceCode, xlBookTarget As Variant

Set xlApp = CreateObject("Excel.Application")
Set xlBookSourceCode = xlApp.Workbooks.Open("C:\Documents and
Settings\[usrname]\Application Data\Microsoft\Excel\XLSTART\PERSONAL.XLS")
Set xlBookTarget = xlApp.Workbooks.Open("C:\Monthly
reports\TestReport\First.xls")
xlApp.Run "sub ChangeFormats()"
xlApp.Application.Visible = True
xlBookSourceCode.Close
xlBookTarget.Close '(False)
xlApp.quit
Set xlApp = Nothing
Set xlBook = Nothing

The target file (and I think Personal.xls) open but the code does not
execute.

I have OLE Automation
COM + Services Type Library
MS Excel 11.0 Object Lib
in my Access references, and

MS Access 11.0 Object Lib in Excel refs.

Also, I notice that when I open Excel from the desktop, the files are
still
open and both target and Personal.xls prompt a Read Only message.

I think I have tried everything ...

Any help appreciated,


Matilda
Click to expand...
Click to expand...
 
Hi,
first check that project is compiled, then try to decompile it and compile
again. then try to restart PC to make sure that no excel instances are
running and hanging. Hopefully it will work then

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


Matilda said:
Hi Alex,

I love your work on these pages , thanks for your response.

I did move that statement as you suggested, but all I saw is the open file
in xl- no evidence of the code having fired.

BTW, in my Access VBA editor, the code does not step through the way it
does
in Excel - it just ran through. I put a break in, and now it simply won't
run
at all!

I'm more accustomed to working in Excel, and am not used to the
idiosyncracies of the Access environment (as you can probably tell!)



Alex Dybenko said:
hi,
try to put xlApp.Application.Visible = True after createobject then step
through your code to see what it actually doing (you will see in excel)

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

Matilda said:
Hi All,

I found help in this forum in doing this, but need help to understand
why
it
won't work in my case!

I want my Excel output from Access to have some actions performed on it
which is in code in Personal.xls. I want to call this code from Access,
and
have modified code from here to look like this:

Dim xlApp As Variant
Dim xlBookSourceCode, xlBookTarget As Variant

Set xlApp = CreateObject("Excel.Application")
Set xlBookSourceCode = xlApp.Workbooks.Open("C:\Documents and
Settings\[usrname]\Application
Data\Microsoft\Excel\XLSTART\PERSONAL.XLS")
Set xlBookTarget = xlApp.Workbooks.Open("C:\Monthly
reports\TestReport\First.xls")
xlApp.Run "sub ChangeFormats()"
xlApp.Application.Visible = True
xlBookSourceCode.Close
xlBookTarget.Close '(False)
xlApp.quit
Set xlApp = Nothing
Set xlBook = Nothing

The target file (and I think Personal.xls) open but the code does not
execute.

I have OLE Automation
COM + Services Type Library
MS Excel 11.0 Object Lib
in my Access references, and

MS Access 11.0 Object Lib in Excel refs.

Also, I notice that when I open Excel from the desktop, the files are
still
open and both target and Personal.xls prompt a Read Only message.

I think I have tried everything ...

Any help appreciated,


Matilda
Click to expand...
Click to expand...
Click to expand...
 
Y'know you MVPs are patient people. I think this is the second time you have
pointed out a simple process error - rather than a syntax error - to me!
I was hearing you say that the syntax looked ok, so it mustn't be compiling.
There was a lot of other code in the project I inserted this module into, and
as it happens, it wasn't compiling at all. So on restart I opened a new db
and copied the module into it to compile and test it - different story
altogether!
As a standalone project I can see the compile and runtime errors perfectly
well, which was not happening before.
Many thanks, I think I should be able to get on now!

Regards,

Matilda

Alex Dybenko said:
Hi,
first check that project is compiled, then try to decompile it and compile
again. then try to restart PC to make sure that no excel instances are
running and hanging. Hopefully it will work then

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


Matilda said:
Hi Alex,

I love your work on these pages , thanks for your response.

I did move that statement as you suggested, but all I saw is the open file
in xl- no evidence of the code having fired.

BTW, in my Access VBA editor, the code does not step through the way it
does
in Excel - it just ran through. I put a break in, and now it simply won't
run
at all!

I'm more accustomed to working in Excel, and am not used to the
idiosyncracies of the Access environment (as you can probably tell!)



Alex Dybenko said:
hi,
try to put xlApp.Application.Visible = True after createobject then step
through your code to see what it actually doing (you will see in excel)

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

Hi All,

I found help in this forum in doing this, but need help to understand
why
it
won't work in my case!

I want my Excel output from Access to have some actions performed on it
which is in code in Personal.xls. I want to call this code from Access,
and
have modified code from here to look like this:

Dim xlApp As Variant
Dim xlBookSourceCode, xlBookTarget As Variant

Set xlApp = CreateObject("Excel.Application")
Set xlBookSourceCode = xlApp.Workbooks.Open("C:\Documents and
Settings\[usrname]\Application
Data\Microsoft\Excel\XLSTART\PERSONAL.XLS")
Set xlBookTarget = xlApp.Workbooks.Open("C:\Monthly
reports\TestReport\First.xls")
xlApp.Run "sub ChangeFormats()"
xlApp.Application.Visible = True
xlBookSourceCode.Close
xlBookTarget.Close '(False)
xlApp.quit
Set xlApp = Nothing
Set xlBook = Nothing

The target file (and I think Personal.xls) open but the code does not
execute.

I have OLE Automation
COM + Services Type Library
MS Excel 11.0 Object Lib
in my Access references, and

MS Access 11.0 Object Lib in Excel refs.

Also, I notice that when I open Excel from the desktop, the files are
still
open and both target and Personal.xls prompt a Read Only message.

I think I have tried everything ...

Any help appreciated,


Matilda
Click to expand...
Click to expand...
Click to expand...
 
Back
Top