Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Duesseldorf, Germany
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automation with excel (2002)

    Hi,
    I want to get some spreadsheets opened from Acces and then closed directly afterwards in order to get the sheets updated via queries run at spreadsheet open time.
    In de###### it runs well, but real time it seems to be to fast and when opening the 2nd sheet it get a message saying that the application may be overloaded. Also I don't want to be asked whether I want to save the files, I really want it. How do I get this running? Here ist (part of) the code:

    Set rst = db.OpenRecordset("FileNames", dbOpenDynaset, dbReadOnly)
    With rst
    .MoveFirst
    Do While Not .EOF
    strFile= !File
    Set applExcl = GetObject(strPfad & strFile)
    applExcl.Application.Visible = True
    applExcl.Windows(1).Visible = True
    applExcl.Application.Visible = False
    applExcl.Application.Quit
    Set applExcl = Nothing
    .MoveNext
    Loop
    End With
    rst.Close
    Set applExcl = Nothing
    End Sub

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automation with excel (2002)

    I never try it in automation, but what happens if you insert a DoEvents ?

    Set applExcl = Nothing
    DoEvents
    .MoveNext
    Francois

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    Duesseldorf, Germany
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automation with excel (2002)

    Thanks, but sorry, doesn't work. For 1 Spreadsheet it works fine.
    Maybe we have to wait for our clever American friends ot be awake.

  4. #4
    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: Automation with excel (2002)

    Hi Horst,
    Without knowing exactly what your Excel Workbook_Open code is doing it's hard to be too specific but does this code help:
    <pre>Dim appExcel as Excel.Application, wbkCurrent as excel.workbook
    Set rst = db.OpenRecordset("FileNames", dbOpenDynaset, dbReadOnly)
    With rst
    .MoveFirst
    set appExcel = new Excel.application
    appexcel.visible = false
    Do While Not .EOF
    strFile= !File
    set wbkCurrent = appExcel.workbooks.open(strPfad & strFile)
    with wbkCurrent
    .save
    .close
    end with
    Set wbkCurrent = Nothing
    .MoveNext
    Loop
    end With
    rst.Close
    appexcel.quit
    Set appExcel = Nothing
    </pre>

    It seemed to me an unnecessary overhead to be opening and closing Excel for each workbook so I've moved that bit outside the loop. You'll also need to set a reference to the Excel object library for this to work as it is, or change it to use getobject or createobject to create the Excel instance.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Star Lounger
    Join Date
    Jan 2001
    Location
    Duesseldorf, Germany
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automation with excel (2002)

    Thanks. Your code now works in principle. What I want to achieve is that the query in the workbook runs, updates the spreadsheet with the new database values, saves and closes.
    I have activated the background update facility and disabled the Update on open facility and added a ".RefreshAll" to the code.
    What now happens is that I get amessage saying that the update may be interrupted and if I wanted to proceed. I said "cancel" for all sheets. The are however updated. Without the .refreshall it runs, but no update. Also I want to supress the "Do you want to save yor changes " questions.

  6. #6
    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: Automation with excel (2002)

    OK I assume we're talking about QueryTables in your Excel workbooks? If so, then you may want to amend the Workbook_Open code so that it only executes if Application.UserControl = True. That way your automation code from Access won't automatically trigger it since the apllication instance is invisible. You will then need to add something along the lines of:
    wbkCurrent.Sheets(1).Querytables(1).Refresh
    before your .Save and .Close lines (if you have more than one sheet/querytable you'll need to loop through them.) This will hopefully cause your automation code to wait until the refresh has finished before continuing. If it does not, then you could use something like:
    Do until wbkCurrent.Sheets(1).querytables(1).refreshing = false
    Loop
    to pause your code until it's finished updating.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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