topic Re: How to Upload excel files to SharePoint (URL) by Macro in QlikView

How to Upload excel files to SharePoint (URL) by Macro

nihhalmca — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

How to Upload excel files to SharePoint (URL) by Macro.

Can you share the vb script plz.

Thanks,

Nihhal.

Re: How to Upload excel files to SharePoint (URL) by Macro

martynlloyd — Thu, 15 Dec 2016 14:55:24 GMT

Hi Nihhal

Use a normal save, you can get the UNC path by doing open in Explorer from the SharePoint library.

Kind regards,

Marty.

Re: How to Upload excel files to SharePoint (URL) by Macro

nihhalmca — Thu, 15 Dec 2016 14:59:17 GMT

Hi Martyn,

I tried and i can able to upload this way however i do not have access to UNC path from server (some security resons).

I can able to access SharePoint by url itself from server.

Could you tell one thing, is it possible?

Re: How to Upload excel files to SharePoint (URL) by Macro

Thu, 15 Dec 2016 15:10:43 GMT

HI Nihhal,

as option you could use standard tools like OneDrive which has synchronization with SharePoint, but in case of using SP 365 nothing helps you except specially designed services.

Re: How to Upload excel files to SharePoint (URL) by Macro

sajalgour2309 — Thu, 15 Dec 2016 15:24:48 GMT

Hello Nihhal,

I have a code which i used in excel vba to do this task, hope this will help.

Sub Copy_Jugal_Data()

Dim FSO As Object

Dim FromPath As String

Dim ToPath As String

On Error GoTo Jugal_Help

FromPath = "give excel path"

ToPath = "give share point path"

If Right(FromPath, 1) = "\" Then

FromPath = Left(FromPath, Len(FromPath) - 1)

End If

If Right(ToPath, 1) = "\" Then

ToPath = Left(ToPath, Len(ToPath) - 1)

End If

Set FSO = CreateObject("scripting.filesystemobject")

If FSO.FolderExists(FromPath) = False Then

MsgBox FromPath & " Path doesn't exist"

Exit Sub

End If

FSO.CopyFolder Source:=FromPath, Destination:=ToPath

MsgBox " Mr. Jugal you can find the files in " & ToPath

Jugal_Help:

Masgbox "Error"

End Sub

If you want to save any activeworkbook than you can use below code.

Sub Save_tosharepoint()

Dim FileName, FilePath As String

Let FilePath = "give share point path"

Let FileName = "give filename"

ActiveWorkbook.SaveAs FileName:=thepath & thefile _

, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

End Sub

Re: How to Upload excel files to SharePoint (URL) by Macro

nihhalmca — Thu, 15 Dec 2016 15:43:25 GMT

Hi Sajal - Thank you,

Its not working for me, i think its is VBA and i am looking for VBS.

Do you have VBS code or do you know how to convert from VBA to VBS.

Re: How to Upload excel files to SharePoint (URL) by Macro

martynlloyd — Thu, 15 Dec 2016 16:05:06 GMT

Hello Nihhal,

To upload to URL, you would need to use SOAP, or other low-level SharePoint code. Some 3rd party apps are out there, you could look at Bamboo Software for instance.

It seems unusual that you have write access to the SharePoint library, but not via Windows Explorer - have you tried a simple drag-and-drop into the folder, does that work?

Marty.

Re: How to Upload excel files to SharePoint (URL) by Macro

martynlloyd — Thu, 15 Dec 2016 16:08:01 GMT

from Microsoft

Key Differences Between Visual Basic for Applications and VBScript

File Operations

VBScript does not support any of the file operations found in Visual Basic for Applications, such as reading and writing to a file. Once again, this is because of the risks involved in being able to access the client machine without restriction. Clearly, allowing direct file I/O from the browser places the client machine at the mercy of any rogue executable that gets downloaded.

Marty.

Re: How to Upload excel files to SharePoint (URL) by Macro

martynlloyd — Thu, 15 Dec 2016 16:14:03 GMT

Hi Nihhal,

I checked several posts on Stack Overflow

I think it is not possible using VBS.

Regards,

Marty.

Re: How to Upload excel files to SharePoint (URL) by Macro

nihhalmca — Fri, 03 Mar 2017 08:31:33 GMT

Hi All, thanks for your help.

And I resolved issue by using UNC path instead of URL in macro code.