VBA Compile error: on SysCmd (Sub or Function not defined)

I want to upload files to sharepoint passing the credentials. I saw this function on a post and it seems may work for me but when I compile it, I get Compile error on SysCmd. I'm not familiar with VBA, please advice, thanks for your help in advance.

Public Sub CopyToSharePoint()

On Error GoTo err_Copy

Dim xmlhttp
Dim sharepointUrl
Dim sharepointFileName
Dim tsIn
Dim sBody
Dim LobjXML As Object
Dim UserName As String
Dim pw As String
Dim RetVal
Dim i As Integer
Dim TotFiles As Integer
Dim Start As Date, Finish As Date
'Dim fso As New FileSystemObject 'Scripting.FileSystemObject
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Dim fldr As Object 'Folder
Dim f As Object 'File

Set fldr = fso.GetFolder("c:\vba2sharepoint\")

UserName = "mysharepointusername@domain.com" 'DLookup("[Username]", "tbl_UserData")
pw = "mysharepointpassword" 'DLookup("[Password]", "tbl_UserData")
sharepointUrl = "https://mysite.sharepoint.com/sites/xyz-uat/_layouts/15/start.aspx#/a1docsuat/"

Set LobjXML = CreateObject("Microsoft.XMLHTTP")

TotFiles = fldr.Files.Count

For Each f In fldr.Files

 sharepointFileName = sharepointUrl & f.Name

    Set tsIn = f.OpenAsTextStream
    sBody = tsIn.ReadAll
    tsIn.Close

    Set xmlhttp = CreateObject("MSXML2.XMLHTTP.6.0")

    xmlhttp.Open "PUT", sharepointFileName, False, UserName, pw

    xmlhttp.Send sBody

  i = i + 1

  RetVal = SysCmd(acSysCmdSetStatus, "File " & i & " of " & TotFiles & " copied...")

Next f

   RetVal = SysCmd(acSysCmdClearStatus)

  Set LobjXML = Nothing

  Set fso = Nothing

err_Copy:

MsgBox Err & " " & Err.Description

If Err <> 0 Then
  MsgBox Err & " " & Err.Description
  Resume Next
End If

End Sub

Open in new window

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 48

Wayne Taylor (webtubbs)Commented: 2015-03-16

SysCmd is an Access command which is why you get an error in Excel. It is used to display messages in the status bar, which can be done in Excel with this code...

Application.StatusBar = "File " & i & " of " & TotFiles & " copied..."

...or to clear, use this...

Application.StatusBar = ""

Wayne

LVL 8

a0k0a7Author Commented: 2015-03-16

Hi Wayne, thanks for your help, I'm new to VBA... are you suggesting to replace

RetVal = SysCmd(acSysCmdSetStatus, "File " & i & " of " & TotFiles & " copied...")

with

Application.StatusBar ="File " & i & " of " & TotFiles & " copied..."

Yes. Also the line...

RetVal = SysCmd(acSysCmdClearStatus)

...which clears the status bar, with this....

Application.StatusBar = ""

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

I tried to comment the lines with SysCmd but I've no luck in getting the code to work (upload to sharepoint). I'm running it as ActiveX control button. What do you think is the best way to run this code from Excel?

A button is a good way to run the above code, although I would use a Forms button control instead of an ActiveX button. They are inserted into the sheet the same way, but are less prone to errors down the track.

Once you've added the button, an "Assign Macro" dialog will display allowing you to select the macro to run when the button is clicked.

I only get one alert window indicating 0

since you answer my question, i will give you the points and create a new one hopefully you can help me figure out what's missing. thanks a lot for your help.

Here's the follow up issue, since the SysCmd question is resolved..

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28637519.html

VBA Compile error: on SysCmd (Sub or Function not defined)

Who is Participating?