EXCEL VBA SERIES – POST – 10 – Automatic file upload to SharePoint url using Excel VBA

Excel vba - Sharepoint upload
Spread the love

 

 

EXCEL VBA SERIES – POST – 10 – [Automatic file upload to SharePoint url using Excel VBA ]:

In this blog post, we will see how to do an automatic upload of any file ( word, excel, pdf, image, text ) to the desired sharepoint url. This blog post is for the users who want to upload a specific file to the desired sharepoint location on a defined interval and for those who have access only to the web front end and don’t have the option to map the sharepoint location as a share drive.

So lets see on how to upload it via weburl front end.

Here we will use excel vba’s runtime environment to read the source file in a binary format and push the content to the specified sharepoint url location. We will also learn on how to invoke the excel vba macro from outside the excel environment using powershell.

Create the below macro in the excel vba editor and name the file as​​ 

uploadFile.xlsm

Sub uploadFileMacro()

 

 ​​ ​​ ​​​​ 'When there is error it will skip the rest of the code

 ​​ ​​ ​​​​ On Error GoTo Error_status

 ​​ ​​ ​​​​ 

 ​​ ​​ ​​​​ ' Sharepoint url to which you need to upload the file

 ​​ ​​ ​​​​ Dim sharepointUrl

 ​​ ​​ ​​​​ 

 ​​ ​​​​ ​​ ' Initialize the xmlhttp object

 ​​ ​​ ​​​​ Dim LobjXML As Object

 ​​ ​​ ​​​​ Set LobjXML = CreateObject("Microsoft.XMLHTTP")

 ​​ ​​ ​​​​ 

 ​​ ​​​​ ​​ ' To read source files as byte

 ​​ ​​ ​​​​ Dim binaryByte() As Byte

 ​​ ​​ ​​​​ Dim binaryByteData As Variant

 ​​ ​​ ​​​​ 

 ​​ ​​ ​​​​ 'Get sourcefilelength for binarybyte initialization.

 ​​ ​​ ​​​​ Dim sourceFileLength As Long

 ​​ ​​ ​​​​ 

 ​​ ​​ ​​​​ 'Get the source file along with the path

 ​​ ​​ ​​​​ Dim fileNamewithPath As String

 ​​ ​​ ​​​​ 

 ​​ ​​ ​​​​ 'Create the destination url

 ​​ ​​ ​​​​ Dim destinationUrl As String

 ​​ ​​ ​​​​ 

 ​​ ​​​​ ​​ 'Initilizate the scripting object for getting folder information.

 ​​ ​​ ​​​​ Dim objFSO As Object

 ​​ ​​ ​​​​ Set objFSO = CreateObject("Scripting.FileSystemObject")

 ​​ ​​ ​​​​ 

 ​​ ​​ ​​​​ 

 ​​ ​​ ​​​​ ' use userid and password only if it is required, most of them sharepoint links

 ​​ ​​ ​​​​ ' work on single sign on mode inside the organization, if connecting from outside

 ​​ ​​ ​​​​ ' the company's network then you might have to provide userid and password

 ​​ ​​ ​​​​ 

 ​​ ​​ ​​​​ ' User = InputBox("Username:")

 ​​ ​​ ​​​​ ' Password = InputBox("Password:")

 ​​ ​​ ​​​​ 

 ​​ ​​ ​​​​ ' Works both for http and https url

 ​​ ​​ ​​​​ sharepointUrl = "http or https url"

 ​​ ​​ ​​​​ 

 ​​ ​​ ​​​​ 

 ​​ ​​ ​​​​ 'If your documents are available in the same path with one direcory level up use this

 ​​ ​​ ​​​​ 'otherwise specify the location where your source file directory is

 ​​ ​​ ​​​​ excelPath = ThisWorkbook.Path

 ​​ ​​ ​​​​ sourceFolderPath = excelPath & "\..\Documents\"

 ​​ ​​ ​​​​ 

 ​​ ​​ ​​​​ 'Get the source folder

 ​​ ​​ ​​​​ Set sourceFolder = objFSO.GetFolder(sourceFolderPath)

 ​​ ​​​​ 

 ​​ ​​ ​​​​ 

 ​​ ​​ ​​​​ 'iterate through each of those files.

 ​​ ​​ ​​​​ For Each f In sourceFolder.Files

 ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ fileNamewithPath = sourceFolderPath & "\" & f.Name

 ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ 

 ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ 'Array length identification

 ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ sourceFileLength = FileLen(fileNamewithPath) - 1

 ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ 

 ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ 'Reinitialize the byte array

 ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ ReDim binaryByte(sourceFileLength)

 ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ 

 ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ 'Open file binary consumption

 ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ Open fileNamewithPath For Binary As #1

 ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ Get #1, , binaryByte

 ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ Close #1

 ​​ ​​ ​​​​ 

 ​​ ​​ ​​ ​​ ​​ ​​​​ ​​ ' Convert to variant to upload.

 ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ binaryByteData = binaryByte

 ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ 

 ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ 'Determine the destination url, make sure you have a "/" at the

 ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ 'end in sharepoint url

 ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ destinationUrl = sharepointUrl & f.Name

 ​​ ​​ ​​​​ 

 ​​ ​​ ​​ ​​ ​​ ​​​​ ​​ ' Upload the data to the server, false means synchronous.

 ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ ' LobjXML.Open "PUT", destinationUrl, False, User, Password

 ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ LobjXML.Open "PUT", destinationUrl, False

 ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ 

 ​​ ​​ ​​​​ 

 ​​ ​​ ​​ ​​ ​​ ​​​​ ' Send the file in.

 ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ LobjXML.Send binaryByteData

 ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ 

 ​​ ​​ ​​​​ Next f

 ​​ ​​ ​​​​ 

 ​​ ​​ ​​ ​​ ​​​​ Set LobjXML = Nothing

 ​​ ​​ ​​ ​​ ​​​​ Set objFSO = Nothing

 ​​ ​​ ​​​​ 

errhandler:

 ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ If Err.Number Then

 ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Error Uploading to SharePoint"

 ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ End If

 ​​ ​​ ​​​​ 

End Sub

 

Now we need to invoke this script in silent mode via command line from the powershell, so we will create the below powershell script and name it as​​ upload.ps1

 

$filepath = split-path $SCRIPT:MyInvocation.MyCommand.Path -parent

$filepathN = $filepath + '\uploadFile.xlsm'

$app = New-Object -comobject Excel.Application

$wb = $app.Workbooks.Open($filepathN)

$app.Run("uploadFileMacro")

$app.Quit()

 

Now we need to invoke the powershell script via a batch will , which will inturn invoke the macro programmed in the excel file which will in turn upload the desired file into the required target sharepoint library destination. Enter the below contents and name the file as​​ upload.bat

@echo off

Powershell.exe -executionpolicy remotesigned -File​​ upload.ps1

Pause

 

The code files are available in the below github link,

https://github.com/rajathithan/EXCEL-VBA

Thanks for viewing the post, please post your comments below,​​ 

Please Like and follow us on Facebook

https://www.facebook.com/gadoth

================================

================================

Related posts

Leave a Comment