How to programmically upload Excel file to Sharepoint?

[Hii Sing Chung]

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

 

[Patrick Molloy]

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]

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.

 

[Barb Reinhardt]

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]

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.