open & close EXCEL with VB6


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Page 1 of 2 12 LastLast
Results 1 to 15 of 19

Thread: open & close EXCEL with VB6

  1. #1
    Join Date
    Apr 2006
    Posts
    28

    open & close EXCEL with VB6

    Hi all,
    I have a picture with part numbers on it. I placed invisible "labels" over each part number. The company that we get the parts from sends us a disk with prices in Excel format. I want to be able to click on the picture and have it open the Excel file, highlight the part and price, and be able to close the Excel window so my picture is visible again. From here I want to be able to click on another part and repeat the process as many times as needed to look up all parts used.
    Nomatter what I try I can only open Excel once, sometimes twice, and then the program refuses to cooperate. Here is some of the code that I have tried:
    Dim AppExcel As Excel.Application

    Private Sub Form_Load()
    Set AppExcel = CreateObject("Excel.Application")
    ChDir "D:\"
    Workbooks.Open FileName:="D:\K and D (152598).xls"
    End Sub

    Each Lable Has It's Own Click Event

    Private Sub lbl034_Click()
    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

    AppExcel.Visible = True
    End Sub

    This code will open Excel and highlight the row once. When I close the Excel window and try clicking on another part an Excel window opens but it is blank even if I click the same part that just opened up perfectly the first time. I have been fighting with this for months. Any suggestions?
    Thanks in advance, Garner

  2. #2
    Join Date
    Feb 2006
    Posts
    133
    First remark, you use a mix of methods. I doubt if you get any result...
    I will reform your code a little bit, because this isn't working. I think you have recorded a macro in excel and then paste it in your VB6.0, this isn't good.
    Benjamin

  3. #3
    Join Date
    Feb 2006
    Posts
    133
    This should give you a start, to open your excel:

    Code:
    Dim AppExcel        As Object
        
        Set AppExcel = CreateObject("Excel.application")
        
        AppExcel.Workbooks.Open "D:\K and D (152598).xls"
        AppExcel.Visible = True
    Benjamin

  4. #4
    Join Date
    Apr 2006
    Posts
    28
    Benjamin,
    You are correct both times. I have a terrible mix of code from commenting out, re-writing, copy and paste, and anything else I could think of to try to make this work. I also did record a "find next" macro and pasted it into my code.
    I'm not a very good programmer. What I know I learned from books that I bought and from helpful people like yourself. I will try your suggestion as soon as I can and post the results.
    Thanks for your time! Garner

  5. #5
    Join Date
    Apr 2006
    Posts
    28

    Unhappy

    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

  6. #6
    Join Date
    Feb 2006
    Posts
    133
    Garner,

    do you get any error? The code works fine by me, if it isn't working for you, you should at least get an error message.
    Benjamin

  7. #7
    Join Date
    Apr 2006
    Posts
    28
    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

  8. #8
    Join Date
    Apr 2006
    Posts
    70
    Have you thought about importing the excel sheet into access table create a field in access and place the values of each "Part" into that column then when the label is clicked, display a window with the part information

    i added 2 events
    mouse over while you move over each image the info in form 2 changes.
    Click event click the label results inthe window opening withthe info
    comment out the mouseover event and use ther Click event

    Run the exe to see or view the souce files then run the exe
    see attached and enjoy !
    Attached Files Attached Files

  9. #9
    Join Date
    Feb 2006
    Posts
    133
    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:

    Code:
    '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.
    Benjamin

  10. #10
    Join Date
    Apr 2006
    Posts
    28
    Jim,
    That is one fantastic program! I think that you are lightyears ahead of anything that I can do. I also don't know how it would work for what I am trying to accomplish. I am trying to make things as simple as possible for people who know even less about computers than myself. You see, our supplier sends a new price list every couple of months. I was hoping that anyone in the shop could just throw the disk in drive D: and come up with a current price. The disks are already in .xls format. I don't even know if I could convert things over properly to make your program work? That may take me a couple more years of learning. And then, even if I could do it, the rest of the shop would be screwed if anything ever happened to me.

    Nomatter what I use at the shop I can see that I will have a ton of fun learning new things from all of that great code you provided!!!

    Benjamin,
    I copied and pasted your code into my project and deleted all of mine. First run, I found that I had to change :="g:\tmp\book11.xls" to my destination of "D:\K and D (152598).xls". Second run, 1st click, Excel opened and the proper line was highlighted. Closed Excel, picture was visible, so I clicked the same part over again. This time there was an error.

    Error #1004 Method 'cell's of Object' Global' Failed

    This part of the code was highlighted:

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

    I am going to look up what this means after I submit this reply. At least now there is an error message that could lead to a solution to the problem. That is more than I had to go on before!

    Thanks a million to both of you!!!! Garner

  11. #11
    Join Date
    Apr 2006
    Posts
    70
    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.

  12. #12
    Join Date
    Apr 2006
    Posts
    28
    Hi again Jim,
    (J) 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.
    (G) That was kind of my plan. Get the project I started to work, let them use it, and that would give me time to study your design.

    (J) how much time are we talking from the point they click the image till excel is opened,
    (G) When it works, it takes less than a second for the highlighted information to show up in the Excel window.
    (J) what if they click another image while excel is still open?
    I think I have that problem covered. I set the Excel window to open maximized. This covers up my picture preventing them from clicking another part until they close the Excel window.
    (J)as for the data, you could write a import routine to import the data from the CD to the database.
    (G) Yes, I would like to do this. Just keep in mind that I write programs because I like learning. I really donít know how to do that yet so that would be a new learning adventure. I also have to do this learning in very limited time. I have been working ten hour days, rush home to grab a quick meal and take the girls to basketball, softball, dance, or whatever is on the agenda for the evening, and still have time with my wonderful wife. This leaves me very little time for programming or even replying to all of the great people, like you, that Iíve met through these forums. Someday life might slow down enough that I can learn how to write a cool program like the one that you sent to me.
    Thanks again, Garner

  13. #13
    Join Date
    Apr 2006
    Posts
    28
    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.

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


    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???

    Thanks again to everyone who has offered help! Garner

  14. #14
    Join Date
    Apr 2006
    Posts
    70
    set a break point on thr click event of the label and run your app. ater the first run through, click the same label again and seee what happens.

    As a side note, have you thought of using acess like the example I gave you?
    Converting your existing app to use access will be easy...
    make a reference to ADO
    use the sub main i sent you.
    open access and import your data into a new table using the wizard. (yup it will create the table for you)
    point the above submain to point to the new database & table.
    Change the event for the labels to search the database and display the info

    When suppliers send you a updated price sheet, rename the existing table & simply import the data again

    No code changes are needed since the table name will always be the same.
    Last edited by jim12345; 05-08-2006 at 05:53 AM.

  15. #15
    Join Date
    Dec 2003
    Posts
    2,750
    Quote Originally Posted by 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.
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

Similar Threads

  1. problem in opening XML with EXCEL via VB6
    By eranfox in forum VB Classic
    Replies: 5
    Last Post: 08-09-2007, 07:18 AM
  2. Replies: 0
    Last Post: 10-10-2002, 05:36 AM
  3. Replies: 2
    Last Post: 10-09-2002, 09:58 AM
  4. open connection to excel from a webpage
    By fareeda in forum ASP.NET
    Replies: 1
    Last Post: 06-26-2002, 02:59 PM
  5. Open and close application
    By Zoe Thompson in forum VB Classic
    Replies: 0
    Last Post: 11-23-2001, 02:50 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center