Error 424 when opening .xlsx from Access 2010

[wilsation]

Here it is with WindowState

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]

Is that the exact code that you tested? Can you re-test what you pasted.

The reason I ask is because one of them has UserControl and the other doesn't.

 

[wilsation]

Ok, I run this code:

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

The spreadsheet opens as desired, behind it is the 1004 error - Unable to set windowstate.
When I click Debug it highlights

xlApp.Windowstate = xlMaximized

 

[vbaInet]

Let's try this:

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

 

[wilsation]

Hi, I've tried your code but get this error when it runs:

Compile Error: User -defined type not defined

And stops at this line:

Dim xlBook As Excel.Workbook

I've googled this error which suggested missing references, I've added DAO lib, ActiveX lib and Excel lib but it didn't make any difference.

Many thanks for your continued help.

 

[winshent]

Hi, I've tried your code but get this error when it runs:

Compile Error: User -defined type not defined

And stops at this line:

Dim xlBook As Excel.Workbook

That line is not part of the code posted by vbaInet

 

[wilsation]

Apologies, the error I get when running

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

is Run-time error 1004:
Unable to set the WindowState property of the Application class

 

[winshent]

So if you comment that line out the code runs fine ?

 

[wilsation]

If I comment out

Dim xlBook As Object

I still get the 1004 WindowState error which highlights this line

.WindowState = xlMaximized

 

[winshent]

If you comment out....

.WindowState = xlMaximized

 

[wilsation]

Tried that but then the spreadsheet just opens minimised, which is back where we started....

 

[winshent]

I tested the code with Office 2003 (don't have 2010 here)...

As long as Excel Object Library is added to the list of references, code works fine here.

Suggest maybe an install problem.. try another machine..

 

[wilsation]

Hi Winshent, I agree that it looks like it could be a setup/config issue rather than a coding issue, I'll just have to keep looking.

As an aside, when I add in the Excel lib reference the spreadsheet will only open minimised regardless of code used.

thanks for your help.

 

[winshent]

Try

.Parent.WindowState = xlMaximized

 

[wilsation]

Produces the same 1004 windowstate error and highlights .Parent.WindowState = xlMaximized

Note that the spreadsheet opens maximised with or without the .Parent but this dialog box is behind it.

 

[winshent]

Security related issue ?

Is it a trusted document/ location ?

 

[wilsation]

Unfortunately not, if only it was so simple.

 

[winshent]

Filepath length over 250 characters ?

 

[wilsation]

Nope, I've purposely kept the filepath as short as possible - I hate long filepaths .

 

[vbaInet]

It's nothing to do with code but with your Excel file. If you try it on a new Excel file it should work. If you want, you can upload the exact Excel file that you're trying to open and I'll see what is causing it.