I would like to have VBA to automatically/programmically upload an Excel
file to a Sharepoint site. I tried the code below but got 'File not Found'
error. I could manually save as the file to the same path, so the path is
correct. Any suggestions?
Sub PostToSharepoint()
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim strWorkSheet As String
Dim cell As Range
Dim strFileName
strWorkSheet = Format(Date - 1, "mmmmyy")
Set wbBook = ThisWorkbook
Set wsSheet = ThisWorkbook.Sheets(strWorkSheet)
wsSheet.Activate
strFileName = "http://myweb:8585/sites/it_security/monitoring/Shared
Documents/Management Reports/Daily Reports.xls"
For Each cell In Range(Range("B4"), Range("B34"))
If cell.Value <= Now() - 1 And IsFormula(cell.Offset(0, 1)) =
True Then
cell.Range(cell.Offset(0, 1), cell.Offset(0, 4)).Copy
Range(cell.Offset(0, 1), cell.Offset(0, 4)).PasteSpecial
(xlPasteValues)
End If
Next cell
ThisWorkbook.SaveCopyAs Filename:=strFileName 'File not found error
occurred'
End Sub
Function IsFormula(Check_Cell As Range)
IsFormula = Check_Cell.HasFormula
End Function

try changing the file name. Your code uses this variable strFileName , which
is the file that you opened. try
ThisWorkbook.SaveCopyAs "Test"

and see where it gest saved to

"Hii Sing Chung" wrote:

I would like to have VBA to automatically/programmically upload an Excel
file to a Sharepoint site. I tried the code below but got 'File not Found'
error. I could manually save as the file to the same path, so the path is
correct. Any suggestions?
Sub PostToSharepoint()
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim strWorkSheet As String
Dim cell As Range
Dim strFileName
strWorkSheet = Format(Date - 1, "mmmmyy")
Set wbBook = ThisWorkbook
Set wsSheet = ThisWorkbook.Sheets(strWorkSheet)
wsSheet.Activate
strFileName = "http://myweb:8585/sites/it_security/monitoring/Shared
Documents/Management Reports/Daily Reports.xls"
For Each cell In Range(Range("B4"), Range("B34"))
If cell.Value <= Now() - 1 And IsFormula(cell.Offset(0, 1)) =
True Then
cell.Range(cell.Offset(0, 1), cell.Offset(0, 4)).Copy
Range(cell.Offset(0, 1), cell.Offset(0, 4)).PasteSpecial
(xlPasteValues)
End If
Next cell
ThisWorkbook.SaveCopyAs Filename:=strFileName 'File not found error
occurred'
End Sub
Function IsFormula(Check_Cell As Range)
IsFormula = Check_Cell.HasFormula
End Function

Dear Patrick,

I already assigned the variable strFileName to
""http://myweb:8585/sites/it_security/monitoring/Shared Documents/Management
Reports/Daily Reports.xls". If I use "test" it will work. It failed because
it is not a filesystem path, it is an http path, so my question is how to
tell Excel to use http as path or something like that. Alternatively, is
there a way to get around this limitation so that I can met my requirement
of uploading file to a Sharepoint site automatically.

"Patrick Molloy" wrote in message
...
try changing the file name. Your code uses this variable strFileName ,
which
is the file that you opened. try
ThisWorkbook.SaveCopyAs "Test"

and see where it gest saved to

"Hii Sing Chung" wrote:

I would like to have VBA to automatically/programmically upload an Excel
file to a Sharepoint site. I tried the code below but got 'File not
Found'
error. I could manually save as the file to the same path, so the path is
correct. Any suggestions?
Sub PostToSharepoint()
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim strWorkSheet As String
Dim cell As Range
Dim strFileName
strWorkSheet = Format(Date - 1, "mmmmyy")
Set wbBook = ThisWorkbook
Set wsSheet = ThisWorkbook.Sheets(strWorkSheet)
wsSheet.Activate
strFileName =
"http://myweb:8585/sites/it_security/monitoring/Shared
Documents/Management Reports/Daily Reports.xls"
For Each cell In Range(Range("B4"), Range("B34"))
If cell.Value <= Now() - 1 And IsFormula(cell.Offset(0, 1)) =
True Then
cell.Range(cell.Offset(0, 1), cell.Offset(0, 4)).Copy
Range(cell.Offset(0, 1), cell.Offset(0, 4)).PasteSpecial
(xlPasteValues)
End If
Next cell
ThisWorkbook.SaveCopyAs Filename:=strFileName 'File not found
error
occurred'
End Sub
Function IsFormula(Check_Cell As Range)
IsFormula = Check_Cell.HasFormula
End Function

