Linking a button to a excel file

[HVACMAN24]

I made a button open an excel file, but my problem is when it opens the excel program it opens maximized but the spreadsheet file opens as a smaller window in excel. Is there a way I can tell it to open to the max size? When the file gets open by double clicking it it opens maximized like I want so it appears to only be when opening it through my database that it doesnt. I've linked it by using the code below:

dim ctl as CommandButton
set ctl = Me!Button1
with ctl
.hyperlinkaddress = "C:\Desktop\ExcelFile.xlsx"
.hyperlink.follow
end with

 

[boblarson]

Put this function into a STANDARD MODULE and then you can simply pass the file path and name to it and it will open it and ensure the application window and the worksheet are maximized:

Function OpenExcelFile(FileName As String)
    Dim objExcel As Object
 
On Error GoTo Errors
 
    Set objExcel = CreateObject("Excel.Application")
    objExcel.Workbooks.Open (FileName)
    
    With objExcel
        .WindowState = xlMaximized
        .ActiveWindow.WindowState = xlMaximized
        .UserControl = True
    End With

    objExcel.Visible = True

    Set objExcel = Nothing

ExitHere:
     Exit Function
Errors:
           
            MsgBox "Error " & Err.Number & " - " & " (" & Err.Description & ") in procedure OpenExcelFile of Module Module19", , CurrentDb.Properties("AppTitle")
            Resume ExitHere
            Resume
End Function

Then you would call it like:

OpenExcelFile "C:\MyFolder\MyFile.xlsx"

and that is it.

 

[HVACMAN24]

I tried your code as described above but it doesnt open and gives the error "Property not found". When I hit debug it highlights the msgbox line of code in the function, I'm not sure what property it's referring too, but there must be something else not working for it to go to that line of code anyways right? I double checked the file path and name and I have them correct. Any idea's?

 

[HVACMAN24]

Nevermind I went back through and tested it step by step and it was breaking on the .WindowState = xlMaximized saying xlMaximized variable wasnt defined. So I just took those 2 lines out and it worked like I wanted.

Thanks

 

[JANR]

I'm not sure what property it's referring too, but there must be something else not working for it to go to that line of code anyways right?

Probably an oversight by Bob, his code uses Latebinding so you have to supply Excel specific command constants.

so:

Function OpenExcelFile(FileName As String)
    Dim objExcel As Object
    [COLOR=red]Const xlMaximized = -4137
[/COLOR]On Error GoTo Errors
 
    Set objExcel = CreateObject("Excel.Application")
    objExcel.Workbooks.Open (FileName)
    
    With objExcel
        .WindowState = xlMaximized
        .ActiveWindow.WindowState = xlMaximized
        .UserControl = True
    End With
    objExcel.Visible = True
    Set objExcel = Nothing
ExitHere:
     Exit Function
Errors:
           
            MsgBox "Error " & Err.Number & " - " & " (" & Err.Description & ") in procedure OpenExcelFile of Module Module19", , CurrentDb.Properties("AppTitle")
            Resume ExitHere
            Resume
End Function

And now it works.

JR

 

[boblarson]

Probably an oversight by Bob,

Yep, exactly. I meant to do that but forgot when I was in the middle of putting it up.

 

[HVACMAN24]

Yea, that worked. Thanks a lot for your help