I found this code that opens up a workbook, saves it and closes it. I will
be calling a further macro within this code latter. My problem is that it
will open a specific file called "Book1.xls", however I have a file that
changes name everyweek and I wish to run below each day at 9:00am. Is it
possible to have say in Sheet1 A1 the file name so that the code below can
reference to it? Is that possible?

Q 2 - do I place the Sub RunOnTime within a 'normal' module within my file?

Thanks


Sub RunOnTime()
'Run this once and at the next 9am it will run
'the procedure OpenBook2
Application.OnTime "09:00:00", "OpenBook2"
End Sub

Sub OpenBook2()
'This procedure opens the book2.xls file
'Prints it and closes it without saving
'It then loops back to RunOnTime and runs again
'at the next 9am
Dim wb2 As Workbook
Set wb2 = Workbooks.Open("C:\Book1.xls")
wb2.Close SaveChanges:=True
RunOnTime
End Sub

#1. You can pick up the name in A1 of sheet1 by changing this line:

Set wb2 = Workbooks.Open("C:\Book1.xls")
to
Set wb2 = Workbooks.Open(thisworkbook.worksheets("sheet1").r ange("a1").value

I'm not sure how you go about updating that cell's value for the next day,
though.

#2. Yep.

#3. If you haven't looked at Chip Pearson's notes about OnTime, you may want to
review them:
http://www.cpearson.com/excel/ontime.htm

John wrote:

I found this code that opens up a workbook, saves it and closes it. I will
be calling a further macro within this code latter. My problem is that it
will open a specific file called "Book1.xls", however I have a file that
changes name everyweek and I wish to run below each day at 9:00am. Is it
possible to have say in Sheet1 A1 the file name so that the code below can
reference to it? Is that possible?

Q 2 - do I place the Sub RunOnTime within a 'normal' module within my file?

Thanks

Sub RunOnTime()
'Run this once and at the next 9am it will run
'the procedure OpenBook2
Application.OnTime "09:00:00", "OpenBook2"
End Sub

Sub OpenBook2()
'This procedure opens the book2.xls file
'Prints it and closes it without saving
'It then loops back to RunOnTime and runs again
'at the next 9am
Dim wb2 As Workbook
Set wb2 = Workbooks.Open("C:\Book1.xls")
wb2.Close SaveChanges:=True
RunOnTime
End Sub

--

Dave Peterson

Thanks Dave

I just re-name the file each week, so I guess the changed name will follow
in A1, but thinking, that will mean all my 'old' files with the same command
will continue to run also. Guess I just have to remove the OnTime command
from them


"Dave Peterson" wrote in message
...
#1. You can pick up the name in A1 of sheet1 by changing this line:

Set wb2 = Workbooks.Open("C:\Book1.xls")
to
Set wb2 =
Workbooks.Open(thisworkbook.worksheets("sheet1").r ange("a1").value

I'm not sure how you go about updating that cell's value for the next day,
though.

#2. Yep.

#3. If you haven't looked at Chip Pearson's notes about OnTime, you may
want to
review them:
http://www.cpearson.com/excel/ontime.htm

John wrote:

I found this code that opens up a workbook, saves it and closes it. I
will
be calling a further macro within this code latter. My problem is that it
will open a specific file called "Book1.xls", however I have a file that
changes name everyweek and I wish to run below each day at 9:00am. Is it
possible to have say in Sheet1 A1 the file name so that the code below
can
reference to it? Is that possible?

Q 2 - do I place the Sub RunOnTime within a 'normal' module within my
file?

Thanks

Sub RunOnTime()
'Run this once and at the next 9am it will run
'the procedure OpenBook2
Application.OnTime "09:00:00", "OpenBook2"
End Sub

Sub OpenBook2()
'This procedure opens the book2.xls file
'Prints it and closes it without saving
'It then loops back to RunOnTime and runs again
'at the next 9am
Dim wb2 As Workbook
Set wb2 = Workbooks.Open("C:\Book1.xls")
wb2.Close SaveChanges:=True
RunOnTime
End Sub

--

Dave Peterson

Renaming the file won't affect what's in A1, though. That would be a manual
effort???

John wrote:

Thanks Dave

I just re-name the file each week, so I guess the changed name will follow
in A1, but thinking, that will mean all my 'old' files with the same command
will continue to run also. Guess I just have to remove the OnTime command
from them

"Dave Peterson" wrote in message
...
#1. You can pick up the name in A1 of sheet1 by changing this line:

Set wb2 = Workbooks.Open("C:\Book1.xls")
to
Set wb2 =
Workbooks.Open(thisworkbook.worksheets("sheet1").r ange("a1").value

I'm not sure how you go about updating that cell's value for the next day,
though.

#2. Yep.

#3. If you haven't looked at Chip Pearson's notes about OnTime, you may
want to
review them:
http://www.cpearson.com/excel/ontime.htm

John wrote:

I found this code that opens up a workbook, saves it and closes it. I
will
be calling a further macro within this code latter. My problem is that it
will open a specific file called "Book1.xls", however I have a file that
changes name everyweek and I wish to run below each day at 9:00am. Is it
possible to have say in Sheet1 A1 the file name so that the code below
can
reference to it? Is that possible?

Q 2 - do I place the Sub RunOnTime within a 'normal' module within my
file?

Thanks

Sub RunOnTime()
'Run this once and at the next 9am it will run
'the procedure OpenBook2
Application.OnTime "09:00:00", "OpenBook2"
End Sub

Sub OpenBook2()
'This procedure opens the book2.xls file
'Prints it and closes it without saving
'It then loops back to RunOnTime and runs again
'at the next 9am
Dim wb2 As Workbook
Set wb2 = Workbooks.Open("C:\Book1.xls")
wb2.Close SaveChanges:=True
RunOnTime
End Sub

--

Dave Peterson

--

Dave Peterson