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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

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
Wayne Taylor (webtubbs)Commented:
Yes. Also the line...

      RetVal = SysCmd(acSysCmdClearStatus)

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

    Application.StatusBar = ""
0

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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.