Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
vba to upload to sharepoint
1,427 views
Skip to first unread message
bobh
May 30, 2012, 8:54:06 AM5/30/12
to
Hi All,
Using Access2003 I extract some data from some tables and populate
some spreadsheet templates and save them on a server/folder. Now I'd
like to copy those spreadsheets to SharePoint. There are already prior
month copies of the spreadsheets on SharePoint so the folder structure
already is there.
I'd like to automate this process as there are many spreadsheets to
upload, anyone have vba that would do that?
thanks
bobh.
Using Access2003 I extract some data from some tables and populate
some spreadsheet templates and save them on a server/folder. Now I'd
like to copy those spreadsheets to SharePoint. There are already prior
month copies of the spreadsheets on SharePoint so the folder structure
already is there.
I'd like to automate this process as there are many spreadsheets to
upload, anyone have vba that would do that?
thanks
bobh.
Albert D. Kallal
Jun 2, 2012, 5:23:41 PM6/2/12
to
You can normally use standard copy commands.
Eg:
copy c:\test.txt \\ridessoftware.sharepoint.com\TeamSite\Documents\
Note in above how I changed the path name to standard DOS command syntax (\
back slash in place of the web forward slash).
Also, while looking at a document library in a web browser, from the ribbon
under "library tools" simply choose "library".
Then under "connect and export", simply choose "open with explorer". This
will result in the web folder being opened as a standard windows explorer on
your desktop. At that point you can use drag + drop from a folder on your
desktop (ctrl-a, select all documents. Then you simply ctrl-v to past the
documents into this web folder that now looks + feels + works like a
standard windows folder.
I not tested if the filecopy command inside of access works but you can use
DOS COPY or xcopy commands, and windows scripting via the file system object
should work just fine.
I think with above tips, you likely not need copy to copy all the documents,
but a drag+drop via the windows explorer would do the trick.
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
PleaseNoS...@msn.com
==
"bobh" wrote in message
news:22d503bf-9348-4d64...@v33g2000yqv.googlegroups.com...
bobh
Jun 4, 2012, 2:55:16 PM6/4/12
to
On Jun 2, 5:23 pm, "Albert D. Kallal" <PleaseNOSpamkal...@msn.com>
wrote:
using it. Since I'm uploading 20+ spreadsheets each one to a different
SP folder a have a loop and call this function and pass each
destination folder path and file name. I also found that I don't need
the user or pass which is why you see those lines commented out.
bobh.
Function WebUploadFile(file, url)
'Function WebUploadFile(file, url, user, pass)
' Written by Jeff Jones 3-30-2004. Pure freeware, please
redistribute.
'====================== WebDAV upload single file
'Use this function call to upload a single file
' WebUploadFile "C:\file.txt", "http://server/folder/file.txt", "domain
\user", "password"
Dim objXMLHTTP, objADOStream, arrbuffer
Set objADOStream = CreateObject("ADODB.Stream")
objADOStream.Open
objADOStream.Type = 1
objADOStream.LoadFromFile file
arrbuffer = objADOStream.Read()
Set objXMLHTTP = CreateObject("MSXML2.ServerXMLHTTP")
'objXMLHTTP.Open "PUT", url, False, user, pass
objXMLHTTP.Open "PUT", url, False
objXMLHTTP.Send arrbuffer
Set objADOStream = Nothing
Set objXMLHTTP = Nothing
End Function
wrote:
> You can normally use standard copy commands.
>
> Eg:
>
> copy c:\test.txt \\ridessoftware.sharepoint.com\TeamSite\Documents\
>
> Note in above how I changed the path name to standard DOS command syntax (\
> back slash in place of the web forward slash).
>
> Also, while looking at a document library in a web browser, from the ribbon
> under "library tools" simply choose "library".
>
> Then under "connect and export", simply choose "open with explorer". This
> will result in the web folder being opened as a standard windows explorer on
> your desktop. At that point you can use drag + drop from a folder on your
> desktop (ctrl-a, select all documents. Then you simply ctrl-v to past the
> documents into this web folder that now looks + feels + works like a
> standard windows folder.
>
> I not tested if the filecopy command inside of access works but you can use
> DOS COPY or xcopy commands, and windows scripting via the file system object
> should work just fine.
>
> I think with above tips, you likely not need copy to copy all the documents,
> but a drag+drop via the windows explorer would do the trick.
>
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> PleaseNoSpam_kal...@msn.com
>
> Eg:
>
> copy c:\test.txt \\ridessoftware.sharepoint.com\TeamSite\Documents\
>
> Note in above how I changed the path name to standard DOS command syntax (\
> back slash in place of the web forward slash).
>
> Also, while looking at a document library in a web browser, from the ribbon
> under "library tools" simply choose "library".
>
> Then under "connect and export", simply choose "open with explorer". This
> will result in the web folder being opened as a standard windows explorer on
> your desktop. At that point you can use drag + drop from a folder on your
> desktop (ctrl-a, select all documents. Then you simply ctrl-v to past the
> documents into this web folder that now looks + feels + works like a
> standard windows folder.
>
> I not tested if the filecopy command inside of access works but you can use
> DOS COPY or xcopy commands, and windows scripting via the file system object
> should work just fine.
>
> I think with above tips, you likely not need copy to copy all the documents,
> but a drag+drop via the windows explorer would do the trick.
>
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
>
> ==
>
> "bobh" wrote in message
>
> news:22d503bf-9348-4d64...@v33g2000yqv.googlegroups.com...
>
> Hi All,
>
> Using Access2003 I extract some data from some tables and populate
> some spreadsheet templates and save them on a server/folder. Now I'd
> like to copy those spreadsheets to SharePoint. There are already prior
> month copies of the spreadsheets on SharePoint so the folder structure
> already is there.
> I'd like to automate this process as there are many spreadsheets to
> upload, anyone have vba that would do that?
> thanks
> bobh.
Thanks for your reply, I found and tested this and it works so I'm
> ==
>
> "bobh" wrote in message
>
> news:22d503bf-9348-4d64...@v33g2000yqv.googlegroups.com...
>
> Hi All,
>
> Using Access2003 I extract some data from some tables and populate
> some spreadsheet templates and save them on a server/folder. Now I'd
> like to copy those spreadsheets to SharePoint. There are already prior
> month copies of the spreadsheets on SharePoint so the folder structure
> already is there.
> I'd like to automate this process as there are many spreadsheets to
> upload, anyone have vba that would do that?
> thanks
> bobh.
using it. Since I'm uploading 20+ spreadsheets each one to a different
SP folder a have a loop and call this function and pass each
destination folder path and file name. I also found that I don't need
the user or pass which is why you see those lines commented out.
bobh.
Function WebUploadFile(file, url)
'Function WebUploadFile(file, url, user, pass)
' Written by Jeff Jones 3-30-2004. Pure freeware, please
redistribute.
'====================== WebDAV upload single file
'Use this function call to upload a single file
' WebUploadFile "C:\file.txt", "http://server/folder/file.txt", "domain
\user", "password"
Dim objXMLHTTP, objADOStream, arrbuffer
Set objADOStream = CreateObject("ADODB.Stream")
objADOStream.Open
objADOStream.Type = 1
objADOStream.LoadFromFile file
arrbuffer = objADOStream.Read()
Set objXMLHTTP = CreateObject("MSXML2.ServerXMLHTTP")
'objXMLHTTP.Open "PUT", url, False, user, pass
objXMLHTTP.Open "PUT", url, False
objXMLHTTP.Send arrbuffer
Set objADOStream = Nothing
Set objXMLHTTP = Nothing
End Function
0 new messages