MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Read in Data Without Opening The File

  Asked By: Dora    Date: Oct 29    Category: MS Office    Views: 1643

I want to write the data from cells(1,1) in workbooks("A") to workbooks

Here is my code in workbook B:

The code only works when the workbook A already open.Otherwise, the
code fails.

Who can tell me how to write the data in workbook A to workbook B
without open the workbook A?



5 Answers Found

Answer #1    Answered By: Kuhlbert Schmidt     Answered On: Oct 29

I'm not sure that it's possible to do this without opening  the file.
The fact that you wish to access the information in workbook("A") means
that you have to open  it.
The question is: HOW must the file  be opened.
it MAY be possible to copy the file to .xla and open it as an add-in.
That way, the file isn't viewable, but MAY be accessible programmatically.
I haven't tried this, they keep giving me WORK to do!

After viewing your question though, are you saying that you don't WANT
to open the first workbook, or that you don't want to open it MANUALLY.
(meaning that you want the program to open it if it not already open).
Perhaps I'm reading more into this than is meant...

Answer #2    Answered By: Maria Hughes     Answered On: Oct 29

It's actually very easy to pull data  from a closed workbook  into an open
workbook. I'm don't think you can retrieve the data via code, then put the
retrieved value in a cell. I think you will have to enter a formula in a cell to
pull the value from a cell in the closed workbook. Once the value is retrieved,
you can convert the formula to a value. Here is an example:

Sub Macro1()
'Enter a formula in A1 to pull A1 from Sheet1 of closed wkbk A.
'For the closed workbook, must include the path.
Range("A1").FormulaR1C1 = _
'Copy the formula in A1 down through A4.
Selection.AutoFill Destination:=Range("A1:A4"), Type:=xlFillDefault
'Copy & paste as values.
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

There are some limitations to this technique. If the closed workbook is
password protected, you will have to provide the password when you enter the
first retrieve formula. You can create a SUMPRODUCT formula referring to a
closed workbook, but not SUMIF. I don't remember all the caveats, but I hope
this is helpful.

Answer #3    Answered By: Damian Jones     Answered On: Oct 29

I'll have to try this.
The last time I did something like this, the formulas that reference other
caused them to open.
But perhaps that was an Excel 2003 "feature".

Answer #4    Answered By: Eamon Jones     Answered On: Oct 29

it DID actually work without opening  the original file...
The resulting formula I used was:


which read  the contents from Cell A1 in sheet "Sheet1"
of workbook: C:\temp\Book1.xls

i even tested it by changing the contents of Cell A1,
closed all workbooks and opened the "new" book and it updated properly.

You can programatically insert this into the cell by using:

ActiveCell.FormulaR1C1 = "='C:\temp\[Book1.xls]Sheet1'!R2C1"

Now, a blank in Book1 returns "0", so to remove that, I used instead:

ActiveCell.FormulaR1C1 = _
"=IF('C:\temp\[Book1.xls]Sheet1'!R1C1 =

then used:

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

to copy/paste values

Learn something new every day... Does that mean I can go home now?

Answer #5    Answered By: Rachael Ferguson     Answered On: Oct 29

Using simple cell to cell "=" links into an unopened workbook  was a
method for recovering data  from a damaged and un-openable workbook (
actually I think it was before workbooks - just a sheet).

Short - Create a Link:
Type "=" then another cell. A formula automatically results. The
other cell can be in another workbook/file.

long - easy to do, many words to describe)
The original algorithm that I saw first instructed you to create (in
a destination sheet/cell) a link to an open, blank, "surrogate" sheet
(by clicking an "=" then clicking in the surrogate's source cell).
Then you changed the workbook name in the destination cell's resulting
formula to the name of the damaged workbook. Then copied that formula
over the expected data area. Whatever data 'came' was recoverable.

Didn't find what you were looking for? Find more on Read in Data Without Opening The File Or get search suggestion and latest updates.