Avatar of a0k0a7
a0k0a7
Flag for United States of America asked on

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

Microsoft ExcelVisual Basic ClassicVB ScriptVBA

Avatar of undefined
Last Comment
a0k0a7

8/22/2022 - Mon
SOLUTION
Wayne Taylor (webtubbs)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
a0k0a7

ASKER
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..."
ASKER CERTIFIED SOLUTION
Wayne Taylor (webtubbs)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
a0k0a7

ASKER
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?
Wayne Taylor (webtubbs)

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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
a0k0a7

ASKER
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.
a0k0a7

ASKER