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