Excel VBA Gurus Needed

Diego

New Member
Joined
Jun 9, 2003
Messages
16
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.

Code:
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:

Code:
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
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Ivan F Moala

MrExcel MVP
Joined
Feb 10, 2002
Messages
4,209
Here is one way to test for this

Rich (BB code):
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

MrExcel MVP
Joined
May 28, 2002
Messages
11,023
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.
Diego said:
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}
 

Watch MrExcel Video

Forum statistics

Threads
1,113,862
Messages
5,544,726
Members
410,630
Latest member
JFORTH97
Top