Error 424 when opening .xlsx from Access 2010 (1 Viewer)

[wilsation]

Hi, when opening an excel spreadsheet from a link on a form the spreadsheet opens in a minimised state. Adding this line of code fixes the problem and opens the spreadsheet with focus.

Set xlApp.UserControl = True

However, when I close the spreadsheet there's an error message behind it - 424 - Object Expected. Here's the full code:

Private Sub txtWorksPriceLink_Click()
 Dim xlApp As Object
Dim xlBook As Object
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("Z:\" & Me.txtProjID & "\WorksPrice.xlsx")
xlApp.Visible = True
Set xlApp.UserControl = True
Set xlApp = Nothing
 End Sub

Any help much appreciated

Thanks

 

[vbaInet]

Closing the Excel workbook manually or through code?

 

[wilsation]

Actually my OP isn't quite right. The spreadsheet opens as expected with the error message behind it - it's nothing to do with closing the form.

 

[vbaInet]

What is the full error message and which line does it highlight?

 

[wilsation]

The full error is 'Runtime error 424, Object required'

This is the highlighted code:

Set xlApp.UserControl = True

Thanks

 

[vbaInet]

It's used without "Set".

 

[wilsation]

I've already tried it without Set, without Set the spreadsheet opens minimised.

 

[winshent]

Does this help you?

http://www.eng-tips.com/viewthread.cfm?qid=19576

 

[vbaInet]

I've already tried it without Set, without Set the spreadsheet opens minimised.

The UserControl property is not an object and therefore it will fail if you use Set. winshent has given you a link that would enable you maximize the spreadsheet.

 

[wilsation]

Thanks for your help guys but still no joy, when I try

[FONT=Courier New]xlApp.WindowState = xlMaximized[/FONT]

Excel opens but with the following error:
Runtime error 1004 - Unable to windowstate property of the Application class.

When I say I want excel to open 'maximised' what I actually mean is I want it to open visible and not minimised in the toolbar.

This code should make it visible but it's not working

xlApp.Visible = True

 

[winshent]

Have you tested with a blank workbook instead of WorksPrice.xlsx?

 

[wilsation]

Yes, same problem.

 

[vbaInet]

Excel opens but with the following error:
Runtime error 1004 - Unable to windowstate property of the Application class.

There's a typo there. What's the exact error message.

 

[wilsation]

Apologies, full error should read:

Unable to Set the Windowstate property of the Application Class

 

[vbaInet]

Let's see the entire code as you have it.

 

[wilsation]

This is the code

Private Sub txtWorksPriceLink_Click()
 Dim xlApp As Object
Dim xlBook As Object
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("Z:\" & Me.txtProjID & "\WorksPrice.xlsx")
xlApp.Visible = True
Set xlApp.UserControl = True
Set xlApp = Nothing
 End Sub

 

[vbaInet]

The full code that includes WindowState.

 

[wilsation]

Here's the code

Private Sub txtWorksPriceLink_Click()
 Dim xlApp As Object
Dim xlBook As Object
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("Z:\" & Me.txtProjID & "\WorksPrice.xlsx")
xlApp.Visible = True
 xlApp.Windowstate = xlMaximized
Set xlApp = Nothing
 End Sub

 

[vbaInet]

------->

The full code that includes WindowState.

I need to see that.

 

[vbaInet]

You've just added that line. Can you re-run the code and paste exactly the code that fails.