[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


update an excel worksheet from access

Posted on 2012-09-06
Medium Priority
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
  • 3
  • 2
LVL 40

Accepted Solution

als315 earned 2000 total points
ID: 38375051
You can allow multiuser access to this workbook(http://www.technize.net/how-to-work-simultaneously-on-one-excel-workbook-by-multiple-users/), 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

ID: 38375854
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 40

Expert Comment

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

Author Comment

ID: 38381442
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

ID: 38416346
Thanks a lot...

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

872 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