Solved

Excel remote updates

Posted on 2013-10-31
6
193 Views
Last Modified: 2013-11-12
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
0
Comment
Question by:gixxer6
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 70

Expert Comment

by:KCTS
ID: 39615692
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
 

Author Comment

by:gixxer6
ID: 39616259
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
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 39616357
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
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 39616361
and create named ranges in the master to act as targets for the db lookup.
0
 

Author Comment

by:gixxer6
ID: 39621517
Thanks Robber,

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

thx
0
 
LVL 32

Accepted Solution

by:
Robberbaron (robr) earned 500 total points
ID: 39623848
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question