import =[test.xls]Sheet1!A1 - help

[AMaleThing]

I'm importing data from .xls reports created each day. The file will
include the date.

Is it possible the file is called "Test 01-05-2008.xls", I could
create a string something like:

=[test +"A3"+ .xls]Sheet1!A1 // Where A3 is 01-05-2008

Help appreciated.

 

[jain_ayush85]

Use INDIRECT function of excel.

-Ayush Jain
www.vbamacros.blogspot.com

 

[AMaleThing]

Use INDIRECT function of excel.

-Ayush Jainwww.vbamacros.blogspot.com

I'm importing data from .xls reports created each day.  The file will
include the date.

Is it possible the file is called "Test 01-05-2008.xls", I could
create a string something like:

=[test +"A3"+ .xls]Sheet1!A1    // Where A3 is 01-05-2008

Help appreciated.- Hide quoted text -

- Show quoted text -

Thank you,

Is it possible to dumb your answer down a little? I haven't used
INDIRECT before, and from the help description, I'm still lost :s

From the description of INDIRECT, I couldn't see how it would create a
string such as "=[test 01-05-2008.xls]Sheet1!A1"

Help please.

 

[Dave Peterson]

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

========
Try opening "Test 01-05-2008.xls" first (easier to test).

Then in a worksheet in a different workbook, put the date in A1.

Then try this:
=INDIRECT("'[test " & TEXT(A1,"mm-dd-yyyy") & ".xls]sheet1'!A1")

(is 01-05-2008 January 5, 2008 or May 1, 2008???? Change that =text() format
the way you need).

After you have that working, you can try using =indirect.ext(). You'll have to
add the drive/path, so that it looks something like:

=INDIRECT.ext("'c:\excel\[test " & TEXT(A1,"mm-dd-yyyy") & ".xls]sheet1'!A1")

But that's a guess. I don't use that addin.

But lots of people do. If you have trouble getting it to work, post back with
the drive/folder name and what you've tried. I'll bet you get help!

I'm importing data from .xls reports created each day. The file will
include the date.

Is it possible the file is called "Test 01-05-2008.xls", I could
create a string something like:

=[test +"A3"+ .xls]Sheet1!A1 // Where A3 is 01-05-2008

Help appreciated.

 

[gwhenning]

The indirect formula allows you to type in any value and use it as a cell (or
range?) reference. For example if you type =A1 into cell C1, whatever you
have typed into A1 will also be in cell C1. if you type ="A"&"1" in cell C1
you literally get "A1" in cell C1. If you enclose that formula in an indirect
statement =INDIRECT("A"&"1") you will once again get whatever was typed into
cell A1. Indirect changes the text "A1" into a cell reference. You can
substitue the value of a cell as a cell reference, for example D1="A1" and
cell C1=indirect(D1) then the value of C1 will be whatever is in A1.