Convert Excel code to work in Access

  • Thread starter Thread starter Stephen sjw_ost
  • Start date Start date
S

Stephen sjw_ost

Hello,

I am trying to automate Excel with Access and have been trying to convert
this Excel code to work from Access.

Sub DoIt1()
'
' This macro places a shape ,triangle, on the selected sheet to make a
"Please wait sign".
'
Application.ScreenUpdating = True
With Sheet2.Shapes("AutoShape 6")
.Visible = msoTrue = (Not Sheet2.Shapes("AutoShape 6").Visible)
End With
With Sheet2.Shapes("AutoShape 6")
.Visible = msoFalse = (Not Sheet2.Shapes("AutoShape 6").Visible)
Application.StatusBar = False
End With
Sheet2.Select
End Sub

I have added in my References the "Microsoft Excel 11.0 Object Library" and
am using the following to automate Excel with;

Public xApp As Excel.Application
Public xBook As Excel.Application

Sheet2 = sheets name "Options"

Here is my attempt to convert the Excel code to work from Access with no luck;

Function DoIt()
Set xApp = GetObject(, "Excel.Application")
Set xBook = xApp.ActiveWorkbook
' This macro places a shape ,triangle, on the selected sheet to make a
"Please wait sign".
xBook.Sheets("Options").Activate
With xBook.Sheets("Options").Shapes("AutoShape 6")
.Visible = xApp.Workbook.msoTrue = (Not
xBook.Sheets("Options").Shapes("AutoShape 6").Visible)
End With

With xBook.Sheet2.Shapes("AutoShape 6")
.Visible = xApp.Workbook.msoFalse = (Not
xBook.Sheets("Options").Shapes("AutoShape 6").Visible)

End With
xBook.Sheets("Options").Select
End Function

This code does compile in Access but it will not automate Excel with the
desired result which is to open a hidden triangle object on Sheet2 "Options"
to let the user know something is running. I get the error;

Run-time error '438';
Object doesn't support this property or method

The error occurs in the 1st With/End With on the .Visible

As always, any help is greatly appreciated.
Stephen
 
The answer to this question turns out to be very easy. I stepped thru the
code in Excel first and found that msoTrue = -1 and msoFalse = 0. So in the
Access automation I changed the msoTrue to a -1 and the msoFalse to a 0. The
new code compiled so I ran it and viola! It worked like a charm. Here is my
final code for this operation. I hope it can help someone.

The AutoShape and name can be changed to what ever shape you may want, just
make the autoshape name match in the code.

Public Function DoIt()
Dim xApp As Excel.Application
Dim xBook As Excel.Workbook
Set xApp = GetObject(, "Excel.Application")
Set xBook = xApp.ActiveWorkbook
' This macro places a shape ,triangle, on the selected sheet to make a
"Please wait sign".
xBook.Sheets("Options").Activate
With xBook.Sheets("Options").Shapes("AutoShape 6")
.Visible = -1 = (Not xBook.Sheets("Options").Shapes("AutoShape
6").Visible)
End With

With xBook.Sheets("Options").Shapes("AutoShape 6")
.Visible = 0 = (Not xBook.Sheets("Options").Shapes("AutoShape
6").Visible)

End With
xBook.Sheets("Options").Select
End Function
 
Back
Top