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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

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,029
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}
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,820
Messages
5,772,462
Members
425,760
Latest member
paphon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top