[Solved] Copying files from Sharepoint to local directory

[AnonymousX]

Hello,

I'm hoping to create a way of copying and renaming a specific file off of a company Sharepoint site.

For local files I've always used the method of using FileExists( "path") then FileCopy ( "source", "dest" [, flag = 0] )

#include <WinAPIFiles.au3>

Copy_File()

Func Copy_File()

local $source = "C:\Users\auser\Documents\test.xls"
Local $dest = "C:\Users\auser\Documents\test"
Local $iFileExists = FileExists($source)

    If $iFileExists Then
        FileCopy($source,$dest);copy file to new location
        MsgBox($MB_SYSTEMMODAL, "", "File was copied")
    Else
        MsgBox($MB_SYSTEMMODAL, "", "File doesn't exist")
    EndIf

EndFunc

However with the file location provided by sharepoint, it seems autoIt isn't able to find it. File path provided by sharepoint looks something like this:

https://workspace.company.com/Folder/Folder%20B/File%20Name.xls

I know if I have excel open and paste the link into the excel file name open box, it will open the file just fine. Also I know I can create shortcuts to these links, and when I click on them it will open the file just fine too. So I'm not sure how I have to refer to these files for AutoIT to recognize it and copy it to the folder location I want.

I don't really have a good understanding on how this stuff works, but I was hoping the solution wasn't too complicated, and could use some help.

Any help is appreciated, thanks in advance.

Edited November 12, 2019 by AnonymousX

[AnonymousX]

I also tried to making a batch file instead since I couldn't get Autoit to work, but wasn't able to get that to work either.

Not working code:

@echo off

set /a flag = 0

echo Locating Folder path of files

set Sourcedir="https://company.com/folder/file%20name.xls"
echo Source = %Sourcedir%

set Destinationdir="%cd%\testing.xls"
echo Destination = %Destinationdir%


if not exist %Sourcedir% set /a flag=1 :if directory doesn't exist set flag

echo Attempting to Copy
if not "%flag%"==1 (copy %Sourcedir% %Destinationdir%  )
if "%flag%"==1 (echo. & echo Error: Source Location Not Found & echo. & echo Program Cancelled)
if not "%flag%"==1 (echo. & echo Copy Complete)

 

[Nine]

Normal Windows network path has the format like \\DOMAIN\Application\Folder\Filename.xls

But you want to use something like http://... you should use FTP* or TCP* or InetGet to copy files. 

Edited November 9, 2019 by Nine

[water]

To open an Excel workbook from Sharepoint you can use the latest Excel UDF. If needed you will find a fix for the FileExist problem here.

[seadoggie01]

I've done this using a mapped drive before... here's the steps I use

1. Open Internet Explorer to your sharepoint site
2. Navigate to the "Documents" folder
3. In the view options, click "View in File Explorer" (Other browsers don't have this option. I haven't explored what this does in the background either, but if you know Javascript, please tell me what you find!)
4. Map this directory as a network drive (I like S:\ so I can remember SharePoint)
5. Copy and paste as you like

I'm not sure if it's my permissions here at work or not, but the mapped drive doesn't stay mapped, so I repeat this process whenever I need to copy paste in. Also note that this won't allow you to set custom properties on your documents. It does, however, seem to overwrite documents and maintain their custom properties if they were previously added.

I think at one point I mapped a drive using "@SSL " somewhere in the path, but I failed to document or remember it. It may be helpful to someone who understands internet tech better

[AnonymousX]

Thanks guys for the comments, but sadly wasn't able to get anything to work. I'm pretty beginner to coding so I feel like this may just be one over my head. If it helps I'm just needing to copy a file from sharepoint locally, it never needs to go the other direction. 

 

@Nine  I tried using the InetGet function but wasn't able to get it to work, I'll have to do some more reading on it. I gave up a little quick as I was hoping I could get something working with Water's suggestion.

 

@water I tried using your _Excel_BookOpenEX found in the link you posted but I wasn't able to get it to work either. I just tried opening the file but nothing happened, no errors were created.  

#include <Excel.au3>
#include <File.au3>
#include <Array.au3>
#include <WinAPIFiles.au3>

