Jump to content
AnonymousX

[Solved] Copying files from Sharepoint to local directory

Recommended Posts

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 by AnonymousX

Share this post


Link to post
Share on other sites

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)

 

Share this post


Link to post
Share on other sites

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 by Nine

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-07-21 - Version 1.5.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

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 :D


All my code provided is Public Domain... but it may not work. ;) Use it, change it, break it, whatever you want.

Spoiler

My Humble Contributions:
Personal Function Documentation - A personal HelpFile for your functions
Acro.au3 UDF - Automating Acrobat Pro
ToDo Finder - Find #ToDo: lines in your scripts

Share this post


Link to post
Share on other sites

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 by AnonymousX

Share this post


Link to post
Share on other sites

@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

 

Share this post


Link to post
Share on other sites

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)


All my code provided is Public Domain... but it may not work. ;) Use it, change it, break it, whatever you want.

Spoiler

My Humble Contributions:
Personal Function Documentation - A personal HelpFile for your functions
Acro.au3 UDF - Automating Acrobat Pro
ToDo Finder - Find #ToDo: lines in your scripts

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By AlMax3000
      You can make a program or solution that copies all files of a certain extension, example  > .rar (only rar),  and copies them to a directory
       
       
      too simple I solved 🙂


    • By Subz
      Backstory:
      Our Microsoft Office Templates shared folder was changed from a DFS share to an Isilon share. example:
      Old Server: \\Domain.com\Office\Templates
      New Server: \\Templates.domain.com\Office\Templates
      The team making the changes overlooked that several hundred thousand documents, had been attached to the old template documents.  So when you open a document which has been attached, it will take a couple of minutes to open, while it tries to locate the old server path.  I've been asked to come in and fix it, so after several hours found that the data is being held in document.zip\word\_rels\settings.xml.rels, I now need to replace the old server path with the new server path.  I didn't want to use dom as that would take too long and found a tool wtc https://github.com/NeosIT/wtc which  works perfectly, takes about 8 minutes to scan a single directory with 4000 documents and fix them.  The problem is the documents are all held on sharepoint and they want to retain the file timestamp, which is easy enough, but they also don't want to keep the "Modified By" apparently they don't like seeing all the documents appearing as "Modified by: Subz"  Anyone know of way to retain the "Modified By" info,
    • By hugomito
      Hi all,
      I hope you can help me with this need. In the company I work for, every time we need to deploy a change into a Production environment, we need to add a SharePoint entry in a Calendar area. This is in order to notify Operations team that a change will be introduced on a specific date.
      The fields we need to fill out are:
      ows_Project ows_ChangeReq ows_Description ows_StartDate ows_EndDate The thing is that, I have all these values available in an AutoIT application I built called EAI Tool so, what I need is: Whenever a user click a button, a SharePoint record should be created populating SharePoint fields with the values from EAI Tool applicacion.
      If you need additional information, let me know.
      Regards,
    • By boy233
      I wanted to know if you have UDF for Sharepoint ?! An example?
    • By JonF
      I have a server running Small Busines Server 2008.  On that server is a shared directory containing Microsoft Office templates in the normal directory setup; a bunch of templates, a Smart Art subdirectory, and a Document Themes subdirectory containing Theme Colors, Theme Effects, and Theme Fonts subdirectories.
      My task is to create a program that copies all that to the local user's templates subdirectory.  It must work on the local network or over the VPN.  I am not allowed to wipe the local template directory. I may not assume that any drives are mapped, I have to use UNC paths to the server. So a .cmd file doesn't work because it doesn't do UNC paths, at least copy and xcopy and robocopy don't.
      This morning I sat down with AutoIt and came up with:
       
      #include <File.au3> #include <Array.au3> #include <FileConstants.au3> $LocalPath = EnvGet("APPDATA") & "\Microsoft\Templates\" $Result = DirCopy("\\192.168.7.250\Users\<redacted>\<redacted>\<redacted>\<redacted> Templates 2",$LocalPath,$FC_OVERWRITE) MsgBox(0,"Info",$Result) Works on the internal network. Works when I connect to our guest network (which is a different subnet) and connect to the (SoftEther) VPN.  Does not work when connected to the VPN on physically remote machines.
      ?????
       
×
×
  • Create New...