PDA

View Full Version : how to set a value to an external cell ?



vb_albion
08-28-2009, 09:54 AM
hi
does anybody know how to set a cell value in a closed external workbook?
the following doesn't work:
Application.Workbooks("F:\DESKTOP\za abc\xy_z.xls") _
.Worksheets("Sheet1").Range("H1").Value = 123 /error 1004 - application defined or object defined error/
thanks

Bob Phillips
08-28-2009, 10:49 AM
Open it.

Set the value.

Save it.

Close it.

vb_albion
08-28-2009, 11:04 AM
Open it.

Set the value.

Save it.

Close it.
LOL, does it mean there's no way ?

Bob Phillips
08-28-2009, 11:12 AM
You can do it with ADO, but that is just so much simpler.

vb_albion
08-28-2009, 11:27 AM
absolutum understoodum

vb_albion
08-28-2009, 11:39 AM
On the second thought i have no other solution but to use ADO (i need results from several workbooks to merge into one ...), but i've never used it :dunno
Could you help me out with this? Basically i need something like:
Application.Workbooks("F:\DESKTOP\za abc\xy_z.xls") _
.Worksheets("Sheet1").Range("H1").Value = Worksheets(strFirst10).Cells(1, 1).Valuehow to implement ado? :help

Bob Phillips
08-28-2009, 11:46 AM
Why does that necessitate ADO?

You can just process each book in turn, same way.

vb_albion
08-28-2009, 12:05 PM
You can just process each book in turn, same way. huh, not that simple. i have 2 workbooks (pay-in & pay-out) with the corresponding user forms. after issuing say pay-in receipt it supposed to have a button which will forward data (ordinal numeral, customer name, amount ..) as a new row in the common log workbook (with the amount on the 'pay-in' or the 'pay-out' column). i hope you got the picture.

vb_albion
08-28-2009, 12:22 PM
I suppose ms access is the right thing for this, but it faded out from my mind.

Bob Phillips
08-28-2009, 01:48 PM
huh, not that simple. i have 2 workbooks (pay-in & pay-out) with the corresponding user forms. after issuing say pay-in receipt it supposed to have a button which will forward data (ordinal numeral, customer name, amount ..) as a new row in the common log workbook (with the amount on the 'pay-in' or the 'pay-out' column). i hope you got the picture.

Can say that I have, but it is never simpler to use ADO to write to a worksheet rather than opening them within Excel. If it has forms that you need to open, you definitely cannot use ADO.