Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
GetValue from closed workbook
I've been using a function "GetValue" which gets a value from a closed
workbook. (Found at: http://j-walk.com/ss/Excel/tips/tip82.htm) Works great except I want to put this into a loop to reference different cells. Therefore I'm trying to change it so that it accepts TheRow and TheColumn as arguments instead of ref which is in A1 notation. 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 Thanks in advance for your help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
GetValue from closed workbook
Perhaps the following. I include two examples. You need to pass the full wb
path and file name to the GetData procedure. I use GetOpenFileName in the examples. This does not open the source wb, just gets the path and name. Note that, for example, if the destination range is ActiveSheet.Range(A1:J1) and the source cell name is "J10" then the destination range (A1:J1) will be populated with the source range values from J10:S10. Similarly, if the destination range is ActiveSheet.Range(A1:A10) and the source cell name is still "J10" then the destination range (A1:A10) will be populated with the source range values from J10:J19. Sub Test1() Dim FileName As Variant FileName = Application.GetOpenFilename("Excel Files(*.xls), *.xls") If FileName = False Then Exit Sub GetData CStr(FileName), "Sheet1", "A1", ActiveSheet.Range("A1:A20") End Sub Sub Test2() Dim FileName As Variant FileName = Application.GetOpenFilename("Excel Files(*.xls), *.xls") If FileName = False Then Exit Sub GetData CStr(FileName), "Sheet1", "A1", ActiveSheet.Range("A1:J1") End Sub Sub GetData(SourceFile As String, SourceSheet As String, _ SourceCell As String, DestRng As Range) Dim P As String P = Left$(SourceFile, InStrRev(SourceFile, "\") - 1) SourceFile = Dir(SourceFile) DestRng.Formula = "=If('" & P & "\[" & SourceFile & "]" & _ SourceSheet & "'!" & SourceCell & "=" & """"", """", '" & _ P & "\[" & SourceFile & "]" & SourceSheet & "'!" & SourceCell & ")" DestRng.Value = DestRng.Value End Sub Greg |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
GetValue - Closed Workbook? | Excel Discussion (Misc queries) | |||
Getvalue from closed workbook | Excel Discussion (Misc queries) | |||
getValue | Excel Programming | |||
GetValue to extract data from closed files | Excel Programming | |||
Closing Hidden Workbook when Active Workbook is Closed | Excel Programming |