Opening Excel 2010 from Word 2010

[paul_53]

I am trying to open a specific excel worksheet from an activex button on a word document, however it does not seem to want to work, any help would be appreciated, the code I have tried is:-

Private Sub CommandButton161_Click()

    Dim oExcel As Excel.Application
    Dim oWB As Workbook
    Set oExcel = New Excel.Application
    Set oWB = oExcel.Workbooks.Open("\\Dfs50620\204723001\workgroup\I Drive\Noticeboard\Leave_2014.xls")
    oExcel.Visible = True
    Set oWB = oExcel.Workbooks.Open(sPath)
   

End Sub

Thanks in anticipation of someone helping out on what has been a frustrating 2 hours

Paul

 

[Gary McMaster]

Except for the last line which doesn't belong, your code looks OK to me. In fact it runs for me (Office 2000) as written.

Have you referenced the Excel object library? Verified the file path?

Gary

 

[paul_53]

Thanks for coming back to me Gary, I removed the last line as suggested but I am still getting a compile error, User-defined type not defined at Dim oExcel As Excel.Application

 

[Gary McMaster]

That message sounds like you haven't referenced the Excel Object library.

In the VBA IDE Main Menu bar click Tools>References

Find: MicroSoft Excel x.x Object Library and tick the check box.

The x.x will be 12.0 or something like that. Mine is 9.0 but that's for XL2000. Yours will be higher.

Gary

 

[paul_53]

Thank you Gary, that did the trick, very much appreciated

Paul

 

[paul_53]

Just a follow up, I've found a flaw in my plan, if someone already has the worksheet open a code message comes up, how would I get a simple pop up box saying "Someone using file, please try later", presumable its an error handler but I've never used one before

Thanks

 

[Gary McMaster]

Try something like this:

Private Sub CommandButton161_Click()

    Dim oExcel As Excel.Application
    Dim oWB As Workbook
    
    On Error Resume Next
    
    Set oExcel = New Excel.Application
    Set oWB = oExcel.Workbooks.Open("[URL="file://\\Dfs50620\204723001\workgroup\I"]\\Dfs50620\204723001\workgroup\I[/URL] Drive\Noticeboard\Leave_2014.xls")
    
    If Err Then
        MsgBox "Someone using file, please try later"
        'or
        MsgBox Err.Description
        Err.Clear
    Else
        oExcel.Visible = True
    End If
    
    On Error GoTo 0

End Sub

Gary

 

[paul_53]

Hi Gary I tried that but oddly enough its doing something that I didn't expect. Usually because these files are on a shared network it won't let you amend, it just tells you its read only however its letting a number of users open the file without any sort of error messages now, but no message box, all a bit odd

 

[Gary McMaster]

Maybe use the readonly property in your code and inform the user that it is read only.

Here' s the VBA help on it.

Workbook.ReadOnly Property


Returns True if the object has been opened as read-only. Read-only Boolean.Syntax
expression.ReadOnly
expression A variable that represents a Workbook object.

Example
If the active workbook is read-only, this example saves it as Newfile.xls.

Visual Basic for Applications
If ActiveWorkbook.ReadOnly Then ActiveWorkbook.SaveAs fileName:="NEWFILE.XLS"End If

 

[paul_53]

Thanks for coming back to me Gary, I will give that a go on Monday when I am back in the office