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_CopyDim xmlhttpDim sharepointUrlDim sharepointFileNameDim tsInDim sBodyDim LobjXML As ObjectDim UserName As StringDim pw As StringDim RetValDim i As IntegerDim TotFiles As IntegerDim Start As Date, Finish As Date'Dim fso As New FileSystemObject 'Scripting.FileSystemObjectDim fso As ObjectSet fso = CreateObject("Scripting.FileSystemObject")Dim fldr As Object 'FolderDim f As Object 'FileSet 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.CountFor 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 = Nothingerr_Copy:MsgBox Err & " " & Err.DescriptionIf Err <> 0 Then MsgBox Err & " " & Err.Description Resume NextEnd IfEnd Sub
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.
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.
a0k0a7
ASKER
Here's the follow up issue, since the SysCmd question is resolved..
RetVal = SysCmd(acSysCmdSetStatus, "File " & i & " of " & TotFiles & " copied...")
with
Application.StatusBar ="File " & i & " of " & TotFiles & " copied..."