Solved

Excel remote updates

Posted on 2013-10-31
6
169 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
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
ScreenConnect 6.0 Free Trial

Want empowering updates? You're in the right place! Discover new features in ScreenConnect 6.0, based on partner feedback, to keep you business operating smoothly and optimally (the way it should be). Explore all of the extras and enhancements for yourself!

 
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

822 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