• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 536
  • Last Modified:

update an excel worksheet from access

Hi,
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
Err.Clear
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
xl.Quit
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.
0
alam747
Asked:
alam747
  • 3
  • 2
1 Solution
 
als315Commented:
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")
0
 
alam747Author Commented:
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.

Thanks
0
 
als315Commented:
When (or where) it is asking you? In Powerpoint?
0
 
alam747Author Commented:
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.

Thanks
0
 
alam747Author Commented:
Thanks a lot...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now