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

LVL 8
a0k0a7Asked:
Who is Participating?
 
Wayne Taylor (webtubbs)Commented:
Yes. Also the line...

      RetVal = SysCmd(acSysCmdClearStatus)

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

    Application.StatusBar = ""
0
 
Wayne Taylor (webtubbs)Commented:
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
0
 
a0k0a7Author Commented:
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..."
0
Ultimate Tool Kit for Technology Solution Provider

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.

 
a0k0a7Author Commented:
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?
0
 
Wayne Taylor (webtubbs)Commented:
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.
0
 
a0k0a7Author Commented:
I only get one alert window indicating 0
alert window
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.
0
 
a0k0a7Author Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.