Excel VBA Gurus Needed

[Diego]

As part of a bigger project, I have the following Sub that opens another workbook and passes certain parameters to it.

The Shell open an excel workbook has certain command line parameters (/e/r) that when passed to the file it runs a procedure using an ODBC connection.

Sub OpenDataFile()
Dim RetVal As Variant

' Open Excel File capable of Reading routes Export DB to generate Report
With ThisWorkbook.Worksheets("Menu")
    RetVal = Shell("excel.exe """ & .Range("DBFile").Value & """ " & .Range("CLine").Value & "", 1)
    AppActivate RetVal
End With

End Sub

This runs fine. However, then when I try to check if the workbook is open via:

Function WorkbookOpen(WorkBookName As String) As Boolean
' returns TRUE if the workbook is open
    WorkbookOpen = False
    On Error GoTo WorkBookNotOpen
    If Len(Application.Workbooks(WorkBookName).Name) > 0 Then
        WorkbookOpen = True
        Exit Function
    End If
WorkBookNotOpen:
End Function

the function return FALSE. My it is bacause it is another Excel session and it does not recognise it as the same application.
I need to read some values from that workbook. My question is, how do I interact with that workbook?

This, for example, does not work. (strStuff is the workbook opened using the shell command)
Workbooks(strBuff).Worksheets("Report").Range("A1").value

Any ideas on how to reach the newly opened workbook using this method. I cannot use workbooks.open xxxxx because I cannot pass command line parameters that way.

Any ideas? Thanks

 

[Ivan F Moala]

Here is one way to test for this

Sub TestVBA()
    If IsFileOpen(Range("DBFile").Value) Then
        MsgBox "File is open"
    Else
        MsgBox "File is not open"
    End If
End Sub

Function IsFileOpen(strFullPathFileName As String) As Boolean
'// VBA version to check if File is Open
'// We can use this for ANY FILE not just Excel!
'// Ivan F Moala
'// http://www.xcelfiles.com

Dim hdlFile As Long

    '// Error is generated if you try
    '// opening a File for ReadWrite lock >> MUST BE OPEN!
    On Error GoTo FileIsOpen:
    hdlFile = FreeFile
    Open strFullPathFileName For Random Lock Read Write As hdlFile
    IsFileOpen = False
    Close hdlFile
    Exit Function
FileIsOpen:
    '// Someone has it open!
    IsFileOpen = True
    Close hdlFile
End Function

 

[tusharm]

Don't use Shell. Use ActiveX. msdn.microsoft.com has any number of articles on the subject. Here's one:

Creation of Object Variables to Automate Another Office Application
http://msdn.microsoft.com/library/d...ariableToAutomateAnotherOfficeApplication.asp

And, thanks for formatting the code for readability.

As part of a bigger project, I have the following Sub that opens another workbook and passes certain parameters to it.

The Shell open an excel workbook has certain command line parameters (/e/r) that when passed to the file it runs a procedure using an ODBC connection.
{snip}