open & close EXCEL with VB6

This should give you a start, to open your excel:

Dim AppExcel As Object

Set AppExcel = CreateObject("Excel.application")

AppExcel.Workbooks.Open "D:\K and D (152598).xls"
AppExcel.Visible = True

Benjamin,
Thanks for trying to help! However I am still having no luck. I tried what you suggested and now it won't even open excel the first time. I don't understand why the code that I originally used would work perfectly the first time but as soon as I close the Excel window and try again it don't work?! I'm not going to give up! The answer is out there and I'll find it or die trying.
Thanks again, Garner

Benjamin,
This is another thing that I don't understand. I commented out all of my error handeling in hopes that it would lead me to the problem. Thing is, it don't generate any errors! With my original code the program opens Excel, finds the product, and highlights the entire row. I close the Excel window, click on a number, and excel opens again but this time only a gray background or a blank frame. I can close this window and my picture re-appears with no errors, no freeze up, nothing.
When I tried what you suggested, nothing happened. I clicked the picture and the picture was still there, no errors, no freeze up, nothing. It don't make any sense! Another guy sent me some really fancy code with a module to detect if Excel was running, error handlers, the works. He commented a spot in his code where I was supposed to paste my "find next" code. I done what he said and it worked just like my code. Opened once perfectly, then nothing.
I'm still thinking, reading, and pulling my hair out. This should be so easy but it's becomming a real challenge.
Thanks again, Garner

Hi,

the only thing I can imagine is that your instant of excel isn't opened correctly. So I've checked your first code and this solution should work much better:

'Dim AppExcel As Excel.Application

Private Sub lbl034_Click()
Set AppExcel = CreateObject("Excel.Application")
AppExcel.Workbooks.Open FileName:="g:\tmp\book11.xls"

Cells.Find(What:="34034b", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

'Highlights the entire row.
If IsEmpty(ActiveCell) Then Exit Sub

On Error Resume Next
If IsEmpty(ActiveCell.Offset(0, -1)) Then Set LeftCell = ActiveCell Else Set LeftCell = ActiveCell.End(xlToLeft)
If IsEmpty(ActiveCell.Offset(0, 1)) Then Set RightCell = ActiveCell Else Set RightCell = ActiveCell.End(xlToRight)
Range(LeftCell, RightCell).Select

AppExcel.Visible = True
End Sub

Private Sub Form_Load()
'Set AppExcel = CreateObject("Excel.Application")
'Workbooks.Open FileName:="g:\tmp\book11.xls"
End Sub

First of all dim appExcel above should be deleted is not the method we use.
The code in the form_load has to be deleted also.
To open a workbook don't use only workbooks.open, but app.excel.workbooks.open

Check this code and let me know the outcome.

I see your point with wanting it to be easy to use.. if they update the picing every few months, you could make a copy of database, let them continue with what they have and then cut over to a dtabase style. how much time are we talking from the point they click the image till excel is opened, what if they click another image while excel is still open?

as for the data, you could write a import routine to import the data from the CD to the database.

Hi all,
So far, nothing has resolved my problem. I have some new code that you might want to look at.

This is a module to detect Excel.

' Declare necessary API routines:
Declare Function FindWindow Lib "user32" Alias _
"FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As Long) As Long

Declare Function SendMessage Lib "user32" Alias _
"SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, _
ByVal wParam As Long, _
ByVal lParam As Long) As Long

Public Sub DetectExcel()
' Procedure dectects a running Excel and registers it.
Const WM_USER = 1024
Dim hWnd As Long
' If Excel is running this API call returns its handle.
hWnd = FindWindow("XLMAIN", 0)
If hWnd = 0 Then ' 0 means Excel not running.
Exit Sub
Else
' Excel is running so use the SendMessage API
' function to enter it in the Running Object Table.
SendMessage hWnd, WM_USER + 18, 0, 0
End If
End Sub

This is the main code.

Private Sub Form_Activate()
p4l80e.SetFocus
End Sub

Private Sub lbl034_Click()
'On Error GoTo erh

Dim MyXL As Object ' Variable to hold reference to Microsoft Excel.
Dim ExcelWasNotRunning As Boolean ' Flag for final release.

' Test to see if there is a copy of Microsoft Excel already running.
On Error Resume Next ' Defer error trapping.
' Getobject function called without the first argument returns a
' reference to an instance of the application. If the application isn't
' running, an error occurs.
Set MyXL = GetObject(, "Excel.Application")
If Err.Number <> 0 Then ExcelWasNotRunning = True
Err.Clear ' Clear Err object in case error occurred.

' Check for Microsoft Excel. If Microsoft Excel is running,
' enter it into the Running Object table.
DetectExcel

Set MyXL = GetObject(App.Path & "\K and D (152598).xls")

' Show Microsoft Excel through its Application property. Then
' show the actual window containing the file using the Windows
' collection of the MyXL object reference.
MyXL.Application.Visible = True
MyXL.Parent.Windows(1).Visible = True


Workbooks.Open FileName:="D:\K and D (152598).xls"
Cells.Find(What:="34034b", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
'Highlights the entire row.
If IsEmpty(ActiveCell) Then Exit Sub
On Error Resume Next
If IsEmpty(ActiveCell.Offset(0, -1)) Then Set LeftCell = ActiveCell Else Set LeftCell = ActiveCell.End(xlToLeft)
If IsEmpty(ActiveCell.Offset(0, 1)) Then Set RightCell = ActiveCell Else Set RightCell = ActiveCell.End(xlToRight)
Range(LeftCell, RightCell).Select

Exit Sub
erh: MsgBox Error(Err)
End Sub

This code works perfectly the first time I click a label. When I close the Excel window and click the label a second time, the Excel window just flashes on the screen for a half second and I'm back at the start screen. No matter how many times you click the label from this point Excel only flashes on the screen, then off, like the close button has been clicked. I even tried placing a message box at different points in the main code. That didn't work either.
This code produces no errors, no freeze ups, and no crashes. It just won't keep the Excel window open after the first click. How is that possible when I click the same label that is using the same code that just worked? :confused:

Thanks again to everyone who has offered help! Garner

This code works perfectly the first time I click a label. When I close the Excel window and click the label a second time, the Excel window just flashes on the screen for a half second and I'm back at the start screen.

It looks to me like your Excel object is going out of scope after the Click event terminates. This could explain the behavior you are seeing.

If you want Excel to remain open and visible I would create your Excel object outside of the Click event. Otherwise you may end up with several instances of Excel running that are not visible (because Excel isn't being terminated properly). You can verify whether this is happening by looking for Excel processes in the Task Manager.

Once again, definining a variable in a Sub, Function, Event, etc will localize it to that routine. Once that routine terminates the variable is out of scope and destroyed. If you don't properly terminate the application to which that variable points to before it is destroyed there is a good chance that the application will remain in memory. This could cause problems upon re-use or creation of a new instance of the application.

I would either terminate Excel (Quit method) before the routine finishes executing, or declare a global or module level variable for your Excel object and then terminate it when your application terminates.

Sorry everyone,
I spoke to soon. I went back to testing the program and it's right back to the same thing as before. I guess I'll start reading on how to declare a global or module level variable for your Excel object and then terminate it when your application terminates.
Back to work, Garner