Could this be a problem with using open on a sharepoint site or do I have
something wrong with an object? Since it is a run-time error it doesn't tell
me exactly what to look for .
thanks,


Option Explicit
Private reportTitle, filePath, reportDate As String

'Copies Excel spreadsheet from http://vsps02/sites/default.aspx/Capacity
Managmenet/Weekly Report/


Sub CopySheet()
Dim sharePointURL As String
Dim objExcel, objWorkbook, objWorksheet As Object
sharePointURL = "http://vsps02/sites/default.aspx"
reportTitle = "Capacity Report"
reportDate = "07-0630"
filePath = "c:\\Temp\Segmentation"



Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open(filePath & reportDate & " " &
reportTitle)
objExcel.Visible = True

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

ActiveWorkbook.SaveAs Filename:=filePath & reportDate & " " & reportTitle
'ActiveWorkbook.Close
End Sub

I found the runtime error. I had a problem with the path. Sorry.

"Janis" wrote:

Could this be a problem with using open on a sharepoint site or do I have
something wrong with an object? Since it is a run-time error it doesn't tell
me exactly what to look for .
thanks,


Option Explicit
Private reportTitle, filePath, reportDate As String

'Copies Excel spreadsheet from http://vsps02/sites/default.aspx/Capacity
Managmenet/Weekly Report/


Sub CopySheet()
Dim sharePointURL As String
Dim objExcel, objWorkbook, objWorksheet As Object
sharePointURL = "http://vsps02/sites/default.aspx"
reportTitle = "Capacity Report"
reportDate = "07-0630"
filePath = "c:\\Temp\Segmentation"



Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open(filePath & reportDate & " " &
reportTitle)
objExcel.Visible = True

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

ActiveWorkbook.SaveAs Filename:=filePath & reportDate & " " & reportTitle
'ActiveWorkbook.Close
End Sub

Just a note but most of your variables are probably not what you think they
are. The majority are of type variant. Check out this link...

http://www.cpearson.com/excel/variables.htm
--
HTH...

Jim Thomlinson


"Janis" wrote:

I found the runtime error. I had a problem with the path. Sorry.

"Janis" wrote:

Could this be a problem with using open on a sharepoint site or do I have
something wrong with an object? Since it is a run-time error it doesn't tell
me exactly what to look for .
thanks,


Option Explicit
Private reportTitle, filePath, reportDate As String

'Copies Excel spreadsheet from http://vsps02/sites/default.aspx/Capacity
Managmenet/Weekly Report/


Sub CopySheet()
Dim sharePointURL As String
Dim objExcel, objWorkbook, objWorksheet As Object
sharePointURL = "http://vsps02/sites/default.aspx"
reportTitle = "Capacity Report"
reportDate = "07-0630"
filePath = "c:\\Temp\Segmentation"



Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open(filePath & reportDate & " " &
reportTitle)
objExcel.Visible = True

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

ActiveWorkbook.SaveAs Filename:=filePath & reportDate & " " & reportTitle
'ActiveWorkbook.Close
End Sub

Thanks, I read your web page. I didn't know that dim var x,y,z as string
was not the same as
dim var x as string
dim var y as string
dim var z as string
that seems logical that they would be the same thing
thanks again

"Jim Thomlinson" wrote:

Just a note but most of your variables are probably not what you think they
are. The majority are of type variant. Check out this link...

http://www.cpearson.com/excel/variables.htm
--
HTH...

Jim Thomlinson


"Janis" wrote:

I found the runtime error. I had a problem with the path. Sorry.

"Janis" wrote:

Could this be a problem with using open on a sharepoint site or do I have
something wrong with an object? Since it is a run-time error it doesn't tell
me exactly what to look for .
thanks,


Option Explicit
Private reportTitle, filePath, reportDate As String

'Copies Excel spreadsheet from http://vsps02/sites/default.aspx/Capacity
Managmenet/Weekly Report/


Sub CopySheet()
Dim sharePointURL As String
Dim objExcel, objWorkbook, objWorksheet As Object
sharePointURL = "http://vsps02/sites/default.aspx"
reportTitle = "Capacity Report"
reportDate = "07-0630"
filePath = "c:\\Temp\Segmentation"



Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open(filePath & reportDate & " " &
reportTitle)
objExcel.Visible = True

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

ActiveWorkbook.SaveAs Filename:=filePath & reportDate & " " & reportTitle
'ActiveWorkbook.Close
End Sub

Workbook.SaveCopyAs(fileName) solved the problem for me

"Janis" wrote:a

Could this be a problem with using open on a sharepoint site or do I have
something wrong with an object? Since it is a run-time error it doesn't tell
me exactly what to look for .
thanks,


Option Explicit
Private reportTitle, filePath, reportDate As String

'Copies Excel spreadsheet from http://vsps02/sites/default.aspx/Capacity
Managmenet/Weekly Report/


Sub CopySheet()
Dim sharePointURL As String
Dim objExcel, objWorkbook, objWorksheet As Object
sharePointURL = "http://vsps02/sites/default.aspx"
reportTitle = "Capacity Report"
reportDate = "07-0630"
filePath = "c:\\Temp\Segmentation"



Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open(filePath & reportDate & " " &
reportTitle)
objExcel.Visible = True

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

ActiveWorkbook.SaveAs Filename:=filePath & reportDate & " " & reportTitle
'ActiveWorkbook.Close
End Sub