update an excel worksheet from access

Posted on 2012-09-06
Last Modified: 2012-09-19
I have an excel worksheet for schedule want to update the sheet from access while a button pressed. I wrote VBA code as below:
On Error Resume Next
Set xl = GetObject(, "Excel.Application")

Dim xl As Object
Dim wb_Target As Object
Dim ws_Target As Object

If Err.Number <> 0 Then
    Set xl = CreateObject("Excel.Application")
    blnEXCEL = True
End If
On Error GoTo 0
Set wb_Target = xl.Workbooks.Open("C:\test\Schedule.xls")
Set ws_Target = wb_Target.Sheets("Updated Schedule")
xl.Visible = False

strCurrentTime = Format(Now, "HH:MM:SS")

ws_Target.Cells(TimeRow, TimeColumn) = strCurrentTime
wb_Target.Close True  
Set wb_Target = Nothing
Set xl = Nothing

Though its updating the cell with the current time the problem is I cant leave open shechule.xls. If the excel is open it gives error and could'nt update. I have a powerpoint which link excel the sheet "Updated Schedule" and I want to keep displaying with updated data from access through excel.
Would you please advise to update data from access while excel file is open where need to change.
One more thing I want to do is, want to assign current time - 10 miniutes.
for example if now 10:15AM I want to assign 10:05AM. VBA code for that would be great.

Thanks in advace for your prompt response.
Question by:alam747
    LVL 39

    Accepted Solution

    You can allow multiuser access to this workbook(, but you should refresh data in Powerpoint, changes will not be shown immediately.
    Time is not so difficult:
    strCurrentTime = Format(Dateadd("n", -10, Now), "HH:MM:SS")

    Author Comment

    Hi als315,

    I make workbook shared using tools but still if its open asking me want re-open it or not.
    Would you please advise which option need to be checked/unchecked to avoid conflict.

    LVL 39

    Expert Comment

    When (or where) it is asking you? In Powerpoint?

    Author Comment

    Hi asl315,
    Its because the Schedule.xls file was open in my end while I execute the access program.
    I will check if its happen while its open in other workstation( normally it will ) and let you know then.


    Author Closing Comment

    Thanks a lot...

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    Many companies are making the switch from Microsoft to Google Apps ( Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now