Excel remote updates


I have a requirement to create a Excel spreadsheet that will need to lookup cells and update cells in a remote master sheet. the sheet will be held locally on laptops and the master sheet is located on a server. (i will be adding further functionality later, ie emailing suppliers to order services etc)

i have done a basic layout and some formulas but i do not know how to link and update the master sheet.

Also is it better (easier for me) to use a vlookup for the information i require from the master sheet or would some VB code be quicker?

The is a unique referance (Site_ID)
Who is Participating?
Robberbaron (robr)Connect With a Mentor Commented:
this is code from an old VB6 app. ie very similar to VBA.

the excel file has named ranges that match the names like "proj_num" & "proj_name"

it is also possible to use cell addresses as well but i found using rangenames much easier to follow and especially to change the Excel file as didnt need to change the VB code.

    'open Excel file as DB
    'update data
   svname = "MyTest.xls"
On Error GoTo 0

    'Open the ADO connection to the Excel workbook
    Set oConn = New ADODB.Connection
    oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=" & svname & ";" & _
               "Extended Properties=""Excel 8.0;HDR=NO;"""
    'Add values to individual cells
    'oConn.Execute "Insert into First_Name (F1) Values ('Nancy')"
    'oConn.Execute "Insert into Last_Name (F1) Values ('Davolio')"
    'oConn.Execute "Insert into Title (F1) Values ('Sales Manager')"
    'oConn.Execute "Insert into Hire_Date (F1) Values (#11/1/00#)"
    'oConn.Execute "Insert into Comments (F1) Values ('This is a line of long text that will wrap in the cell.This is a line of long text that will wrap in the cell.')"

    ExcelSetRange oConn, "proj_num", job.pr_Id
            marker = 3
    LogAdd "PQP Pn1: " & job.proj_Name1 & "!"
    ExcelSetRange oConn, "proj_name", job.proj_Name1
        marker = 31
    LogAdd "PQP Pn2: " & job.proj_Name2 & "!"
    ExcelSetRange oConn, "proj_name2", job.proj_Name2
            marker = 4
    ExcelSetRange oConn, "proj_num", job.pr_Id
    ExcelSetRange oConn, "proj_date", Format(Now(), "dd mmm yyyy")
            marker = 5
    ExcelSetRange oConn, "proj_type", job.proj_Type
    ExcelSetRange oConn, "proj_addr", PCase(job.PStreet1)
            marker = 6
    ExcelSetRange oConn, "client_name", PCase(job.client_name)
    ExcelSetRange oConn, "client_attn", job.Iheader
            marker = 7
    ExcelSetRange oConn, "client_addr1", PCase(job.IStreet1) & " "
    ExcelSetRange oConn, "client_addr2", PCase(job.IStreet2) & " "
            marker = 8
    ExcelSetRange oConn, "client_addr3", PCase(job.ISuburb)
    ExcelSetRange oConn, "client_addr4", job.IState & " " & job.IPostcode
    ExcelSetRange oConn, "client_ref", job.client_ref
            marker = 9
    ExcelSetRange oConn, "client_contact", PCase(job.IContact)   'ExcelSetRange oConn, "fee_amt",job.proj_feevalue
    'ExcelSetRange oConn, "fee_type",job.proj_feetype
             marker = 10
    ExcelSetRange oConn, "proj_dir", NameConvert(job.proj_director)
    ExcelSetRange oConn, "proj_leader", NameConvert(job.proj_leader)
            marker = 11
    ExcelSetRange oConn, "contact_user", NameConvert(job.user)
            marker = 14
    'wb.BuiltinDocumentProperties.Item("Title") = job.pr_id
    'wb.BuiltinDocumentProperties.Item("Subject") = job.proj_name1 & "," & job.proj_name2
    'wb.BuiltinDocumentProperties.Item("Keywords") = job.pr_id & "," & job.proj_name1 & "," & job.proj_name2
    'wb.BuiltinDocumentProperties.Item("Author") = job.user
    'wb.BuiltinDocumentProperties.Item("Keywords") = job.proj_leader

    'Close the connection
Sub ExcelSetRange(oConn As Connection, RangeName As String, RngData As Variant)
    Dim sqlQ As String, vData As Variant
    Dim oRS As ADODB.Recordset
    Select Case VarType(RngData)
        Case vbNull
            vData = ""
        Case vbInteger, vbLong, vbSingle, vbDouble
            vData = RngData
        Case vbBoolean
            If RngData = True Then
                vData = "TRUE"
                vData = "FALSE"
            End If
        Case Else
            vData = RngData
    End Select
    'sqlQ = "Insert into " & RangeName & " (F1) Values (" & vdata & ")"
    sqlQ = "SELECT * FROM " & RangeName
    Set oRS = New ADODB.Recordset
        oRS.ActiveConnection = oConn
        oRS.CursorType = adOpenDynamic        'Static cursor.
        oRS.LockType = 2                      'Pessimistic Lock.
        'oRS.EditMode = 3
        oRS.Source = sqlQ
        oRS.Fields(0).value = vData

End Sub

Open in new window

VLOOKUP will work fine - assuming you have connectivity

Just open both the remote and master sheets on a machine. On the remote sheet start entering the formula =VLOOKUP(.... and point to the other sheet for the references.
gixxer6Author Commented:
i dont have a problem with vlookups (just want clarification), but i do need help with being able to update the cells in the master sheet.

The master sheet currently has 200+ lines in and i need to update approx 5 cells from the gating sheet to the master. each line is for a different site with the site_id field being the unique referance between the two.

the gating sheet will be filled in for each of the records by different people (currently a team of three) i would like to be able to press a command button and have the master sheet updated.
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Robberbaron (robr)Commented:
for updating the master held on a server, I recommend treating it as a database.

use the JET or ACE  EXCEL providers.


it gets over the sharing issues and closes quickly.
Robberbaron (robr)Commented:
and create named ranges in the master to act as targets for the db lookup.
gixxer6Author Commented:
Thanks Robber,

can you provide a sample bit of code as i am not familiar using this method.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.