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

Thread: Noob problem

  1. #1
    Join Date
    Nov 2004
    Posts
    47

    Unanswered: Noob problem

    Hi, I've been using access for a while but never really used modules... Today I'm facing a new challenge. It might be really simple for you guys but since I dont know too much about VB, it is very tricky for me.

    What I want to do is build a module that would open an excel file, save and close it immediately afterwards. Basically, all I need is excel to load an excel workbook (maybe excel will already be opened) refresh its links with my access db, save and close. (id like it if the user didnt see the excel program openiong and closing...)

    Someone on these boards already posted a similar question and someone answered him with the following code, that I found very interesting.:



    Dim xlApp As excel.Application
    Dim xlWkb As excel.Workbook

    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If Err.number0 Then
    ' excel not already open
    Set xlApp = New excel.Application
    End If

    Set xlWkb = xlApp.Workbooks.Open("X:\Martin\AFMQ\DB\afmq.xls")
    xlWkb.Visible = True
    ' do whatever it is you got to do.
    ..
    xlWkb.Close

    if no xlApp.UserControl then
    ' opened from code
    xlApp.Quit
    End If

    Set xlWkb = Nothing
    Set xlApp = Nothing



    My problem is I have no clue how to adapt it to my access db,... when i start a new module, I always get the following line at the top...


    Option Compare Database


    should it stay there?

    And when I tried pushing the play button, it added this to teh bottom of the code...

    Sub excell()

    End Sub


    Maybe the code i got from the boards isnt good, but it looks good to my noob eye. My guess is I only have troubles adapting it to my db.

    Thanks for reading and I hope you can help me!


    Fuujin

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Ok How about this
    go to the references and choose Microsoft Excel ?.? Object Library
    where ?.? is a number based on the version of excel you have mine is 9.0

    next put this code in
    Code:
    Sub OpnExcel()
        Dim xlApp As Excel.Application
        Dim xlBook As Excel.Workbook
        
        'set references to excel objects
        Set xlApp = New Excel.Application
        Set xlBook = xlApp.Workbooks.Open("K:\Information\David\OP Activity.xls", True)
        
        'save the workbook as a new name
        xlBook.SaveAs "K:\Information\David\OP Activity2.xls"
        
        'close and quit out of excel
        xlBook.Close
        xlApp.Quit
        
        'get rid of the object references
        Set xlBook = Nothing
        Set xlApp = Nothing
    End Sub
    since we are not giving the user any control we can open a new application of excel without any problem,
    you may need to put in some error handling if the workbook has been moved or renamed
    but this will do the job that you want to do

    Dave

  3. #3
    Join Date
    Nov 2004
    Posts
    47
    Im sorry but I dont understand this,.. like I said, im a noob

    Ok How about this
    go to the references and choose Microsoft Excel ?.? Object Library
    where ?.? is a number based on the version of excel you have mine is 9.0

    what references, in Access? Cause what i want is to be able to get a macro in access that does what I asked in teh first post...

    your code seems nice, id like to try it, and THANK YOU VERY MUCH for helping!

  4. #4
    Join Date
    Nov 2004
    Posts
    47
    also, i would like the file to save over itself, not on a new name

    Thanks

  5. #5
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Ok this is fairly simple in the Code editor
    on the Menu Bar Tools-> References a box appears scroll down until you find
    Microsoft Excel ?.? object library click the check box by it's name and then press ok

    to save the file over itself replace the line

    xlBook.SaveAs "K:\Information\David\OP Activity2.xls"

    with

    xlBook.Save

    Dave

  6. #6
    Join Date
    Nov 2004
    Posts
    47
    this is weird, i see the reference on the tool menu, but its in grey, I cant use it.... have an idea why?

  7. #7
    Join Date
    Nov 2004
    Posts
    47
    have to be in design view, sorry

  8. #8
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Do you have a module open if not open one and try again or do you have anything highlighted on your project window (should be on the left hand side if not press ctrl + R) click on something in your current DB and try again

  9. #9
    Join Date
    Nov 2004
    Posts
    47
    ok it looks like its working!!!!!

    THANK A LOT !!!

    now how can i make a macro that calls that function?

  10. #10
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    are you assigning it to a control like a button or how do you want to call it,
    if it is a button go to the properties then to events choose the click event and click the ... button choose code then when the new code window pops up you can put in the line

    Call OpnExcel


    pedantic coder time
    This is just for reference for the future what i provided for you was a sub routine, a function is a routine that returns a value and is a distinct type in VBA

  11. #11
    Join Date
    Nov 2004
    Posts
    47
    Thanks a lot!



    Would it be possible to add something in the code to refresh the external data contained in the excel file before saving it? It already is on auto-refresh on startup but I dont think its working when called from the code... or maybe you can tell me...


    And could there be a way to see the computer open the excell file? Just to be sure its working,... When I push the play button it seams very quick,.. almost too quick hehe


    thanks again in advance

  12. #12
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Try this for your links

    Replace
    Set xlBook = xlApp.Workbooks.Open("K:\Information\David\OP Activity.xls", True)

    with
    Set xlBook = xlApp.Workbooks.Open("K:\Information\David\OP Activity.xls", 3)

    This should update your external data, i picked the wrong value :0

    Do you realy want it to open excel and close while being visible, it will only pop up for a few seconds if you really want ot do this then add in the line

    xlBook.Visible = True but i wouldnt bother if you want to know when the process is complete how about using a message box with
    MsgBox "Text in Here"

  13. #13
    Join Date
    Nov 2004
    Posts
    47
    Thanks!

    To answer your previous question, Id like to lauch it from a macro. How can I do it? Im not sure exactly when the event will take place, but it would probably be automatically after a form closes or maybe on a button,... So i was wondering if it was possible to insert it in a macro.

  14. #14
    Join Date
    Nov 2004
    Posts
    47
    Really curious, it doesnt seem to be working....
    well it doesnt update,...

    In my excel file, one of the sheet imports data from an access query, another sheet caculates some totals with the imported values. Then the sheet with the new calculated values is a linked table in access....

    Therefore, when values in the access query change, Excel when updating should transfer the new values from access to excel, than back to access in the linked table,... but nothing happens when i use the sub command.

    why???

    also, when i open my xcell wrkbook i get a popup that asks me if im sure i want an automatic refresh, could that cause the problem?

  15. #15
    Join Date
    Nov 2004
    Posts
    47
    ANyone else can help?? Im kinda blocked right now, I would appreciate it

Posting Permissions

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