VB.exe not recognizing Excel

VHS01

Board Regular
Joined
Nov 3, 2005
Messages
139
I have a VB.exe that creates new sheets each month in an existing workbook. The first thing I need to do is check the open workbook to be sure it's the corrct one. However after several unsuccessful attempts, I added a MsgBox in the VB.exe to return the Workbook caption. No surprise when it turned up as nothing. Here is the code, what am I doing wrong???

The actual Workbook caption is "MyWorkbook.xls"

xlApp.ActiveWorkbook.Activate
If ActiveWorkbook.Windows(1).Caption <> "MyWorkbook.xls" Then

Thanks
Gary
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Gary

What do you actually mean by 'VB.exe'?

By the way I don't think there is a caption property of an Excel Workbook/Window, have you tried Name?
 
Upvote 0
Norie
Thanks for the reply.

I think we are on the right track here.

By VB.exe, I mean the code is writtten in VB and compiled into an exe for installation on other computers. The VB project not only creates the workbooks but, acts as the point of update and support. The created workbooks reference back to the VB exe at scheduled times.

I tried "Activeworkbook.name" in place of "ActiveWorkbook.Windows(1).Caption", using a msgbox to check, both return nothing.

"Shell" in Excel is calling the VB exe just fine.

'First I check the workbook calling the VB exe
If ActiveWorkbook.Windows(1).Caption <> "MyWorkbook.xls" Then
'I create a new Workbook, add new sheets all set up for the project, and give the new Workbook a caption using: xlApp.ActiveWorkbook.Windows(1).Caption = "MyWorkbook.xls"
'And save
Else this is the right workbook, just do updates

Right now each call to the VB exe creates another new workbook instead of just doing the updates. So the problem how to identify the open workbook.

Hope this clarifies and you can point me in the right direction.

Thanks again
Gary
 
Upvote 0
I tried "Activeworkbook.name" in place of "ActiveWorkbook.Windows(1).Caption", using a msgbox to check, both return nothing.

Are you using GetObject to get a reference to the running instance of Excel Application? If not, how then?

Please post the applicable VB.exe code.

Tom
 
Upvote 0
Tom:
Thanks for jumping in.
This may be exactly where I am falling down.

Since the excel file is calling the VB exe I do not have anything that directly references the workbook calling the VB file. Honestly, I don't know how, so no code to paste other than what I gave before.

Please give me an idea of how this is done.
Thanks
Gary
 
Upvote 0
Gary

Where is the code you posted located?
 
Upvote 0
Norie
The previous posts contain most of the code, but evidently not enough
I think my whole problem is the referencing, but I'll have to clean up the code before I can paste it. Lots of tries that are rem'd out.
Will get back asap.
Thanks
Gary
 
Upvote 0
Gary

Is the code you posted in VB or in Excel VBA?
 
Upvote 0
Norie:
The code is in VB
However, doing a search in the VB Help on what Tom was asking yielded the answer
I was not correctly referencing the app. Set xlApp = GetObject(, "Excel.Application")

Thanks to all
Gary
 
Upvote 0
VHS,

Assuming that you are using Vb 4,5, or 6. If you are using Net let me know.

Create a new Windows Application project.
Add four commandbutton to Form1

Paste this code into Form1

Rich (BB code):
Option Explicit

Const HWND_TOPMOST = -1
Const HWND_NOTOPMOST = -2
Const SWP_NOSIZE = &H1
Const SWP_NOMOVE = &H2
Const SWP_NOACTIVATE = &H10
Const SWP_SHOWWINDOW = &H40
Private Declare Sub SetWindowPos Lib "User32" (ByVal hWnd As Long, ByVal hWndInsertAfter As Long, ByVal X As Long, ByVal Y As Long, ByVal cx As Long, ByVal cy As Long, ByVal wFlags As Long)

Dim ExcelApp As Excel.Application
Dim Workbook As Excel.Workbook

Private Sub Form_Activate()
    'KPD-Team 1998
    'URL: http://www.allapi.net/
    'E-Mail: KPDTeam@Allapi.net
    'Set the window position to topmost
    SetWindowPos Me.hWnd, HWND_TOPMOST, 0, 0, 0, 0, SWP_NOACTIVATE Or SWP_SHOWWINDOW Or SWP_NOMOVE Or SWP_NOSIZE
End Sub

Private Sub Command1_Click()
    'caption "Add Worksheet"
    Workbook.Sheets.Add
    MsgBox "You just added a worksheet named " & Workbook.ActiveSheet.Name
End Sub

Private Sub Command2_Click()
    'caption "Insert Values into Range"
    Workbook.ActiveSheet.Range("A1").Value = "Wuss Up!"
End Sub

Private Sub Command3_Click()
    'caption "Close Workbook"
    Workbook.Close False 'false argument = Do Not Save Changes
End Sub

Private Sub Command4_Click()
    'caption "Close Excel"
    'close if there are no workbooks open
    If ExcelApp.Workbooks.Count = 0 Then ExcelApp.Quit
End Sub

Private Sub Form_Load()
    Dim WorkbookPath As String
    'Command() will pass the commandline arguments
    WorkbookPath = Command()
    
    Set Workbook = GetObject(WorkbookPath)
    Set ExcelApp = Workbook.Parent
    ExcelApp.Visible = True
End Sub

The code in Form_Load will provide you with a strong reference to the ActiveX Excel application.

Forget about using the workbook name. The calling code(shell) will absolutely be in the correct workbook I would assume. Why else would the code be there. Pass the workbook full name as a command line argument to the VB.exe. This will leave you independant of the workbook name or location.

Example code in your workbook might be...

Rich (BB code):
Public Sub GetMyVbApp()
    Dim VbAppPath As String
    Dim CommandLineArg As String
    
    VbAppPath = "C:\Documents and Settings\Tom\Desktop\Vb.exe Example\VB Exe\SampleVbExcel.exe"
    CommandLineArg = ThisWorkbook.FullName
    
    Shell VbAppPath & " " & CommandLineArg, vbNormalFocus
End Sub

Any problems? Download and extract leaving the folder structure as is.

Tom
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

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
Back
Top