Vba to Download file from Site.

[pedie]

How do i make VBA to open workbook in Site that looks like
http://www.mrexcel.com/forum/showthread.php?t=544379/myDATA.xls

Is there a way to upload too in site?
Click on new > click on upload > browes for workbook & click ok [this is how it is manually done...]

Please advice.

 

[Colin Legg]

Hi Pedie,

You can't attach files here.

To get the "screenshot" of the worksheet, I think you have 4 main choices:

1. MrExcel board html maker by Richard Schollar
2. html maker by Colo and Ivan Moala
3. Excel Jeanie
4. Table-it by Eric Van Geit

I use Richard's one and I think it's pretty good. You can also modify it if you want to.

 

[pedie]

I think i explained it in a wrong way...
I am wanting to see if there is way to download or upload file to Sharepoint through VBA...


Thanks again Colin!
Pedie

 

[MikeDBMan]

Pedie, I too am looking to learn how to do this. Please let me know if you have figured out a way to do it. I will do the same!

 

[Colin Legg]

I think i explained it in a wrong way...
I am wanting to see if there is way to download or upload file to Sharepoint through VBA...


Thanks again Colin!
Pedie

Oh I see!

Yes, it should be quite easy. For example, saving a workbook to Sharepoint should just be a case of using Workbook.SaveAs

http://msdn.microsoft.com/en-us/library/aa159897(v=office.11).aspx

To save a document to a SharePoint site, you can use the Save method from the object model of the application, together with a properly constructed URL. For example, to add the first open Excel workbook to a SharePoint site on a server execute this line of code:


Application.Workbooks(1).SaveAs _ "http://server_name/sites/site_name/Shared%20Documents/file_name.xls"

</PRE>


</PRE>

 

[MikeDBMan]

You would also want to issue this to check the file in:

ActiveWorkbook.CheckInWithVersion

This would unload it and check it in on SharePoint so you would not manually have to do that also.

I can't figure out how to write code to check a file out though. Any suggestions?

 

[Colin Legg]

It's on the link I posted. The Workbooks class has a CheckOut method:

You can also check documents in and out from VBA code inside of a document provided that the document was opened from a document library. Because of the exclusive check-out model, you should always check to make sure that it is safe to perform these operations before actually performing them. To check out an Excel workbook, use this code example:

If Workbooks.CanCheckOut("PartSales.xls") = True Then
    Workbooks.CheckOut docCheckOut
Else
    MsgBox "This document cannot be checked out."
End If

 

[MikeDBMan]

In your example when I try to use it, the docCheckOut gets highlighted I get the message, "Compile Error, variable not found".

I saw other code in one of your samples:

Workbooks.CheckOut(Filename:=docCheckOut)

but that line of code does not compile either. Does the docCheckOut need to be the full path and file name of the file trying to check out, perhaps? I am going to test that.

 

[Colin Legg]

They're not my examples.

docCheckOut is a String variable which holds the filename of the document; if it's not declared and you are (quite correctly) using Option Explicit then you'll get that error.

 

[MikeDBMan]

I got this to work. To check out a file:

First this line of code before opening the file:

        Let OKOut = UseCanCheckOut(ThePath & TheFile2)
        If OKOut = 1 Then
            On Error Resume Next
            Application.DisplayAlerts = False
            Workbooks.Open FileName:=ThePath & TheFile2
       end if

Function UseCanCheckOut(docCheckOut As String)
    If Workbooks.CanCheckOut(FileName:=docCheckOut) = True Then
        Workbooks.CheckOut docCheckOut
        UseCanCheckOut = 1
    Else
        'MsgBox "You are unable to check out this document at this time."
        UseCanCheckOut = 0
    End If
End Function