copy sheet from Server URL
 

What I'm trying to do is automate the copying of one spreadsheet ("P
History") from a workbook on a Sharepoint server URL and paste to a new
local workbook on a weekly basis.

One problem is the report title:

The title remains the same but they append the date to it. The weekly
report is added to the Sharepoint server every 7 days.
The date is formatted year-monthdate,
for example 07-0717, for today.
Is there a function that will give me the date for each consecutive report?
Somehow I have to calculate the correct one if it is possible in order to
copy the correct sheet.
I don't know if it is possible to copy this report directly off the server?

The second problem is I am not quite sure I have the code right to do the
copy.
-------------------code-----------------------------

Option Explicit
Dim ReportTitle, FilePath As String
Dim ReportDate As Date


Sub CopySharePointSheet()
Set ReportTitle = "My Report Name"
Set ReportDate = 07-????
Set FilePath = "c:\\Temp\"



Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open(HTTP://my web site/ReportDate
ReportTitle)
objExcel.Visible = True

Set objWorksheet = objWorkbook.Worksheets("P History")
objWorksheet.Copy


ActiveWorkbook.SaveAs Filename:=ReportDate + ReportName + FilePath
'ActiveWorkbook.Close
End Sub



THANKS :-)