I have searched this and other forums, my books, and the Microsoft website. I am using Microsoft Access 2002 SP1 and am trying to copy a .xls file from a internal network drive to a Sharepoint Site. I have setup "my network places" to show the applicable Sharepoint Site and Folder, and can move the file manually but when I try to use the following code, I get a "Path not found!" error.

Call FileCopy("R:\Access\Development\Gina\Automated\STARSprositUp date\DocumentsMissingStatus.xls", "http://collaboration.organon.intra/sites/GCORM_Business_Analysis/Sample BARP Process Site/SCBM STARS Test/DocumentsMissingStatus.xls")

I have tried all different syntax and can't see to get it to work. Is this even possible, or is there a better method?

Any help would be greatly appreciated...
Thank you,
Gina

To my knowledge you cannot use a FileCopy command to place a file on Sharepoint. FileCopy requires a fixed drive or UNC path to the folder, because Sharepoint is web based you cannot simply copy a file to a URL, it needs to be uploaded, I would guess the Windows OS is handling this for you when you are using your My Network Places.

You would probably need to look at web API's to upload a file to a URL.

Thanks for your guidance, I will pursue the web api method.

I did get this to work: see code:
You have to setup the Sharepoint folder in your "Network Places" first.

Call FileCopy("R:\GMAO-ACCESS\Development\Gina\Automated\
COMETcontractWarningEmailRpts\StatusOfCNS.xls", "\\teamsite.merck.com\external\VSM\shared documents\Invoices\statusOfCNS.xls")

However on the Sharepoint side I am now having an issue with checking in the document.

Gina:

FYI - in case if you don't want to rely on a mapped network drive, you should be able to use HTTP URL as the path directly to put your file in there.

As for checking in/out, I would venture to guess you'd need to:

1) invoke a SharePoint web service

CheckInFile (http://msdn.microsoft.com/en-us/library/websvclists.lists.checkinfile.aspx)
CheckOutFile (http://msdn.microsoft.com/en-us/library/websvclists.lists.checkoutfile.aspx)

2) Using web browser and send your user to the Document Library and have your user check in/out manually

3) Create a workflow that automatically checks in the file that's added to the Document Library.

HTH.

Thanks Banana - Finally got it to work using your advice.

Love this forum...

Glad it helped!

Hi Gina,

Is it possible for you to post your code here?

I need the same in Excel VBA, which I think I could pick some idea from your code.

Thanks in advance.

Here are instructions that I drafted for this process:

Go to Microsoft Downloads and install the "Office Web Toolkit" on the target machine.

Open MS Access and Create a new Module or open a module that contains your local code.

Click on the "Tools" Menu.

Click on the "Web Service Reference"

Click the button (bottom left) titled "Web Service URL"

Type http://teamsite.xxx.com/_vti_bin/lists.asmx <--- (_vti_bin)

The search results (box at top right) should return:
[ ] Lists

Check the "Lists" box and click the "Add" button.

A class module will be created and titled "CLSWS_LISTS", save this module.


Within your own module, use the following code to post to Sharepoint:

'output your query or report from the database that needs to be posted
Call DoCmd.OutputTo(acOutputQuery, "Export Voucher Invoices", acFormatXLS, "\\servername\folderpath\filename.xls")

'you must delete the previous file on Sharepoint so you can post a new one
Kill ("\\teamsite.xxx.com\external\folderpath\filename.xl s")

'copy the new file up to the teamsite
Call FileCopy("\\servername\folderpath\FROMfilename.xls", "\\teamsite.xxx.com\teamsitepath\folderpath\filenam e.xls")

'SHAREPOINT CHECK DOCUMENT IN PROCESS – uses the class module that was created.
'variable declaration
Dim listService As New clsws_Lists
Dim myresults As Boolean
Dim UploadInformation As String

'this is a OPTIONAL comment that is added as a property of the document in Sharepoint
UploadInformation = "Data Imported from xxx on:" & Now()

'this code CHECKS IN the document on Sharepoint
myresults = listService.wsm_CheckInFile("https://teamsite.xxx.com/teamsitepathl/folderpath/filename.xls", UploadInformation, "1")

Thanks Gina. As I mentioned, my "project" is in Excel and so the instruction you posted may not entirely apply. But it sure contains something I can work on to achieve the results.

Thanks again.