INDIRECT on closed files

  • Thread starter Thread starter Gromit
  • Start date Start date
G

Gromit

Hi there,

I'm trying to reference a closed file using INDIRECT

e.g.

=INDIRECT('C:\[test.xls]Sheet1'!A1)

But I just get a REF! error.

Does anyone know if what I'm trying to do is possible, or am I just
doing something wrong?

Cheers,

Graham
 
Hi Jim,

I did a search on Google and turned up the following code written by
John Walkenbach, apparently designed to solve my problem. However, I
still can't get this to work. Is it me, or am I really barking up the
wrong tree here? I'm using Excel 2000 on Win 2000.

Thanks,

Graham


Function GetValue(path, file, sheet, range_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(range_ref).Range("A1").Address(, , xlR1C1)

' Execute an XLM macro

GetValue = ExecuteExcel4Macro(arg)

End Function
 
That works from VBA, but not when called from a worksheet.

But Harlan Grove posted a function that opens the other workbook and retrieves
the value from a separate instance of excel:
http://google.com/[email protected]
Hi Jim,

I did a search on Google and turned up the following code written by
John Walkenbach, apparently designed to solve my problem. However, I
still can't get this to work. Is it me, or am I really barking up the
wrong tree here? I'm using Excel 2000 on Win 2000.

Thanks,

Graham

Function GetValue(path, file, sheet, range_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(range_ref).Range("A1").Address(, , xlR1C1)

' Execute an XLM macro

GetValue = ExecuteExcel4Macro(arg)

End Function
Click to expand...
 
This file might be a help:
http://www.bygsoftware.com/examples/zipfiles/UsingSqlRequest.zip
It's in the "Excel with Access Databases" section on page:
http://www.bygsoftware.com/examples/examples.htm

This workbook demonstrates how to get data direct from an MS Access table,
or from an open or closed MS Excel workbook using the workbook function
SQL.REQUEST.

Recently updated to show the use of SQL.REQUEST in the same workbook.

The code is open and commented.
--

Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"
 
Why are you using indirect

='C:\[test.xls]Sheet1'!A1

works just fine.

Using macros and so forth to do a simple link in more than taking the long
way around the block.

If you wanted the link to be dynamic (change the sheet name or the workbook
name by entering it in another cell) you might employ one of those other
methods.
 
or maybe the simple and very standard

='C:\[test.xls]Sheet1'!A1

he didn't say he wanted it to be dynamic.

--
Regards,
Tom Ogilvy

Dave Peterson said:
That works from VBA, but not when called from a worksheet.

But Harlan Grove posted a function that opens the other workbook and retrieves
the value from a separate instance of excel:
http://google.com/[email protected]
Hi Jim,

I did a search on Google and turned up the following code written by
John Walkenbach, apparently designed to solve my problem. However, I
still can't get this to work. Is it me, or am I really barking up the
wrong tree here? I'm using Excel 2000 on Win 2000.

Thanks,

Graham

Function GetValue(path, file, sheet, range_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(range_ref).Range("A1").Address(, , xlR1C1)

' Execute an XLM macro

GetValue = ExecuteExcel4Macro(arg)

End Function
Click to expand...
Click to expand...
 
And I bet that's what Gromit really wanted.

(forest/trees thing on my part)

Tom said:
or maybe the simple and very standard

='C:\[test.xls]Sheet1'!A1

he didn't say he wanted it to be dynamic.

--
Regards,
Tom Ogilvy

Dave Peterson said:
That works from VBA, but not when called from a worksheet.

But Harlan Grove posted a function that opens the other workbook and retrieves
the value from a separate instance of excel:
http://google.com/[email protected]
Hi Jim,

I did a search on Google and turned up the following code written by
John Walkenbach, apparently designed to solve my problem. However, I
still can't get this to work. Is it me, or am I really barking up the
wrong tree here? I'm using Excel 2000 on Win 2000.

Thanks,

Graham

Function GetValue(path, file, sheet, range_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(range_ref).Range("A1").Address(, , xlR1C1)

' Execute an XLM macro

GetValue = ExecuteExcel4Macro(arg)

End Function
Click to expand...
Click to expand...
Click to expand...
 
By the place where you got involved, things had already turned to sh*t. I
just hope his excel growth isn't permanently stunted <g> or that he doesn't
publicly embarrass himself in front of his peers.

--
Regards,
Tom Ogilvy


Dave Peterson said:
And I bet that's what Gromit really wanted.

(forest/trees thing on my part)

Tom said:
or maybe the simple and very standard

='C:\[test.xls]Sheet1'!A1

he didn't say he wanted it to be dynamic.

--
Regards,
Tom Ogilvy

Dave Peterson said:
That works from VBA, but not when called from a worksheet.

But Harlan Grove posted a function that opens the other workbook and retrieves
the value from a separate instance of excel:
Click to expand...
http://google.com/[email protected]
Gromit wrote:

Hi Jim,

I did a search on Google and turned up the following code written by
John Walkenbach, apparently designed to solve my problem. However, I
still can't get this to work. Is it me, or am I really barking up the
wrong tree here? I'm using Excel 2000 on Win 2000.

Thanks,

Graham

Function GetValue(path, file, sheet, range_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(range_ref).Range("A1").Address(, , xlR1C1)

' Execute an XLM macro

GetValue = ExecuteExcel4Macro(arg)

End Function
Click to expand...
Click to expand...
Click to expand...
 
I sometimes/usually only read the replies and sometimes I don't get the real
question.

(On the other hand, a little public embarrassment never caused me any harm.
<Twitch, twitch, twitch!>)

Tom said:
By the place where you got involved, things had already turned to sh*t. I
just hope his excel growth isn't permanently stunted <g> or that he doesn't
publicly embarrass himself in front of his peers.

--
Regards,
Tom Ogilvy

Dave Peterson said:
And I bet that's what Gromit really wanted.

(forest/trees thing on my part)

Tom said:
or maybe the simple and very standard

='C:\[test.xls]Sheet1'!A1

he didn't say he wanted it to be dynamic.

--
Regards,
Tom Ogilvy

That works from VBA, but not when called from a worksheet.

But Harlan Grove posted a function that opens the other workbook and
retrieves
the value from a separate instance of excel:

http://google.com/[email protected]

Gromit wrote:

Hi Jim,

I did a search on Google and turned up the following code written by
John Walkenbach, apparently designed to solve my problem. However, I
still can't get this to work. Is it me, or am I really barking up the
wrong tree here? I'm using Excel 2000 on Win 2000.

Thanks,

Graham

Function GetValue(path, file, sheet, range_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(range_ref).Range("A1").Address(, , xlR1C1)

' Execute an XLM macro

GetValue = ExecuteExcel4Macro(arg)

End Function
Click to expand...
Click to expand...
Click to expand...
 
Back
Top