How to write in a closed workbook

[gnod]

Hi,

how can i write in a closed workbook and automatically computed if there's a formula without opening the file.. i have a function how to read in a closed workbook but how can i write? :help :think:

Private Function ReadDataFromWorkbook(SourceFile As String, SourceRange As Variant) As Variant
Dim cnn As ADODB.Connection, rst As ADODB.Recordset
Dim strConnection As String, strMyPrompt As String

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=No"";"

Set cnn = New ADODB.Connection
On Error GoTo ErrorHandler
cnn.Open strConnection
Set rst = cnn.Execute("[" & SourceRange & "]")
On Error GoTo 0
ReadDataFromWorkbook = rst.GetRows 'returns a two dim array with all records in rst
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
On Error GoTo 0
Exit Function

ErrorHandler:
strMyPrompt = "The Source File or Source Range is invalid!"

MsgBox strMyPrompt, vbCritical, "Error: Consolidation"
Set rst = Nothing
Set cnn = Nothing
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
'Terminates execution
End
End Function

[johnske]

Far as I know, there's no way you can write to a closed workbook, and if you could, how could it be saved without having the workbook open? - What you have seems long-winded, try Option Explicit

Sub ExtractDataFromClosedBook1()

Application.ScreenUpdating = False

'first we'll create links to get the data from the closed book
'(put your own path and ranges below)
With [Sheet1!A1:H400]
' (------------worksheet links to the closed book-----------)
.Value = "='" & ActiveWorkbook.Path & "\[Book11.xls]Sheet1'!A1:H400"

'now remove the links
.Value = .Value
End With

Application.ScreenUpdating = True

End Sub :)

[gnod]

Here's the scenario of my project:
i have 50+ excel file which has a data for employee dental fee, lets say Php 100.00 then later we decided to change it to Php 50.00.. i need it to write to the 50+ excel file without opening the file.. i don't want to open the 50+ excel file to change the dental fee to Php 50.00..

So, it is not possible?
pls :help
: pray2:

[Bob Phillips]

Cross-posted at JMT.

Cross-posting wastes our time.

[stanl]

As an aside: could


' (------------worksheet links to the closed book-----------)
.Value = "='" & ActiveWorkbook.Path & "\[Book11.xls]Sheet1'!A1:H400"


be amended so that it gets the formatting as well as the values. Stan

[CBrine]

You may want to look at this MSDN article, which explains how to use ADO to write to a closed excel workbook. I tried some testing, and I was not able to get it to work. You need to define ReadOnly=False in the connection string.

http://support.microsoft.com/kb/257819

HTH
Cal

[gnod]

Cross-posted at JMT.

Cross-posting wastes our time.

Sorry, maybe some guys from JMT knows how to write in a closed workbook that's why i post it in that forum..

[gnod]

Thanks Cbrine.. i'll try this one..

[johnske]

Any closed workbook is just a series of 1's and 0's in a file on your hard drive, and of course those 1's and 0's could always theoretically be read, written to, and otherwise changed and manipulated by (say) using a 3rd party program or application to write directly to that file - but this is not a simple task.

However, the main point here is... what exactly IS the point? You already have an application that can open the book and read/write/change/(whatever), it's called Excel - use the Excel app to open the file and make your changes (set ScreenUpdating = False if you don't want to see the other file...):)


As an aside: could


' (------------worksheet links to the closed book-----------)
.Value = "='" & ActiveWorkbook.Path & "\[Book11.xls]Sheet1'!A1:H400"


be amended so that it gets the formatting as well as the values. StanNo - all that's being done here is creating links to the data in the closed book, we don't create links to formats - open the workbook to get the formats :)

[gnod]

Here's the scenario of my project:
i have 50+ excel file which has a data for employee dental fee, lets say Php 100.00 then later we decided to change it to Php 50.00.. i need it to write to the 50+ excel file without opening the file.. i don't want to open the 50+ excel file to change the dental fee to Php 50.00..

So, it is not possible?
pls :help
: pray2:

here's the point.. that's why i need it to write in a closed workbook

[johnske]

If there is something in those closed workbooks that is dependent on variable data in another (independent) workbook, you can put links in those books to the data in the book that they're dependent on - the dependent data will then be updated when the dependent books are opened.

[johnske]

BTW, note that (IMO) links and any ideas of 'writing to a closed workbook' should not be considered good practice at all.

Your project(s) should preferably be arranged such that most - if not all - of your code (and esp. any relating to variables that may change over time {e.g. tax rates, pay rates, etc}) is in the one workbook - this workbook should then also be used to open, access, and process those other workbooks with their dependent data.

When arranged in that way, any variable changes (or code changes you need to make) only have to be made to the one workbook - if presentation is not an issue, you might also consider using a database (e.g. Access) as a back-end for the main workbook. :)

[Bob Phillips]

Sorry, maybe some guys from JMT knows how to write in a closed workbook that's why i post it in that forum..

