asked on

Updating MS Excell File on a Sharepoint site using VB.net

Hello,

I'm writing an app in VB.net to use an excel file as a database. Right now I have the file local on my PC but want to be able to access the file via SharePoint. When I navigate via web to the excel file (example https://Sharepoint.com/sites/MYsite/documents/Database.xlsx) I need to enter my user name and password. The code I have is:

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        Dim Conn As New OleDbConnection
        Dim Comm As New OleDbCommand
        Conn.ConnectionString = CreateConnString("c:\temp\Book1.xls")
        Comm.Connection = Conn
        Comm.CommandText = "Insert INTO [MySheet$] (F1, F2) Values ('1','A')"
        Try
            Conn.Open()
            Comm.ExecuteNonQuery()
            MsgBox("Inserted.")
            Conn.Close()
        Catch ex As Exception
            MsgBox("Error.")
            Conn.Close()
        End Try
    End Sub

Open in new window

Can someone tell me how VB.net can access this document on my SharePoint site using a user name and password?

ASKER CERTIFIED SOLUTION

unknown_routine

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ryan Smith

ASKER

No I'm not able to access the file using windows exporer.. I'm running this on Windows 7.. Should that matter?

unknown_routine

OK so first step:

Contact with admin of sharepoint:

You should have access to

\\Sharepoint.com\sites\MYsite\documents\

On Windows explorer on any windows XP, vista, 7, etc

Ryan Smith

ASKER

Ok i got it to work with "\\Sharepoint.com\sites\MYsite\documents\" But I'm getting an error in my app because i need to use a user name and password. Where do i put this in my code??

I have a function at the top:

    Private Function CreateConnString(ByVal Str As String) As String

        Return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Str + ";Extended Properties=""Excel 8.0;HDR=YES;"""

    End Function

Open in new window

Do i put it here? How?

Nasir Razzaq

I think the proper approach would be to check out the file from Sharepoint so that others don't modify it while you are working with it, make changes to it and then check it in. You will have to use Sharepoint Client Object Model for this. Are you willing to go that route?

Ryan Smith

ASKER

The application I'm creating will be used by multi users at one time. So are you telling me that only one instance of the application can be ran at one time?

Nasir Razzaq

Files in sharepoint have to be checked out in order to be modified and only one user can check out a file at a time.

Rainer Jeschor

Hi,

username / password: as you have to enter the user/pass when using Internet Explorer, you should check, if your SharePoint root url is configured to be in the "Local Intranet Zone". Normally (and if you run on a domain joined machine) you will no longer be asked for a password.

Check-out (@CodeCruiser): this is NOT true. That depends on the SharePoint list configuration. As long as the list owner did not activate "Force check-out", everyone can easily upload and overwrite existing versions of the file

Which SharePoint version and edition is in use?
Why do you want to use Excel as a database - especially in the case of multiple user making adjustments?
What is your business case?
IMHO Excel is the worst place to be used as a database. Perhaps you might think of using a SharePoint list or a real database for handling your business requirements.

HTH
Rainer

Nasir Razzaq

If the user is not required to checkout, then file would be overwritten by multiple users using it at the same time.

Ryan Smith

ASKER

@Rainer the application I’m creating is for time management. the reason I’m using SharePoint is because the database have to be online and accessible by everyone who is running the application. The version of SharePoint is Windows SharePoint services 2.0.

I was able to get my code to work by using the url \\Sharepoint.com\sites\MYsite\documents\. so that’s a good thing. My only concern now is will multiple people be able to edit/view this database though by vb.net application. Is there another databse you recommend? I know VB.net has a lot of functions with MS Access. But again.. the database needs to be stored online somewhere.

Nasir Razzaq

Do you have SQL server installed on any of your servers?