Copy Values Without Opening A Sheet.

  • Thread starter Thread starter Matt
  • Start date Start date
M

Matt

Does anyone know of any VB code which can copy the data in
a worksheet without opening it up?

Just as an example.

I want a routine that I can specify the path of another
workbook and copy the VALUES of a particular sheet without
opening it up.

Example :-

I have a blank workbook with a sheet named 'Master'

The other worksheet is in another unopened excel workbook
stored under

c:/excel/files/data.xls

The sheet I want the values from is called 'original'.

...............PLZ Help............
 
Do you need to go in and copy them, or do you just want to link to them. If
linking is fine then you have pretty much built the link with your path - just
need a few tweaks:-

Assuming you wanted the data back from a sheet called Sheet1 in that file

='c:\excel\files\[data.xls]Sheet1'!A1

This will link to cell A1 on that sheet and return the value.
 
Hi
try the following UDF

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

sub foo()
dim ret
ret = GetValue("c:\excel\files\","data.xls","original","A1")
msgbox ret
End Sub
 
Back
Top