Ravi, here is a tip from J. Walk
VBA does not include a method to retrieve a value from a closed file. You can,
however, take advantage of Excel's ability to work with linked files.
This tip contains a VBA function that retrieves a value from a closed workbook.
It does by calling an XLM macro.
Note: You cannot use this function in a worksheet formula.
The GetValue Function
The GetValue function, listed below takes four arguments:
path: The drive and path to the closed file (e.g., "d:\files")
file: The workbook name (e.g., "budget.xls")
sheet: The worksheet name (e.g., "Sheet1")
ref: The cell reference (e.g., "C4") Private Function GetValue(path, file,
sheet, ref)
' Retrieves a value from a closed workbook
Dim arg As String
' Make sure the file exists
If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If
' Create the argument
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)
' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function
Using the GetValue Function
To use this function, copy the listing to a VBA module. Then, call the function
with the appropriate arguments. The Sub procedure below demonstrates. It simply
displays the value in cell A1 in Sheet1 of a file named Budget.xls, located in
the XLFiles\Budget directory on drive C:.Sub TestGetValue()
p = "c:\XLFiles\Budget"
f = "Budget.xls"
s = "Sheet1"
a = "A1"
MsgBox GetValue(p, f, s, a)
End Sub
Another example is shown below. This procedure reads 1,200 values (100 rows and
12 columns) from a closed file, and places the values into the active
worksheet.Sub TestGetValue2()
p = "c:\XLFiles\Budget"
f = "Budget.xls"
s = "Sheet1"
Application.ScreenUpdating = False
For r = 1 To 100
For c = 1 To 12
a = Cells(r, c).Address
Cells(r, c) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating = True
End Sub
Caveat
In order for this function to work properly, a worksheet must be active in
Excel. It will generate an error if all windows are hidden, or if the active
sheet is a Chart sheet.
_________________________________________________________________________________________________
"There are known knowns. These are things we know that we know. There are known
unknowns. That is to say, there are things that we know we don't know. But
there are also unknown unknowns. There are things we don't know we don't know."
--- On Sat, 30/8/08, Ravi Megharaj <[EMAIL PROTECTED]> wrote:
From: Ravi Megharaj <[EMAIL PROTECTED]>
Subject: $$Excel-Macros$$ Re: Code to copy paste data from different files
To: excel-macros@googlegroups.com, [EMAIL PROTECTED]
Date: Saturday, 30 August, 2008, 3:53 PM
Hi Darshan,
Thank you so much for this code.
For the following code to work it is necessary that all the other files namely
sales and Emp_Info must be opened. But I do not want to open those files. All
these files are saved in a particular location say on E:\Data. Hence, I will
open only file Consolidated and run the macro on this sheet.
Can you please me on this?
On Thu, Aug 28, 2008 at 3:01 PM, Darshan Amreliya <[EMAIL PROTECTED]> wrote:
Use following code
Sub copy_data()
Windows("Consolidated").Activate
Sheets("sales").Select
Windows("Sales").Activate
Range("a2:b7").Select
Selection.Copy
Windows("Consolidated").Activate
Sheets("sales").Select
Range("a2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("Consolidated").Activate
Range("a2:b11").Select
Selection.Copy
Windows("Consolidated").Activate
Sheets("emp").Select
Range("a2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
On 8/27/08, Ravi Megharaj <[EMAIL PROTECTED]> wrote:
> Hi Group,
> I have attached 3 excel files namely, Sales, Emp_Info and Consolidated. Can
> anyone please provide me code for following task:
> All the data needs to be copied from file Sales-Tab Sales and pasted to file
> Consolidated-Tab Sales. Similarly from file Emp_Info-Tab EMP to file
> Consolidated=Tab EMP.
>
> Please do the needful on file Consolidated which is attached.
>
> Thanks you so much
>
> Regards,
>
> Ravi S Megharaj
>
> >
>
--
Regards
Darshan Amreliya
Sr Engineer
Ispat Industries Limited
Cell No 09321063681
Get an email ID as [EMAIL PROTECTED] or [EMAIL PROTECTED] Click here
http://in.promos.yahoo.com/address
--~--~---------~--~----~------------~-------~--~----~
Visit the blog to download Excel tutorials at
http://www.excel-macros.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/excel-macros?hl=en
Visit & Join Our Orkut Community at
http://www.orkut.com/Community.aspx?cmm=22913620
Visit the blog to download Excel tutorials at
http://www.excel-macros.blogspot.com
To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com
To see the Daily Excel Tips, Go to:
http://exceldailytip.blogspot.com
-~----------~----~----~----~------~----~------~--~---
