VBA-open excel file from word

[george hart]

I need to open an excel file from word ("C:\Documents and Settings\HartG\My Documents\Dave Slater\Import.xls")
and then run a macro from the excel file called "DelTabs".

I have the code below that I tohught would open the excel file but it's not working...Any help on this would be most apreciated.

Dim oExcel As Excel.Application
Dim oWB As Workbook
Set oExcel = New Excel.Application
Set oWB = oExcel.Workbooks.Open("C:\Documents and Settings\HartG\My Documents\Dave Slater\Import.xls")
'Rest of code
End Sub

Many thanks in advance

 

[Macropod]

You could use code like:

Sub Demo()
Application.ScreenUpdating = True
Dim xlApp As Object, xlWkBk As Object, StrWkBkNm As String, StrWkSht As String
Dim bStrt As Boolean, bFound As Boolean
StrWkBkNm = "C:\Users\" & Environ("Username") & "\Documents\Workbook Name.xls"
StrWkSht = "Sheet1"
If Dir(StrWkBkNm) = "" Then
  MsgBox "Cannot find the designated workbook: " & StrWkBkNm, vbExclamation
  Exit Sub
End If
' Test whether Excel is already running.
On Error Resume Next
bStrt = False ' Flag to record if we start Excel, so we can close it later.
Set xlApp = GetObject(, "Excel.Application")
'Start Excel if it isn't running
If xlApp Is Nothing Then
  Set xlApp = CreateObject("Excel.Application")
  If xlApp Is Nothing Then
    MsgBox "Can't start Excel.", vbExclamation
    Exit Sub
  End If
  ' Record that we've started Excel.
  bStrt = True
End If
On Error GoTo 0
'Check if the workbook is open.
bFound = False
With xlApp
  'Hide our Excel session
  If bStrt = True Then .Visible = False
  For Each xlWkBk In .Workbooks
    If xlWkBk.FullName = StrWkBkNm Then ' It's open
      Set xlWkBk = xlWkBk
      bFound = True
      Exit For
    End If
  Next
  ' If not open by the current user.
  If bFound = False Then
    ' Check if another user has it open.
    If IsFileLocked(StrWkBkNm) = True Then
      ' Report and exit if true
      MsgBox "The Excel workbook is in use." & vbCr & "Please try again later.", vbExclamation, "File in use"
      If bStrt = True Then .Quit
      Exit Sub
    End If
    ' The file is available, so open it.
    Set xlWkBk = .Workbooks.Open(FileName:=StrWkBkNm)
    If xlWkBk Is Nothing Then
      MsgBox "Cannot open:" & vbCr & StrWkBkNm, vbExclamation
      If bStrt = True Then .Quit
      Exit Sub
    End If
  End If
  ' Process the workbook.
  With xlWkBk.Worksheets(StrWkSht)
  '''********* Do your worksheet processing here
  End With
  If bFound = False Then xlWkBk.Close False
  If bStrt = True Then .Quit
End With
' Release Excel object memory
Set xlWkBk = Nothing: Set xlApp = Nothing
Application.ScreenUpdating = True
End Sub

Function IsFileLocked(strFileName As String) As Boolean
  On Error Resume Next
  Open strFileName For Binary Access Read Write Lock Read Write As #1
  Close #1
  IsFileLocked = Err.Number
  Err.Clear
End Function

The above code:
• verifies the workbook's existence
• checks whether Excel is running before starting a new instance
• checks whether the workbook is open by the user running the macro or by another user
• checks whether the specified worksheet exists
• generates error messages if there are problems with any of the above.
Of course, if you only want to extract data from the workbook and you're prepared to risk what is extracted no longer being current because someone else is editing it, you can ignore the test for whether someone else has it open and just open it as read-only.

 

[PL dub]

Thank you Paul for this code !

I changed the code as below and tried it.
Replaced StrWkBkNm = "C:\Users\" & Environ("Username") & "\Documents\Workbook Name.xls" by
StrWkBkNm = "G:\BRAD\In-Service Documents\RIL\RIL Index.xlsx"

If the file is already open by an other user, it does work well, the MsgBox is showing up. (It is not the standard "in use window" from windows, but it does what I need.)

However, when the file is free to be use, I run the Macro and the excel file will not show up ?

Am I doing something wrong ?

 

[PL dub]

FYI, I don't know if it does change something, but I'm using Office 2013

Thank you Paul for this code !

I changed the code as below and tried it.
Replaced StrWkBkNm = "C:\Users\" & Environ("Username") & "\Documents\Workbook Name.xls" by
StrWkBkNm = "G:\BRAD\In-Service Documents\RIL\RIL Index.xlsx"

If the file is already open by an other user, it does work well, the MsgBox is showing up. (It is not the standard "in use window" from windows, but it does what I need.)

However, when the file is free to be use, I run the Macro and the excel file will not show up ?

Am I doing something wrong ?

 

[Macropod]

However, when the file is free to be use, I run the Macro and the excel file will not show up ?

Am I doing something wrong ?

If the file is free, it gets opened; otherwise you'd see another error message. However, as comments in the code show, if the Excel session is started by the code, it (and, hence, the workbook) remains hidden; that is why you won't 'see' it. It remains fully accessible for processing, though. If you want a visual indication, comment-out the line:
If bStrt = True Then .Visible = False
Note that, if Excel is already running, no attempt is made to hide the workbook when opening it.

 

[PL dub]

If the file is free, it gets opened; otherwise you'd see another error message. However, as comments in the code show, if the Excel session is started by the code, it (and, hence, the workbook) remains hidden; that is why you won't 'see' it. It remains fully accessible for processing, though. If you want a visual indication, comment-out the line:
If bStrt = True Then .Visible = False
Note that, if Excel is already running, no attempt is made to hide the workbook when opening it.

I comment-out the line :
If bStrt = True Then .Visible = False
and it still doing the same thing. Even with Excel2013 already open, my workbook "RIL Index.xlsx" will not show-up.

 

[Macropod]

Do you actually have any processing going on where the code has:
'''********* Do your worksheet processing here
If not, all that's going to happen is your workbook will be opened, then closed by the line:
If bFound = False Then xlWkBk.Close False
following which, if the macro started Excel, then it too will be closed by the line:
If bStrt = True Then .Quit
Obviously, if you want the workbook to remain open, you'd need to comment-out/delete those lines.

As for whether an already-open workbook will 'show-up', that depends on whether it was active when the macro was run; the code does nothing to activate one, as that is hardly ever required.

 

[PL dub]

Thank you for the explanation.