update an excel worksheet from access
Posted on 2012-09-06
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
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
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.