How to programmically upload Excel file to Sharepoint?


H

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
 
Ad

Advertisements

P

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
 
H

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.
 
B

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
 
Ad

Advertisements

H

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top