Unexplained read-only from a batch file

[Mark Tangard]

Hi folks. I have a custom toolbar button in Word that uses the VBA's
Shell command to run a batch file:

Shell "C:\GX\OpenLog15.bat"

where the batch file reads:

@echo off
EXCEL.EXE C:\GX\Log15.xls
exit

It works fine, UNLESS Excel is already open with no sheet active AND the
last-opened Excel file is the one shown. In that case it displays the
File-In-Use dialog. Stranger still, if I dismiss that dialog by hitting
Cancel, the file comes up normally (not read-only).

Am I going about this wrong? Running XL 2003, WinXP.

Thanks for any clues.



Mark

 

[Dave Peterson]

I think I'd drop the .bat file and use something like:

Option Explicit
Sub testme()

Dim XLApp As Object
Dim xlWkbk As Object
Dim wkbkName As String
Dim XLWasRunning As Boolean
Dim testStr As String
Dim iCtr As Long

wkbkName = "C:\GX\Log15.xls"

testStr = ""
on Error Resume Next
testStr = Dir(wkbkName)
On Error GoTo 0
If testStr = "" Then
MsgBox wkbkName & " wasn't found!"
Exit Sub
End If

XLWasRunning = True
On Error Resume Next
Set XLApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set XLApp = CreateObject("Excel.Application")
XLWasRunning = False
End If

XLApp.Visible = True 'at least for testing!

Set XLWkbk = XLApp.workbooks.Open(FileName:=wkbkName)

'do some stuff
'and close and save???
XLWkbk.Close savechanges:=True

If XLWasRunning Then
'leave it running
Else
XLApp.Quit
End If

Set XLWkbk = Nothing
Set XLApp = Nothing

End Sub