Solved

Excel remote updates

Posted on 2013-10-31
6
148 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
  • 3
  • 2
6 Comments
 
LVL 70

Expert Comment

by:KCTS
Comment Utility
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
Comment Utility
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)
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 32

Expert Comment

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

Author Comment

by:gixxer6
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

771 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now