Excel remote updates

Hi,

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)
Gating-Sheet-V1.1.xlsm
gixxer6Asked:
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
    oConn.Close
	
	
	
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"
             Else
                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.Open
        
        oRS.MoveFirst
        oRS.Fields(0).value = vData
        oRS.Update
    oRS.Close


End Sub

Open in new window

0
 
Brian PiercePhotographerCommented:
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.
0
 
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.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
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.

http://www.connectionstrings.com/excel/

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

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

thx
0
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.