Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Getting from Excel back to VB (VB6/Xl2000)

    I have a VB 6.0 project with Form1. The Form_Load event calls a sub (OpenXl) from Module1, which opens an xl file called Test1.xls and inserts a few formulas in the spreadsheet.

    After allowing for user input into the spreadsheet, I want to code a VBA procedure that will reopen Form1 of the VB project (without closing the xl workbook).

    Can anyone show me how to do this?
    Here's what I have so far- It all works except for the last line:

    The code for Form1:
    =========================

    Private Sub Form_Load()
    Call OpenXL
    End Sub



    The code in MODULE1:
    ===========================

    Option Explicit
    Public xl As Object
    Public wb As Object
    Public sh As Object
    _______________________________________
    Public Sub OpenXL()
    'create object variables
    Set xl = CreateObject("Excel.Application")
    Set wb = xl.Workbooks.Open(CurDir & "Test1.xls")
    Set sh = wb.Worksheets("Sheet1")
    ' Insert some formulas.
    sh.Range("B8").Formula = "=sum(B4:B7)"
    sh.Range("C4:C7").Formula = "=RC[-1]+5"
    'Make Excel visible
    xl.application.Visible = True
    Form1.Visible = False
    End Sub




    'VBA code in excel to reopen Form1:
    =============================
    'THIS DOESN'T SEEM TO DO THE JOB. What do I need here ???????????

    Sub ReOpenForm1()
    Project1.Form1.Visible = True

    End Sub

  2. #2
    New Lounger
    Join Date
    Feb 2003
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting from Excel back to VB (VB6/Xl2000)

    Try declaring your variable this way:

    Dim xlb As New Excel.Application

    'Open Read Only (you don't have to open read only, this is from my code)
    xlb.Workbooks.Open "workbook name goes here", , True

    'Do some stuff here.... like inserting your formula

    set xlb = nothing

    What you get is Excel remaining on it's own and up to the user to shut it down.

    HTH

    Curt

  3. #3
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting from Excel back to VB (VB6/Xl2000)

    Curt,
    Thanks for the quick reply. I'm not sure if you understood my question, though. With my above code I have no problem getting xl to open and allowing the user to input or close it as he/she wishes. The problem is getting back to my VB form. I need a VBA code line that can be run from a cmd button on the xl worksheet that will bring the VB Form1 back on screen.

  4. #4
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting from Excel back to VB (VB6/Xl2000)

    Curt,
    Out of curiosty, I tried your code to see how it differed from mine and the first line gave me an error msg: "User-defined type not defined." Im wondering if you have referenced a library that I haven't checked off. In the Project ==>References I have checked OLE Automation; and in Project ==> Components ==> Insertable Objects I have checked off "Microsoft Excel Worksheets" I think this latter one is the one I am actually using here.

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Getting from Excel back to VB (VB6/Xl2000)

    Not sure if that can be done easily. Your Excel app knows nothing about your VB project. You can restart your VB project with an AppActivate. If that triggers some event in VB like Paint, then you can make the form visible again. Otherwise, it would be easier to just minimize VB. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  6. #6
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting from Excel back to VB (VB6/Xl2000)

    Excel cannot work with a VB 6 Form.

    You'll need to operate on the Form using VB 6 and pass info back and forth to Excel via the Excel object you created.

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Getting from Excel back to VB (VB6/Xl2000)

    Hi Bryan,
    I think you need to choose a different name for your subroutine otherwise you've got a function and a sub with the same name.
    FWIW.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting from Excel back to VB (VB6/Xl2000)

    <img src=/S/doh.gif border=0 alt=doh width=15 height=15> Quite Right. I guess that's what happens when you call all your subs Test <img src=/S/grin.gif border=0 alt=grin width=15 height=15>. I'll go back and edit the post. so there is no confusion.

    Thanks Rory.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  9. #9
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting from Excel back to VB (VB6/Xl2000)

    <P ID="edit" class=small>(Edited by carbonnb on 14-Feb-03 08:06. To rename the sub so it doesn't conflict with the API declaration)</P>You will need to use a couple of API calls to do it.

    First, in the general declarations section of a module, add the following API and Constant declarations:

    Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
    (ByVal lpClassName As String, ByVal lpWindowName As String) As Long

    Private Declare Function ShowWindow Lib "user32.dll" (ByVal hwnd As _
    Long, ByVal nCmdShow As Long) As Long

    'nCmdShow is EXACTLY one of the following flags specifying how to show the window:
    Const SW_HIDE = 0
    'Hide the window.
    Const SW_MAXIMIZE = 3
    'Maximize the window.
    Const SW_MINIMIZE = 6
    'Minimize the window.
    Const SW_RESTORE = 9
    'Restore the window (not maximized nor minimized).
    Const SW_SHOW = 5
    'Show the window.
    Const SW_SHOWMAXIMIZED = 3
    'Show the window maximized.
    Const SW_SHOWMINIMIZED = 2
    'Show the window minimized.
    Const SW_SHOWMINNOACTIVE = 7
    'Show the window minimized but do not activate it.
    Const SW_SHOWNA = 8
    'Show the window in its current state but do not activate it.
    Const SW_SHOWNOACTIVATE = 4
    'Show the window in its most recent size and position but do not activate it.
    Const SW_SHOWNORMAL = 1
    'Show the window and activate it (as usual).

    Now add the following Procedure to the module you just added the declarations to:

    Sub ActivateVBApp()
    'Get Windows Handle of Form
    hwnd = FindWindow(vbNullString, "Form1")
    'Now show the window
    ShowWindow hwnd, SW_SHOWNORMAL
    End Sub

    The first line get's the windows handle for the form, even if it is hidden, but Excel HAS to know the Caption of the form. You can either hard code it into Excel or use your VB form to place it's caption in a cell in your workbook and then get the value from there.

    The second line takes the windows handle and the flag that tells the API call what you want to do. In this case, show the form normally.

    I'm pretty sure there is an API call that you can pass the full EXE name (MyApp.exe) to to get the windows handle of, but I don't know what it is off hand.

    Good luck and let us know how you make out.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  10. #10
    New Lounger
    Join Date
    Feb 2003
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting from Excel back to VB (VB6/Xl2000)

    (sigh) You are correct, I must not have read your post thoroughly enough and missed it (by a mile). :-)

    You might look into using DDE or another poster suggested API's that would probably work as well. I haven't tried to go back to my VB6 app from Excel.

    Curt

  11. #11
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Getting from Excel back to VB (VB6/Xl2000)

    If you don't want to hard-code in caption of VB form, you can use the Windows class name for main window (ie, the form window) of the VB app as first argument for FindWindow API function. Example I used (similar to code you posted):

    Declare Function apiFindWindow Lib "user32" Alias "FindWindowA" _
    (ByVal strClassName As String, ByVal lpWindowName As Any) As Long

    Declare Function apiShowWindow Lib "user32" Alias "ShowWindow" _
    (ByVal hwnd As Long, ByVal nCmdShow As Long) As Long

    ' Show Window (SW) Constants:
    Const SW_HIDE = 0
    Const SW_SHOWNORMAL = 1
    Const SW_SHOWMINIMIZED = 2
    Const SW_SHOWMAXIMIZED = 3
    Const SW_SHOWNOACTIVATE = 4
    Const SW_SHOW = 5
    Const SW_MINIMIZE = 6
    Const SW_SHOWMINNOACTIVE = 7
    Const SW_SHOWNA = 8
    Const SW_RESTORE = 9
    Const SW_SHOWDEFAULT = 10

    Sub ShowVBApp()

    Dim lngHWnd As Long
    Dim intRtn As Integer

    ' Const VB_APP_CLASS As String = "ThunderRT6Main" ' VB6 Standard .EXE
    Const VB_FRM_CLASS As String = "ThunderRT6FormDC" ' VB6 Form
    ' Const XL_CLASS As String = "XLMAIN" ' Excel App Main Window

    lngHWnd = apiFindWindow(VB_FRM_CLASS, vbNullString)
    intRtn = apiShowWindow(lngHWnd, SW_RESTORE) ' Normal (restored) window

    End Sub

    Testing this in Excel, using the VB App Class name did not work; included it & Excel app class name for info only in sub. Using SW_RESTORE seemed to provide most reliable results. To get the (somewhat arbitrary) Windows class name for an application window, given the app window's hWnd, you can use the GetClassName API. Example:

    Declare Function apiGetClassName Lib "user32" Alias "GetClassNameA" _
    (ByVal hwnd As Long, ByVal lpClassName As String, ByVal nMaxCount As Long) As Long

    Public Function GetClassName(hwnd As Long)

    Dim strBuffer As String
    Dim intCount As Integer
    Const MaxLen = 128

    strBuffer = Space(MaxLen)
    intCount = apiGetClassName(hwnd, strBuffer, MaxLen)
    GetClassName = Left$(strBuffer, intCount)

    End Function

    As for the standard VB project class names, I have no idea what "Thunder" signifies; the "RT6" apparently denotes RunTime 6 (ie, VB 6.0).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •