Solved: Set the cell value of another spreadsheet

[darthNader]

Hello,

I am trying to see if the following is possible, I have 2 spreadsheets File1 and File2. I am writing a macro in file1 that will update the value of a cell in file2. Does anyone know if this is possible to do without opening up file2.

Thanks in advance!
darthNader

[lucas]

[VBA]'credit for this technique goes to John Walkenback
'http://j-walk.com/ss/excel/tips/tip82.htm

Sub GetDataDemo()
Dim FilePath$, Row&, Column&, Address$

'change constants & FilePath below to suit
'***************************************
Const FileName$ = "Book1.xls"
Const SheetName$ = "Sheet1"
Const NumRows& = 10
Const NumColumns& = 10
FilePath = ActiveWorkbook.Path & "\"
'***************************************

DoEvents
Application.ScreenUpdating = False
If Dir(FilePath & FileName) = Empty Then
MsgBox "The file " & FileName & " was not found", , "File Doesn't Exist"
Exit Sub
End If
For Row = 1 To NumRows
For Column = 1 To NumColumns
Address = Cells(Row, Column).Address
Cells(Row, Column) = GetData(FilePath, FileName, SheetName, Address)
Columns.AutoFit
Next Column
Next Row
ActiveWindow.DisplayZeros = False
End Sub
Private Function GetData(Path, File, Sheet, Address)
Dim Data$
Data = "'" & Path & "[" & File & "]" & Sheet & "'!" & _
Range(Address).Range("A1").Address(, , xlR1C1)
GetData = ExecuteExcel4Macro(Data)
End Function[/VBA]

[lucas]

I just noticed you are new to the forum. Welcome.

Give us as many details as you can, it will make it easier to help you.
There are several ways to do this but most involve opening the file. Why is that a problem? It just opens it, retrieves the data and closes it. Is it a shared workbook?

This can all be done when the second workbook opens.....

[GTO]

Hello,

I am trying to see if the following is possible, I have 2 spreadsheets File1 and File2. I am writing a macro in file1 that will update the value of a cell in file2. Does anyone know if this is possible to do without opening up file2.

Thanks in advance!
darthNader

Greetings to all,

Hey darthNader, maybe I misunderstood your question, but it seemed to me that you are asking as to writing to a closed workbook(wb). While there are several ways to read from a closed wb, I am unaware of any way to write to one.

@Lucas:
Hi Steve , hope no damages incurred from the recent weather.

Mark

[lucas]

Darth,
could you clarify your request for us? Maybe a sample set of workbooks zipped and attached to the post. Hit post reply at the bottom left of the last post and then after posting your message, scroll down and look for the button that says "manage attachments"

Hi Mark, it's just been bitterly cold here. No real ice or snow so far. I'm planning a trip to LA after Christmas to visit my son and his family and to get away from the cold for a few weeks.

Still golfing weather in AZ?

[GTO]

@Lucas:

Nope. The day after (whatever day it was I bragged on the lovely weather), it turned as cold as a dead snake and a little rainy a couple of days. I barely caught a couple of sentences from the news/weather last night, but it sounded like less than stellar predictions for tomorrow and Christmas. It is brightening up today though so they may well be off (hopefully).

Well, I'm off to do last minute gift shopping (mostly last minute because I'd rather stick a needle in my eye than shop).

A Merry Christmas to you and yours,

Mark

PS - Okay, when I say cold, I mean the wimpy Phoenix version. Desert rat, thin blood, etc...

[darthNader]

Hi - Thank you for the replies,

@All, I am trying to do work around some limitations in Share point?s Excel Services. I need Excel Services to read from the value of as cell. However, ES does not read from workbooks that are protected or contain comments, both of which my workbook needs to have. As a workaround, I would like to have file1 as my main file with the protection and comments and to write a macro within it that will set a cell in file2 that ES can read from. Please let me know if any clarification is needed.

@GTO, yep, that is exactly what I need to do.

@Lucas, the spreadsheets are being stored in document libraries in a Share point portal which will inherently make them shared and accessible to all users.
For this reason, I thought it might get a little hairy with opening up the file, however, if that is the only way to do it, it might not be too bad after all.

Merry Christmas,
darthNader

[lucas]

This works for putting the value of a cell into a cell in a closed workbook.

You have to put the data.xls in the C:\Temp directory or change the path in the code.

[VBA]
Option Explicit
Sub PutDataInClosedWorkbook()
Dim wb As Workbook
Application.ScreenUpdating = False ' turn off the screen updating
Set wb = Workbooks.Open("C:\Temp\data.xls", True, False)
With wb.Worksheets("Sheet1")
' read data from the source workbook
wb.Worksheets("Sheet1").Range("A10").Formula = ThisWorkbook.Worksheets("Target").Range("A10").Formula

End With
wb.Save
wb.Close
Set wb = Nothing ' free memory
Application.ScreenUpdating = True ' turn on the screen updating
End Sub
[/VBA]

[darthNader]

Lucas,

That did the trick. Thanks very much!!

[lucas]

Be sure to mark your thread solved using the thread tools at the top of the page.

[lucas]

Darth, You might need to check to see if the file is open before you take action on it. A few links that might help:

http://vbaexpress.com/kb/getarticle.php?kb_id=443

http://vbaexpress.com/kb/getarticle.php?kb_id=625

http://vbaexpress.com/kb/getarticle.php?kb_id=468

http://vbaexpress.com/kb/getarticle.php?kb_id=186