Local $oExcel =_Excel_Open(true)
$datawb = _Excel_BookOpenEX($oExcel,"https://workspace.company.com/folder/File%20name.xlsm")

; #FUNCTION# ====================================================================================================================
; Author ........: SEO <locodarwin at yahoo dot com>
; Modified.......: litlmike, water, GMK, willichan
; ===============================================================================================================================
Func _Excel_BookOpenEX($oExcel, $sFilePath, $bReadOnly = Default, $bVisible = Default, $sPassword = Default, $sWritePassword = Default, $bUpdateLinks = Default)
    ; Error handler, automatic cleanup at end of function
    Local $oError = ObjEvent("AutoIt.Error", "__Excel_COMErrFunc")
    #forceref $oError
    If Not IsObj($oExcel) Or ObjName($oExcel, 1) <> "_Application" Then Return SetError(1, @error, 0)
    If StringLeft($sFilePath, "HTTP") = 0 And Not FileExists($sFilePath) Then Return SetError(2, 0, 0) ; <== Modified
    If $bReadOnly = Default Then $bReadOnly = False
    If $bVisible = Default Then $bVisible = True
    Local $oWorkbook = $oExcel.Workbooks.Open($sFilePath, $bUpdateLinks, $bReadOnly, Default, $sPassword, $sWritePassword)
    If @error Then Return SetError(3, @error, 0)
    Local $oWindow = $oExcel.Windows($oWorkbook.Name)
    If IsObj($oWindow) Then $oWindow.Visible = $bVisible
    ; If a read-write workbook was opened read-only then set @extended = 1
    If $bReadOnly = False And $oWorkbook.Readonly = True Then Return SetError(0, 1, $oWorkbook)
    Return $oWorkbook
EndFunc   ;==>_Excel_BookOpenEX

 

@seadoggie01 Thanks for the alternative suggestion, but that isn't going to work for my purposes.

 

 

 

 

I've been thinking about it and I think Nine, you have the right idea. I was able to open the file by using:

 

Local $oIE = _IECreate(URL)

So I'm just going to play around with that InetGet.

Edited November 12, 2019 by AnonymousX

[AnonymousX]

@Nine Thanks mate!

I kept trying with the advice to look at InetGet and got it to work. 

Here is the code for anyone in the future:

 

#include <InetConstants.au3>
#include <MsgBoxConstants.au3>
#include <WinAPIFiles.au3>

; Download a file in the background.
; Wait for the download to complete.

DownloadfromSP()

Func DownloadfromSP()
    ; Save the downloaded file from website
    Local $sFilePath = @ScriptDir & "\SP_Downloaded_File.xlsm"

    ; Download the file in the background with the selected option of 'force a reload from the remote site.'
    Local $hDownload = InetGet("https://workspace.company.com/folder/File%20name.xlsm", $sFilePath, $INET_FORCERELOAD, $INET_DOWNLOADBACKGROUND)

    ; Wait for the download to complete by monitoring when the 2nd index value of InetGetInfo returns True.
    Do
        Sleep(250)
    Until InetGetInfo($hDownload, $INET_DOWNLOADCOMPLETE)

    ; Retrieve the number of total bytes received and the filesize.
    Local $iBytesSize = InetGetInfo($hDownload, $INET_DOWNLOADREAD)
    Local $iFileSize = FileGetSize($sFilePath)

    ; Close the handle returned by InetGet.
    InetClose($hDownload)

EndFunc

 

[Nine]

[seadoggie01]

I found where I managed to use FileCopy with our SharePoint site (for anyone who finds this later). I'm working in VBA, but the same principles apply. 

My company SharePoint homepage is: <CompanyName>.sharepoint.com
My department's (sub) page is:               <CompanyName>.sharepoint.com/system/<dept>
The Documents directory is:                    <CompanyName>.sharepoint.com/system/<dept>/Documents/Forms/Standard View.aspx
When copying I use:                                \\<CompanyName>.sharepoint.com@SSL\system\<dept>\Documents\*

FileCopy never has had an issue with this (assuming I pass a correct filename)