Share workbook on web

[samirshah172]

hello,

i am need to share a workbook on web with group of user in my team. the user will access the workbook through a workbook from local drive with vba code.

i am very much new for sharing on local network as well on web. i want to know tht where can i share (on which website) and how much my cost will be for uploading downloadin data per kb/mb. is there any free option ? and ofcourse the code sample to import/export data on Range basis.

thanks in advance

Samir

 

[Ruddles]

There are many companies offering free Web hosting - those are the words to Google.

Regarding the VBA code, that will have to be written from scratch. I don't think a 'sample' will help you here - it will have to be designed with your particular requirements in mind.

When you say the workbook will be shared, do you mean for read-only access or do you intend that multiple people will be updating it concurrently?

 

[samirshah172]

Dear Ruddles,

Sorry for delay in reply,

Want to give user read & Write access concurrently but no user can open direct from server. the Data file on server should be excessd via workbook on local drive by VBA code only.

by sample code, my mean is code to open the file from server, update data( get & send) & save the updated file on server.

Thanks a lot for reply

Regards

Samir

 

[Ruddles]

In that case you're going to have to give very careful consideration about how you're going to resolve situations where two people attempt to update the same data at the same time.

As a general rule when designing this sort of system I would advise you to assume the worst scenario and make sure your design is robust enough to cope with that, because one day it will happen.

 

[samirshah172]

yes you are very right, this will surely gonna happen, i hv code tht wait for few seconds if a file is open by other user on local network. is thr a way out.

i highly appriciate your help

warm regards

Samir

 

[asyamonique]

yes you are very right, this will surely gonna happen, i hv code tht wait for few seconds if a file is open by other user on local network. is thr a way out.

i highly appriciate your help

warm regards

Samir

Samir,
Can u pls. provide us the code u'd mentioned?
Cheers

 

[samirshah172]

i have this two function copied from this forum or may be from ozgrid.com

Function IsFileOpen(strFullPathFileName As String) As Boolean
' Code to check if File is Open
' We can use this for ANY FILE not just Excel!
Dim hdlFile As Long
    'Error is generated if you try
    'Opening a File for ReadWrite lock >> MUST BE OPEN!
    On Error GoTo FileIsOpen:
    hdlFile = FreeFile
    Open strFullPathFileName For Random Access Read Write Lock Read Write As hdlFile
    IsFileOpen = False
    Close hdlFile
    Exit Function
FileIsOpen:
    'Someone has it open!
    IsFileOpen = True
    Close hdlFile
End Function
Function LastUser(strPath As String) As String
'// This routine gets the Username of the File In Use
'// : Name changes will show old setting
'// : you need to get the Len of the Name stored just before
'// : the double Padded Nullstrings
Dim strXl As String
Dim strFlag1 As String, strflag2 As String
Dim i As Integer, j As Integer
Dim hdlFile As Long
Dim lNameLen As Byte

strFlag1 = Chr(0) & Chr(0)
strflag2 = Chr(32) & Chr(32)
hdlFile = FreeFile
Open strPath For Binary As #hdlFile
    strXl = Space(LOF(hdlFile))
    Get 1, , strXl
Close #hdlFile
j = InStr(1, strXl, strflag2)
#If Not VBA6 Then
    '// Xl97
    For i = j - 1 To 1 Step -1
        If Mid(strXl, i, 1) = Chr(0) Then Exit For
    Next
    i = i + 1
#Else
    '// Xl2000+
    i = InStrRev(strXl, strFlag1, j) + Len(strFlag1)
#End If
'// IFM
lNameLen = Asc(Mid(strXl, i - 3, 1))
LastUser = Mid(strXl, i, lNameLen)
End Function

and in my Sub i hv code

Retry:
        If IsFileOpen(strFileToOpen) Then
            With MyMsgBox 'is a userform
                .Show
                .Label1.Caption = strFileToOpen & " is already open by " & LastUser(strFileToOpen)
            End With
            Application.Wait (Now + TimeValue("0:00:3"))
            Unload MyMsgBox
            GoTo Retry
        Else
'other code if file is not open

 

[Ruddles]

i hv code tht wait for few seconds if a file is open by other user on local network.

As far as I'm aware this sort of approach won't work with a file on a Web server - this will only work for a file which is accessible via a mapped network drive or using its UNC name. A file on a Web server will have to opened via http: or ftp: and then replaced using ftp:.

If your solution is completely dependent on making the file available via the Web, I suggest you start by signing up with one of the many free Web hosting services and upload a small sample file which you can test your code against.

Also, although I don't know much about SharePoint, you might investigate whether that has the functionality you're looking for.

 

[samirshah172]

Dear Ruddles

Thanks for your precious time, i really appriciate if you help me to provide code for Copy a file from local drive to any one web server suppose Google, than to open it and add single data entry in sheet1, cell A1 and than save & Close

Thanks

Regards

Samir

 

[Ruddles]

I've never done it, so I'm afraid i can't provide you with any code. I'm basing my comments on my knowledge of Web hosting services and the FTP protocol, and some experience of automating FTP access to Web servers using MS Access.

You would have to download the file to your local hard disk and open if from there, then make the changes and upload it back. The uploading would have to be via FTP as I'm not aware that Web hosting services provide any other methods which can be automated.

Perhaps you could have a go at setting up some Web space and putting a workbook on there and then performing the various steps manually before you try to automate them, in order to acquire some experience of what's involved.