Didn't know whether to post this here or under Access.

Basically, how do you open an Excel worksheet from Access in code?

Also, the Excel worksheet uses Macro's and is read only/protected. Is it
possible for the warnings to be surpressed? i.e. Instead of the user being
asked whether to allow macros, these are automatically allowed. And instead
of accessing to enter password to modify workbook, 'read only' is
automatically chosen.


Thanks

To use Excel in Access:
In your VBA "References" include a reference to the Microsoft Excel Object
Model. Then you can use code as illustrated below:

Dim XLApp as Excel.Application, XLBook as Excel.Workbook, XLSheet as
Excel.Worksheet

Set XLApp = New Excel.Application
Set XLApp.Visible = True ' (only if you need the user to see Excel -
otherwise you can process everything invisibly)
Set XLBook = XLApp.Workbooks.Open(WorkbookFilePath)
Set XLSheet = XLBook.Sheets("Sheet1")
etc...
Basically anything possible in Excel VBA is done in your Access module as
long as you refer it back to XLApp. When done be sure to close everything
and clean out your object variables, or you may leave your Excel session
running in the background:
XLBook.Close
XLApp.Quit
Set XLSheet = Nothing
Set XLBook = Nothing
Set XLApp = Nothing

As for the other parts of your question:
- You can open the book read only: the full syntax of the Open method for
the workbook is:
expression.Open(FileName, UpdateLinks, ReadOnly, Format, Password,
WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable,
Notify, Converter, AddToMRU)
So you can use the parameters (in this case, ReadOnly) to specify how to
open the workbook - see help for more info.
- Running macros: I don't know any way to open Excel from code and bypass
the macros prompt - doing so would defeat the purposes of macro security
anyway. I have seen the same question asked before in this forum, so you
could search and see if you can find a solution.

HTH!

"Steve Price" wrote:

Didn't know whether to post this here or under Access.

Basically, how do you open an Excel worksheet from Access in code?

Also, the Excel worksheet uses Macro's and is read only/protected. Is it
possible for the warnings to be surpressed? i.e. Instead of the user being
asked whether to allow macros, these are automatically allowed. And instead
of accessing to enter password to modify workbook, 'read only' is
automatically chosen.


Thanks

I think you must have tried already, but forgot to set Excel to visible.
Once you set it to visible all remaining code is same as in excel macros.
You can use below code.

Sub olExcelOpen()
Dim exApp As Excel.Application
Dim myBook As Excel.Workbook
Set exApp = CreateObject("Excel.Application")
exApp.Visible = True
Set myBook = exApp.Workbooks.Open(FileName:="Path to the file",
ReadOnly:=True)
myBook.RunAutoMacros xlAutoOpen
End Sub

Sharad

"Steve Price" wrote in message
...
Didn't know whether to post this here or under Access.

Basically, how do you open an Excel worksheet from Access in code?

Also, the Excel worksheet uses Macro's and is read only/protected. Is it
possible for the warnings to be surpressed? i.e. Instead of the user being
asked whether to allow macros, these are automatically allowed. And
instead
of accessing to enter password to modify workbook, 'read only' is
automatically chosen.


Thanks