Opening Excel from Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

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
 
There is no way programmatically to "suppress" the macro warning message.
That is part of the security feature of EXCEL. You'd have to manually reset
the security setting in EXCEL itself before the program runs.

There is an argument in the code for opening a workbook for providing the
password for "reading" a workbook. There is no such argument for a worksheet
itself, though it's possible via code to unprotect (using the password) a
worksheet. But in your case, the default situation appears to be what you
want.

Here is some sample code to get you started (opens a workbook in read-only
mode):

Dim intColumn As Integer
Dim xlx As Object, xlw As Object, xls As Object
On Error Resume Next
Set xlx = GetObject( , "Excel.Application")
If Err.Number <> 0 Then Set xlx = CreateObject("Excel.Application")
' Comment out the next line if you don't want EXCEL to be "seen" by user
xlx.Visible = True
Set xlw = xlx.workbooks.Open("C:\Filename.xls"), , True
Set xls = xlw.Worksheets("WorksheetName")
'
' do other things here
'
Set xls = Nothing
xlw.Save
xlw.Close False
Set xlw = Nothing
xlx.Quit
Set xlx = Nothing
 
thanks for the reply.

Have implemented this and it surpresses the message asking if user wishes to
open a read only, however, I now get an error within Excel:

It states that "The cell or chart you are trying to change is protected and
therefore read-only"
 
As I said in my post, the code is opening the workbook in "read only" mode.
Thus, you cannot make any changes to the cells on the worksheets. Isn't that
what you said you wanted?

If you don't want to open the workbook in read-only mode, then change this
line
Set xlw = xlx.workbooks.Open("C:\Filename.xls"), , True

to this
Set xlw = xlx.workbooks.Open("C:\Filename.xls"), , False
 
Yes, that's exactly what I wanted but I think there is some coding in the
Excel workbook which I now need to tweak.

Thanks for your help.
 
Back
Top