Auto open/close and update shared workbook

**Nord** · 03-05-2009, 05:58 AM

Hello everyone,

I'm looking for a 100% automated way to display a graph, in fullscreen mode on a projector. The graph is located in a tab of a bigger document and is updated frequently during the day by several persons.
A new document is created every day, luckily with windows standard date formatting in the end, ex 2009-03-05 ( %date% )

I have set up a dedicated PC to the projector that runs Excel 2003

So far, i've only put this line in a .bat file

"\\server\x\x\x\daily report %date%.xls"

So i can at least open the right document every day with help of windows task scheduler..

but I have a few problems here..

the right tab has to be opened.
I want to autosave every 15 minutes, to update the workbook
the tab containing the graph is to be viewed in fullscreen

help, Anyone?

**Richard Buttrey** · 03-05-2009, 06:26 AM

Hi,

1.

Sheets("MySheet").Activate

2.

Sub Autosave()
    Dim dTime As Date
    dTime = Time
    Application.OnTime dTime + TimeValue("00:15:00"), "Autosave"
    ThisWorkbook.Save
End Sub

3. The full screen is an Application level instruction, not a tab instruction.

Application.DisplayFullScreen = True

If you just want the graph to display on it's own, you'd be better putting it on a Chart Sheet rather than having it embedded on an existing sheet.

HTH
Application.OnTime dTime + TimeValue("00:0:05"), "Autosave"
ThisWorkbook.Save
End Sub[/CODE]

**Nord** · 03-05-2009, 07:11 AM

Thanks!

Only one thing though. How do i get this macro to initiate automatically?

* The chart is in a chart sheet..

**Richard Buttrey** · 03-05-2009, 07:19 AM

Hi,

You could call it from the Workbook_Open event and leave it running.

HTH

**Nord** · 03-11-2009, 03:34 AM

Ok here's how it ended.
This script runs in the background.

Sub Macro1()

Dim TodaysDate As String

TodaysDate = Format(Date, "yyyy")
TodaysDate = TodaysDate & "-" & Format(Date, "mm")
TodaysDate = TodaysDate & "-" & Format(Date, "dd")
WorkbookShareLocation = "\\server\folder\folder\daily report "
Workbook = "daily report "

If Dir(WorkbookShareLocation & TodaysDate & ".xls") <> "" Then

GoTo Start
Else

GoTo Waiting
End If

Start:

Workbooks.Open (WorkbookShareLocation & TodaysDate & ".xls")
Windows(Workbook & TodaysDate & ".xls").Activate
Sheets("Diagram").Select
Application.DisplayFullScreen = True
ActiveChart.SizeWithWindow = True

Waiting:

Dim dTime As Date
dTime = Time
Application.OnTime dTime + TimeValue("00:15:00"), "Macro1"
ActiveWorkbook.Save

End Sub

Thanks for helping out