<p>I have two spreadsheets... when one gets modified in a certain way I want to have a macro run that modifies the second in an appropriate manner.  I've already isolated the event I need to act on (the modification of any cell in a particular column), I just can't seem to find any concrete information on accessing and modifying another spreadsheet (this spreadsheet is located on a different LAN share also... the user has access to both, though).</p>

<p>Any help would be great.  References on how to do this or something similar are just as good as concrete code samples.</p>

[{'answer_id': 51111, 'author': 'Michael Pryor', 'author_id': 245, 'author_profile': 'https://Stackoverflow.com/users/245', 'pm_score': 4, 'selected': True, 'text': '<p>In Excel, you would likely just write code to open the other worksheet, modify it and then save the data.</p>\n\n<p>See <a href="http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=302" rel="noreferrer">this tutorial</a> for more info.</p>\n\n<p>I\'ll have to edit my VBA later, so pretend this is pseudocode, but it should look something like:</p>\n\n<pre><code>Dim xl: Set xl = CreateObject("Excel.Application")\nxl.Open "\\\\the\\share\\file.xls"\n\nDim ws: Set ws = xl.Worksheets(1)\nws.Cells(0,1).Value = "New Value"\nws.Save\n\nxl.Quit constSilent\n</code></pre>\n'}, {'answer_id': 51375, 'author': 'paulmorriss', 'author_id': 2983, 'author_profile': 'https://Stackoverflow.com/users/2983', 'pm_score': 0, 'selected': False, 'text': '<p>You can open a spreadsheet in a single line:</p>\n\n<pre><code>Workbooks.Open FileName:="\\\\the\\share\\file.xls"\n</code></pre>\n\n<p>and refer to it as the active workbook:</p>\n\n<pre><code>Range("A1").value = "New value"\n</code></pre>\n'}, {'answer_id': 51413, 'author': 'Mark Nold', 'author_id': 4134, 'author_profile': 'https://Stackoverflow.com/users/4134', 'pm_score': 0, 'selected': False, 'text': '<p>Copy the following in your <code>ThisWorkbook</code> object to watch for specific changes. In this case when you increase a numeric value to another numeric value. </p>\n\n<p>NB: you will have to replace <code>Workbook-SheetChange</code> and <code>Workbook-SheetSelectionChange</code> with an underscore. Ex: <code>Workbook_SheetChange</code> and <code>Workbook_SheetSelectionChange</code> the underscore gets escaped in Markdown code.</p>\n\n<pre><code>Option Explicit\nDim varPreviousValue As Variant \' required for IsThisMyChange() . This should be made more unique since it\'s in the global space.\n\n\nPrivate Sub Workbook-SheetChange(ByVal Sh As Object, ByVal Target As Range)\n  \' required for IsThisMyChange()\n  IsThisMyChange Sh, Target\nEnd Sub\n\nPrivate Sub Workbook-SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)\n  \'  This implements and awful way of accessing the previous value via a global.\n  \'  not pretty but required for IsThisMyChange()\n  varPreviousValue = Target.Cells(1, 1).Value \' NB: This is used so that if a Merged set of cells if referenced only the first cell is used\nEnd Sub\n\nPrivate Sub IsThisMyChange(Sh As Object, Target As Range)\n  Dim isMyChange As Boolean\n  Dim dblValue As Double\n  Dim dblPreviousValue As Double\n\n  isMyChange = False\n\n  \' Simple catch all. If either number cant be expressed as doubles, then exit.\n  On Error GoTo ErrorHandler\n  dblValue = CDbl(Target.Value)\n  dblPreviousValue = CDbl(varPreviousValue)\n  On Error GoTo 0 \' This turns off "On Error" statements in VBA.\n\n\n  If dblValue &gt; dblPreviousValue Then\n     isMyChange = True\n  End If\n\n\n  If isMyChange Then\n    MsgBox ("You\'ve increased the value of " &amp; Target.Address)\n  End If\n\n\n  \' end of normal execution\n  Exit Sub\n\n\nErrorHandler:\n  \' Do nothing much.\n  Exit Sub\n\nEnd Sub\n</code></pre>\n\n<p>If you are wishing to change another workbook based on this, i\'d think about checking to see if the workbook is already open first... or even better design a solution that can batch up all your changes and do them at once. Continuously changing another spreadsheet based on you listening to this one could be painful.</p>\n'}, {'answer_id': 54640, 'author': 'Justin Bennett', 'author_id': 271, 'author_profile': 'https://Stackoverflow.com/users/271', 'pm_score': 0, 'selected': False, 'text': '<p>After playing with this for a while, I found the Michael\'s pseudo-code was the closest, but here\'s how I did it:</p>\n\n<pre><code>Dim xl As Excel.Application\nSet xl = CreateObject("Excel.Application")\nxl.Workbooks.Open "\\\\owghome1\\bennejm$\\testing.xls"\nxl.Sheets("Sheet1").Select\n</code></pre>\n\n<p>Then, manipulate the sheet... maybe like this:</p>\n\n<pre><code>xl.Cells(x, y).Value = "Some text"\n</code></pre>\n\n<p>When you\'re done, use these lines to finish up:</p>\n\n<pre><code>xl.Workbooks.Close\nxl.Quit\n</code></pre>\n\n<p>If changes were made, the user will be prompted to save the file before it\'s closed.  There might be a way to save automatically, but this way is actually better so I\'m leaving it like it is.</p>\n\n<p>Thanks for all the help!</p>\n'}]
['https://Stackoverflow.com/questions/51098', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/271/']