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
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,059
Office Version
  1. 365
Platform
  1. Windows
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?
 

VHS01

Board Regular
Joined
Nov 3, 2005
Messages
139
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
 
L

Legacy 98055

Guest
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
 

VHS01

Board Regular
Joined
Nov 3, 2005
Messages
139

ADVERTISEMENT

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
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,059
Office Version
  1. 365
Platform
  1. Windows
Gary

Where is the code you posted located?
 

VHS01

Board Regular
Joined
Nov 3, 2005
Messages
139

ADVERTISEMENT

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
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,059
Office Version
  1. 365
Platform
  1. Windows
Gary

Is the code you posted in VB or in Excel VBA?
 

VHS01

Board Regular
Joined
Nov 3, 2005
Messages
139
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
 
L

Legacy 98055

Guest
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,534
Messages
5,572,743
Members
412,482
Latest member
arooshrana2
Top