My company recently started using SharePoint with office 365, and I would like to be able to use the copy file activity in automation to put files into document libraries. I have attempted to create a SharePoint data connection in TDP4.0 but this does not seem to be working for SharePoint’s cloud app. I also attempted to paste the URL into the copy file activity, but it is not supported. Is this only possible with SharePoint server on premise? Does anyone have any suggestions?
I was able to get this to work - instead of mapping the SharePoint site as a website, I mapped it to a network drive - in this case Z:
I added Z:\Shared Documents to the copy to location and ran the script. The document was placed in the drive and shows up in my document library. I really thought I had tried this before and it didn’t work. I’ll have to see if I am able to connect to the drive again when I restart.
***Update - when I restarted, the mapped drive was gone. See my reply below for solution that worked.
I attempted a couple things to try to resolve. First I mapped the sharepoint document library to drive S:, then I ran my automation and the file successfully copied, however, the drive does not reconnect at startup even if the option is checked. Second, I mapped the drive using the connect to a website feature. The drive reconnects at startup, but TDP does not copy the file into the C:\Users[username]\AppData\Roaming\Microsoft\Windows\Network Shortcuts[folder name] folder even though the automation script completes successfully. When I drag and drop the file into this folder it works. Why would the automation script not copy the file when simple drag and drop works?
What did eventually work was placing “\” in front of the sharepoint site url:
\[server]@SSL\DavWWWRoot\sites\NA\sc\Shared Documents
Note that the URL was reformatted as if it were an actual drive with \ instead of /
I noticed this path in the comments section of the properties option when I used the map website option under map network drive.
My organization isn’t currently using SharePoint 365, but I’ve been able to successfully upload files to SharePoint by using a file Save As macro that uploads to SharePoint. If you can manually save a Excel workbook to your SharePoint location, then this method has a good chance of working for you. I recommend using the Export widget to trigger a macro that does the SharePoint upload for you at the end of your automation. Here is sample of the macro that you’ll need to get you started.
Dim PreviousMonth As String
Dim Currentdate As String
Dim ServerLocation As String
Dim Extension As String
Dim POLine As String
Currentdate = Now
PreviousMonth = Month(DateAdd(“m”, -1, Currentdate))
PreviousMonthYear = Year(DateAdd(“m”, -1, Currentdate))
ServerLocation = “sssteams.providence.org/…/OSCM IS Reports/”
Extension = “.xlsm”
POLine = “PO_Line_Data”
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
ServerLocation & POLine & PreviousMonth & PreviousMonthYear & Extension, _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
I hope this helps
Hi - I was just able to export directly to a sharepoint library using the path that I constructed in my reply above. It seems like there is the ability within TDP to load files into sharepoint without using VBA.