I've mapped sharepoint drives that I access frequently to an available drive
letter.. I've found that I need to make sure that the WebClient service is
running to be able to access it that way though.

HTH,
Barb Reinhardt

"Hii Sing Chung" wrote:

Dear Patrick,

I already assigned the variable strFileName to
""http://myweb:8585/sites/it_security/monitoring/Shared Documents/Management
Reports/Daily Reports.xls". If I use "test" it will work. It failed because
it is not a filesystem path, it is an http path, so my question is how to
tell Excel to use http as path or something like that. Alternatively, is
there a way to get around this limitation so that I can met my requirement
of uploading file to a Sharepoint site automatically.

"Patrick Molloy" wrote in message
...
try changing the file name. Your code uses this variable strFileName ,
which
is the file that you opened. try
ThisWorkbook.SaveCopyAs "Test"

and see where it gest saved to

"Hii Sing Chung" wrote:

I would like to have VBA to automatically/programmically upload an Excel
file to a Sharepoint site. I tried the code below but got 'File not
Found'
error. I could manually save as the file to the same path, so the path is
correct. Any suggestions?
Sub PostToSharepoint()
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim strWorkSheet As String
Dim cell As Range
Dim strFileName
strWorkSheet = Format(Date - 1, "mmmmyy")
Set wbBook = ThisWorkbook
Set wsSheet = ThisWorkbook.Sheets(strWorkSheet)
wsSheet.Activate
strFileName =
"http://myweb:8585/sites/it_security/monitoring/Shared
Documents/Management Reports/Daily Reports.xls"
For Each cell In Range(Range("B4"), Range("B34"))
If cell.Value <= Now() - 1 And IsFormula(cell.Offset(0, 1)) =
True Then
cell.Range(cell.Offset(0, 1), cell.Offset(0, 4)).Copy
Range(cell.Offset(0, 1), cell.Offset(0, 4)).PasteSpecial
(xlPasteValues)
End If
Next cell
ThisWorkbook.SaveCopyAs Filename:=strFileName 'File not found
error
occurred'
End Sub
Function IsFormula(Check_Cell As Range)
IsFormula = Check_Cell.HasFormula
End Function

Dear Barb,

The mapping of drive to Sharepoint is not possible in our environment. If
not, it will be easy to accomplish. I have to workaround this restriction.
Network folder and file sharing is 'locked-down' in our environment. As
manually I can save or copy the file to sharepoint, I just can't understand
why it is difficult to do it programmically.

"Barb Reinhardt" wrote in message
...
I've mapped sharepoint drives that I access frequently to an available
drive
letter.. I've found that I need to make sure that the WebClient service
is
running to be able to access it that way though.

HTH,
Barb Reinhardt

"Hii Sing Chung" wrote:

Dear Patrick,

I already assigned the variable strFileName to
""http://myweb:8585/sites/it_security/monitoring/Shared
Documents/Management
Reports/Daily Reports.xls". If I use "test" it will work. It failed
because
it is not a filesystem path, it is an http path, so my question is how to
tell Excel to use http as path or something like that. Alternatively, is
there a way to get around this limitation so that I can met my
requirement
of uploading file to a Sharepoint site automatically.

"Patrick Molloy" wrote in
message
...
try changing the file name. Your code uses this variable strFileName ,
which
is the file that you opened. try
ThisWorkbook.SaveCopyAs "Test"

and see where it gest saved to

"Hii Sing Chung" wrote:

I would like to have VBA to automatically/programmically upload an
Excel
file to a Sharepoint site. I tried the code below but got 'File not
Found'
error. I could manually save as the file to the same path, so the path
is
correct. Any suggestions?
Sub PostToSharepoint()
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim strWorkSheet As String
Dim cell As Range
Dim strFileName
strWorkSheet = Format(Date - 1, "mmmmyy")
Set wbBook = ThisWorkbook
Set wsSheet = ThisWorkbook.Sheets(strWorkSheet)
wsSheet.Activate
strFileName =
"http://myweb:8585/sites/it_security/monitoring/Shared
Documents/Management Reports/Daily Reports.xls"
For Each cell In Range(Range("B4"), Range("B34"))
If cell.Value <= Now() - 1 And IsFormula(cell.Offset(0,
1)) =
True Then
cell.Range(cell.Offset(0, 1), cell.Offset(0, 4)).Copy
Range(cell.Offset(0, 1), cell.Offset(0,
4)).PasteSpecial
(xlPasteValues)
End If
Next cell
ThisWorkbook.SaveCopyAs Filename:=strFileName 'File not found
error
occurred'
End Sub
Function IsFormula(Check_Cell As Range)
IsFormula = Check_Cell.HasFormula
End Function