When you cross-post, it is only common courtesy to tell on BOTH forums, cross-post any info/suggestions that you get, and keep b oth forums updated, in other words you do the extra work involved rather than us.

We do this for nowt, we don't need our time being wasted by inconsidered cross-posting.

[stanl]

However, the main point here is... what exactly IS the point?

Why are you yelling at me??? [or to quote Bill Clinton, "It depends on what is the meaning of is?"]

You can (and I have) use ADO to update closed sheets assuming the data is in a recognizable database structure, which obviates the meaning of the word 'closed'.

As for the inclusion of formatting in the linked example, Excel could have done a better job. 02. Stan

[gnod]

johnske,

yes, that's what i need.. only one workbook to be maintained for my "General Assumption Sheet".. that workbook should have the capability to write to the other workbook.. pls refer to the attach file..

if i click the button modify in the % Salary Increase, the value in B2 will modify the 50+ workbook (the 50+ workbook is under in one folder, for ex: "For Modify" folder)..

: pray2:

[CBrine]

John,
In response to your comment.


BTW, note that (IMO) links and any ideas of 'writing to a closed workbook' should not be considered good practice at all.


I have used ADO access to closed workbooks in the past, without any issues. They are treated like any other ADO datasource, which can be read or written too. I posted the link to the MSDN article, after spending about 1/2 hour messing with the coding and trying to test it. I had no more time at that point, but since MS states that it's possible to do this, I figured the OP might want to give it a try. The writing to a close workbook via ADO is a VERY valid technique(IMHO), which is why I was willing to spend some time on it.
Whether this technique is needed for the process that the OP is creating is another question. Why not turn off the screen updates, open the workbook, uUpdate the data, and close and save the workbook again?

Cal

[Bob Phillips]

I have used ADO access to closed workbooks in the past, without any issues. They are treated like any other ADO datasource, which can be read or written too.

Absolutely correct, it is just another datasource to ADO, it is the drivers that manage the data structure.

[gnod]

I figured the OP might want to give it a try.

Sorry, what do you mena by "OP"?? :think:

[Bob Phillips]

You. The Original Poster.

[johnske]

Hi,
how can i write in a closed workbook and automatically computed if there's a formula without opening the file...That's the original question - if the question had been written in the reverse form as: "computed if there's a formula and write in a closed workbook without opening the file", then writing to a closed workbook may well be regarded as a valid approach...

However, that was not how the question was stated - if you're writing to a closed workbook perhaps someone would be so kind as to tell me how any sort of code in that closed workbook can then operate? - If you write to, and then read from this closed workbook without opening it to allow it to calculate, any figures you obtain from it have got to be (at the very least) regarded as suspect and unreliable :)

[gnod]

doe anyone have an idea how to do this.. i still can't figure out in the MSDN article and i don't know how i will start it.. i do have a function how to read in a closed workbook but don't know how to write..

:beg: :(

[Bob Phillips]

You have suggestions here from CBrine using ADO, and suggestions at JMT from myself and UMH to re-design your workbook.

So what have you tried and what is the question now?

[tstom]

'this example writes the value of 50 to range a1 in sheet1 of a workbook located in
'C:\Output.xls
Sub Example()

Call Write2ClosedBookSingleCellRange("C:\Output.xls", _
"Select * From [Sheet1$A1:A1]", _
50)

End Sub

Sub Write2ClosedBookSingleCellRange(WorkbookFullName As String, SQL As String, NewValue)
Dim conn As New Connection, rs As New Recordset

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & WorkbookFullName & _
";Extended Properties=""Excel 8.0;HDR=NO;"""
rs.Open SQL, conn, 1, 3
rs.Fields(0).Value = NewValue
rs.Update: rs.Close: conn.Close
End Sub

[gnod]

It works.. :p Now, i can write to the closed workbook.. thanks..
does the "Output.xls" will automatically computed even if i didn't open the closed workbook? i'll test it if it can compute.. thanks.. :thumb :bow: :clap:

[gnod]

I'm too stupid :banghead: How will i know if it is automatically calculate without opening the file..

[tstom]

No. It won't compute. Does the term cross-posting compute? At least provide the links in your many cross-posts.

You already have been given a solution. You need to open the workbooks via automation or consolidate your shared values on one resource. I just posted this to prove too you that you are barking up the wrong tree... :)

[gnod]

I just posted this to prove too you that you are barking up the wrong tree...

what do you mean :think:

[gnod]

Sorry for cross-posting.. i read this message about cross-posting from other thread in this forum..
http://www.excelguru.ca/node/7


:(

[lucas]

but still no links to the other posts so we can see what others have done on this so we don't duplicate work already done.....



I just posted this to prove too you that you are barking up the wrong tree...

Means that your idea won't work. If you change the data in the closed workbook then it won't calculate until the workbook is opened.....so you need to open each one to make the changes so the calculation will be done. Alt would be as suggested to have one cell(in each of the 50 workbooks) linked to a master where you keep updated values. When you open each of these workbooks the link will update the value.