Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  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
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Dim xl As Excel.Application
    Set xl = CreateObject("Excel.application")

    Regards

  3. #3
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    The following opened a new workbook. But how do I open a specific xls file such as c:\letters\zc.xls

    Private Sub Label1_Click()

    Const MSTB_MSEXCEL = 310&

    Application.Run "utility.util_StartMSToolbarApp", MSTB_MSEXCEL

    Dim xl As Excel.Application
    Set xl = CreateObject("Excel.application")


    End Sub

  4. #4
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    You can do anything with the xl object you can do in excel....

    xl.Workbooks.Open FileName:="c:\letters\zc.xls"

    Regards

  5. #5
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Private Sub Label1_Click()

    Const MSTB_MSEXCEL = 310&

    Application.Run "utility.util_StartMSToolbarApp", MSTB_MSEXCEL

    Dim xl As Excel.Application
    Set xl = CreateObject("Excel.application")

    xl.Workbooks.Open FileName:="c:\letters\zc.xls"

    End Sub

    That opened ZC.xls but it was like some other version as the file name was not at the top.

    Now for something weird and whether thius is because Access 95 is running on WinXP, who knows. I made a new .mdb file with just a form and the label.

    After I ran it a couple of times I then closed the data base down and reopened and the form had gone. However it was there because Analayse showed it up but it could not be seen in the data base. So I made another data base and ran the code a couple of times and then close and reopened the data base. The form was there but could not opened inclding in design view. Message coming was that sort stuff....the form name might be mispelled and so on.

    There must be some way I can duplicate with Excel the way my code opens Word docs.

    I think I have moved from disliking Excel to hating Excel

    Mike

  6. #6
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Hmz works find for me... Did you reference the Microsoft Excel ... Object Library??

    Try adding
    Xl.visible = true
    and try changing the dim to
    Dim xl As New Excel.Application ' which opens a NEW excel
    try removing the bit you added (it runs excel, but that can also be done using the "new" dim)
    Const MSTB_MSEXCEL = 310&

    Application.Run "utility.util_StartMSToolbarApp", MSTB_MSEXCEL

    I am running Office 97...

    BR

  7. #7
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    BR,

    Yes, I did the referencing part.

    Can you post up your "ready to go" code and for what will go on a label

    Private Sub Label1_Click()

    End Sub

    and I will paste in that way there can be no stuff ups.

    The ridiculous part about this is that I don't even need to do it but someone else mentioned it the other day and I thought it might be useful to have on board. Actually it could be fun spraying Access field data all over a spreadsheet

    By the way, thanks for your help and time so far.

    Mike

  8. #8
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    I think part of the problem isa conflict caused by Access 95 (and perhaps Excel95) running on Windows XP.

    I fixed the disappearing form by importing a form from my main data base. One of the things I have noticed is that if I make a new form via Access/Win XP then when changes are made you can't save the changes and all that stuff comes up about another user. However, forms that were made prior to Win XP being used are all normal. That also includes copies of them.

    So far this is now working except is opening what almost amounts to a second version of the xls file. In addition, when close it down and then try and open the file by the normal method of going to Excel, you get all the stuff about another user editing. In fact rebooting the computer was the only way to fix it. Perhaps there is a problem with xls files being made from Excel 95 in the Win XP environment.

    What won't work is xl.Workbooks.EditGoTo and xl.Workbooks.EditPaste but that works for the Word for pasting Access field data into bookmarks.

    Private Sub Label306_Click()
    Const MSTB_MSEXCEL = 310&

    Application.Run "utility.util_StartMSToolbarApp", MSTB_MSEXCEL

    Dim xl As Excel.Application


    Set xl = CreateObject("Excel.application")

    xl.Workbooks.Open FileName:="c:\letters\abc.xls"

    End Sub

  9. #9
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    You CANNOT use word VBA in Excell... You have to use EXCEL vba !!!!!

    A as in Application...

    To find a field in excel you use Range("A1") or Cells(1,1) then put the value in

    xl.range("A1") = "something"

    BR

  10. #10
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    I added

    xl.Cells(1, 1) = "E17"

    and

    xl.Workbook.Cells(1, 1) = "E17"

    But in both cases the DeBug put a box around them

  11. #11
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Tip: record what you are trying to do in excel first then do it in Access cheating of the recorded code in excel....

  12. #12
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    I don't know what you mean by that.

    What I would like to be able to do is to place values from Access fields into pre determined cells in Access. In other words the counterpart of what I do for Bookmarks in Word.

    Mike

  13. #13
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    I know and anderstand what it is you want to do. what i am saying is you can use the recorder in both Excel and word to find the code you need to have

    Your code is probably failing because E17 is (also) a cell location
    try putting a ' in front like so "'E17"

    This works!!!
    sub test()
    Dim xl As new Excel.Application

    Set xl = CreateObject("Excel.application")

    with xl
    .Workbooks.Open FileName:="c:\letters\abc.xls"
    .Cells(1, 1) = "This"
    .Cells(2, 2) = "is"
    .Cells(3, 3) = "Good"
    .Range("C4") = "this"
    .Range("D5") = "is"
    .Range("E6") = "also good"
    end with
    end sub

  14. #14
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Sub test() Highlights an a message Ambigous name detected:test


    Dim xl As New Excel.Application My highlighted section is what also highlights on run compile and message is Inavid use of new keyword. I get that every time irrespective of whatever else I have had in the module.

    I am not sure what you mean by this .Range("C4") = "this"

    Can we try this. I just made another new data base and a form with one label. I go to build code for OnClick and of course the following presents itself

    Private Sub Label0_Click()

    End Sub

    What goes between Private Sub Label0_Click() and End Sub so as to open Excel at a specified file name and at a specified cell.

    Mike

  15. #15
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Quote Originally Posted by Mike375
    Sub test() Highlights an a message Ambigous name detected:test
    This means you have another sub called test, rename this one to whatever... Test987654321() for instance... thats bound to be unique

    Quote Originally Posted by Mike375
    Dim xl As New Excel.Application My highlighted section is what also highlights on run compile and message is Inavid use of new keyword. I get that every time irrespective of whatever else I have had in the module.
    Must be a 95 thing, it runs fine in 97! Remove the New word

    Quote Originally Posted by Mike375
    I am not sure what you mean by this .Range("C4") = "this"
    it is surrounded by the With ... End with command. Meaning that everthing starting with a . should be preceded by xl so the full line says xl.range("C4") = "this"
    Which should put the word this into row 4 column C (3)

    Quote Originally Posted by Mike375
    Can we try this. I just made another new data base and a form with one label. I go to build code for OnClick and of course the following presents itself

    Private Sub Label0_Click()

    End Sub

    What goes between Private Sub Label0_Click() and End Sub so as to open Excel at a specified file name and at a specified cell.

    Mike
    How about you try my code i gave as a sample, IT WORKS (except maybe for the new word in the dim)

    Regards

Posting Permissions

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