Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369

    Unanswered: Opening Excel from Access

    In Access I open specific Word docs as follows and I am wondering if someone can give me the counterpart to do Excel.

    Private Sub Label118_Click()

    Const MSTB_MSWORD = 300&

    Application.Run "utility.util_StartMSToolbarApp", MSTB_MSWORD

    Dim docName As Object
    Set docName = CreateObject("Word.Basic")

    docName.FileOpen "c:\Letters\SoA.doc"

  2. #2
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346
    1) Add the refrence to the Excel Library.

    2)

    Dim exlApp As Excel.Application

    Set exlApp = New Excel.Application
    exlApp.FindFile
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  3. #3
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    I tried this to start with

    Private Sub Label0_Click()

    Const MSTB_MSEXCEL = 310&

    Application.Run "utility.util_StartMSToolbarApp", MSTB_MSEXCEL

    Dim exlApp As Excel.Application

    Set exlApp = New Excel.Application
    exlApp.FindFile

    End Sub

    But I get compile problems with exlApp As Excel.Application

    By the way, this is Access and Excel 95

    The following is what is produced when the Access command button is made using their Open Excel

    Sub Command0_Click()
    On Error GoTo Err_Command0_Click

    Const MSTB_MSEXCEL = 310&

    Application.Run "utility.util_StartMSToolbarApp", MSTB_MSEXCEL

    Exit_Command0_Click:
    Exit Sub

    Err_Command0_Click:
    MsgBox Err.Description
    Resume Exit_Command0_Click

    End Sub

    If I placed on a label

    Private Sub Label1_Click()


    Const MSTB_MSEXCEL = 310&

    Application.Run "utility.util_StartMSToolbarApp", MSTB_MSEXCEL


    End Sub

    That gets gets Excel open

    Mike

  4. #4
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346
    Did you add the Reference of the Excel Library? You would get a compile error if you didn't add that.
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  5. #5
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Quote Originally Posted by SCIROCCO
    Did you add the Reference of the Excel Library? You would get a compile error if you didn't add that.
    How do you do that?

    Mike

  6. #6
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346
    1) Open your Access database.

    2) Open a window where you have your code.

    3) In the Menu select Tools>References

    4) Add the Microsoft Excel Object Library
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  7. #7
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    I added the reference

    Then with the following

    Private Sub Label0_Click()

    Const MSTB_MSEXCEL = 310&

    Application.Run "utility.util_StartMSToolbarApp", MSTB_MSEXCEL

    Dim exlApp As Excel.Application

    Set exlApp = New Excel.Application
    exlApp.FindFile

    End Sub

    It then hits a problem with New Excel.Application and the mesage box says Invalid use of new keyword

    I don't know if this means anything but I just opened (in the main data base) the details of the code on one of the labels that opens a specified Word doc and checked references. There are only 4 that ticked which are the first 4:

    Visual Basic for Applications
    Microsoft Access for Windows 95
    Microsoft Jet SQL help topics
    Microsoft DAO 3.0 Object Library

  8. #8
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346
    Try replacing

    Set exlApp = New Excel.Application

    with

    Set exlApp = CreateObject("Excel.Application")

    Does that work?
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  9. #9
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    Just incase... I will post the code I use to open an excel app.

    Code:
    Private Function openXLCat()
        On Error GoTo openXL_err
    Dim path As String
    
        path = "J:\Price Sheet.xls"
        Set appXL = New Excel.Application
        appXL.Visible = True
        appXL.Workbooks.Open (path)
        
        Set appXL = Nothing
        Set workXL = Nothing
    openXL_exit:
        Exit Function
    
    openXL_err:
        MsgBox Err.Description
        Resume openXL_exit
        
    End Function
    Hope this helps.

    Excel and word are slightly different. (I had to figure out the differnces but that is what I came up with for excel).

    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  10. #10
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    Also if you need it to print I have code to do that as well...

    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  11. #11
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    SCIROCCO

    Just tried this. That opened Excel and brought up the normal list of Excel files in Mydocuments. It also opened two instances of Excel.

    Private Sub Label0_Click()

    Const MSTB_MSEXCEL = 310&

    Application.Run "utility.util_StartMSToolbarApp", MSTB_MSEXCEL

    Dim exlApp As Excel.Application

    Set exlApp = CreateObject("Excel.Application")

    exlApp.FindFile


    End Sub

  12. #12
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    JS,

    I tried yours but no success.

    However, I am not sure how to put yours into or between

    Private Sub Label0_Click()

    End Sub

    and that might be the problem.

    By the way, I just learnt something unpleasant about this coding last night. I have several labels on a from that open and insert into aboput 40 bookmarks in word. In the code there are 40 corresponsing macros that run and these macros are basically GoToControl and the Copy. All the macros have names that end in John such as C1John etc. I made another batch of labels that overlay the others and become invisible/visible based on an entry and copy all the code across. However the macro names need to be changed from C1John to just C1 etc. So I did Find and Replace and with great pleasure saw all the C1John type macro change to C1. But it also changed the C1John to C1 on the other labels. I was looking for such a bulk deal

    Mike

  13. #13
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    However, I am not sure how to put yours into or between

    Private Sub Label0_Click()

    End Sub
    You could put my code and your code as follows

    Code:
    Private Function openXLCat()
        On Error GoTo openXL_err
    Dim path As String
    
        path = "J:\Price Sheet.xls"
        Set appXL = New Excel.Application
        appXL.Visible = True
        appXL.Workbooks.Open (path)
        
        Set appXL = Nothing
        Set workXL = Nothing
    openXL_exit:
        Exit Function
    
    openXL_err:
        MsgBox Err.Description
        Resume openXL_exit
        
    End Function
    Private Sub Label0_Click()
        Call openxlcat
    End Sub
    That should work... sorry I didnt specify this from the beginning. (I did it that way because I dont use buttons I use roll over labels )

    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  14. #14
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    JS,

    Do I paste that between

    Private Sub Label0_Click()

    Your Code

    End Sub

    When I do paste in the only thing that shows is Call openxlcat. If I go over to the top right of the page and select Click (a couple of times) then your code appears but minus some of the bottom lines.

    By the way, I make a new .mdb file each time. There is no way I am trying this in my main data base, at least at the moment After I get this up and running I will retreat to the safety of my macros and queries.

    Mike

  15. #15
    Join Date
    Feb 2004
    Location
    Colorado state
    Posts
    65
    Why don't you just go the simple way and add a button from the toolbox to open excel?

    Mike

Posting Permissions

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