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

Yes. Also the line...

RetVal = SysCmd(acSysCmdClearStatus)

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

Application.StatusBar = ""

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

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

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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.

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

Who is Participating?

Already a member? Login.