Alex,
I'd be interested in the code you wrote it sounds very interesting, I
have programmed in C and Java before so I think I will be able to
understand it. Is there anything about VB I should know in order to
understand your code?
Thanks for the help. If you would rather e-mail the code I can be
reached at
Thanks,
David

Alex Wrote:
David

I don't think that what you want to do is possible with straight
forward
Excel functions.

I have wriiten some code that will do what you want but is written in
VBA
(not sure if you are familiar with this).

Before I post it, would let me know whether you are happy to receive a
VBA
based solution. If you are unfamilier with it then there maybe little
point
in sending it.

To paraphrase, I have written some code that will enable you to type
the
date of the file you wnat into cell A1 of a spreadsheet. Then you press
a
button and it will draw in the data you want from that excel file. All
this
opertaes without you having to open the excel sheet you wnat the data
from.

You would have to amend the code to set the correct filepath for your
files
as well as the Excel data range that you wnat to import. Obviously I do
not
know those things.

Let me know if you are interested...

Alex

"DavidMunday" wrote:
-

I have a set of xls files that contain data I would like to import
into
another xls sheet, however the file that I need to import from will
depend on a date entered into a cell in the destination file. The
source files are named with the following convention:

Week of 3-22-04.xls

I would like to be able to pull data from a worksheet in that
particular file by putting the date 3/22/04 into my main spreadsheet.

Thanks,
David


--
DavidMunday
-


--
DavidMunday

David

I have twice tried to email you the code but I get message from the
mailer-daemon saying that my message has been delayed.

All my other emails have reached their destinations wothout problem. Has
there been a problem with your email recently?

Regards


Alex

"DavidMunday" wrote:


Alex,
I'd be interested in the code you wrote it sounds very interesting, I
have programmed in C and Java before so I think I will be able to
understand it. Is there anything about VB I should know in order to
understand your code?
Thanks for the help. If you would rather e-mail the code I can be
reached at
Thanks,
David

Alex Wrote:
David

I don't think that what you want to do is possible with straight
forward
Excel functions.

I have wriiten some code that will do what you want but is written in
VBA
(not sure if you are familiar with this).

Before I post it, would let me know whether you are happy to receive a
VBA
based solution. If you are unfamilier with it then there maybe little
point
in sending it.

To paraphrase, I have written some code that will enable you to type
the
date of the file you wnat into cell A1 of a spreadsheet. Then you press
a
button and it will draw in the data you want from that excel file. All
this
opertaes without you having to open the excel sheet you wnat the data
from.

You would have to amend the code to set the correct filepath for your
files
as well as the Excel data range that you wnat to import. Obviously I do
not
know those things.

Let me know if you are interested...

Alex

"DavidMunday" wrote:
-

I have a set of xls files that contain data I would like to import
into
another xls sheet, however the file that I need to import from will
depend on a date entered into a cell in the destination file. The
source files are named with the following convention:

Week of 3-22-04.xls

I would like to be able to pull data from a worksheet in that
particular file by putting the date 3/22/04 into my main spreadsheet.

Thanks,
David


--
DavidMunday
-


--
DavidMunday

David Munday

Thanks for getting back to me.

I have attached the VBA code. . There are just two modules within the code
and I have annotated the code to help you see how it works.

Background

With your C and Java backgorund I am sure your programming skills are
superior to my own and you shouldn't have too much trouble understanding the
code (This isn't the esoteric world of object-orientated programming!).

I am a self taught VBA programmer and make bespoke applications from time to
time for my workplace. I keep pieces of code that I have found useful to help
me at a later date. The code I have sent you is an adaptation from some code
out of a VBA programming book by John Walkenbach. When I read your query on
the newsgroup I thought this code would be good for your situation. I adapted
the code to your needs i.e. type in a date in a spreadsheet, press a button,
and it pulls in data from another Excel spreadsheet of that same name e.g.
12/22/04 will get data from 12-22-04.xls.

I tested this out using an Excel spreadsheet called 12-22-04.xls on my
desktop. I made the program return the values from cells A2 to A4 in
12-22-04.xls to my current open workbook without having to open 12-22-04.xls.
[NB this code will not work for you as the path to your Excel spreadsheet
will be different on your PC]

I have annotated the VBA code for you so that you can see how it works. You
will need to make changes to the code to account for (a) the pathname of your
Excel file (b) the name of the sheet the data is kept on (unless it is the
default Sheet1) (c) the cells from which you would like to retrieve the data.
I have indicated this on the code itself.

--------------------------

See what you think and if you have problems then please do not hesitate to
contact me. I am actually in the UK so there maybe timezones to consider in
terms of making timely responses.

One other thing, I had a button on my worksheet to press [Get Data]. Once
the date is entered the button can be pressed and the values retrieved. You
don't have to have a button on the worksheet. I can just as easily have an
icon on the toolbar or add an item to the menu bar. For example, you could
select <data from the menu bar and I could have an option called <Get My
Data...

I don't profess to be expert but I will do all I can to help if you need it...


Best Regards


Alex

Private Function GetValue(path, file, sheet, ref)

'David. This function is called by the code below to find the releveant .xls
sheet. This needs
'does not need to be amended.

Dim arg As String

If Right(path, 1) < "\" Then path = path & "\"
If Dir(path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If

arg = "'" & path & "[" & file & "]" & sheet & "'!" &
Range(ref).Range("A1").Address(, , xlR1C1)

GetValue = ExecuteExcel4Macro(arg)
End Function

Sub dateTest()
'The intial code recasts your date. For example, you want to enter 12/22/04
in the Excel sheet
'but the Excel workbook you want is written as 12-22-04.xls as I understand
it. Also, I presume
'you want to enter dates as 12/22/04 or, say, 3/22/04. The point of the code
immediately below
'is to assess the length of the date in terms of charatcers and extarct the
releveant parts and
'recast it in the format of ##-##-##.

Dim yr, mth, dy As Integer
Dim datePeriod As String
datePeriod = Range("A1") 'This is where you enter date in worksheet. You can
change this reference.

If Len(datePeriod) = 7 Then
yr = Right(datePeriod, 2)
mth = Left(datePeriod, 1)
dy = Mid(datePeriod, 3, 2)
End If

If Len(datePeriod) = 8 Then
yr = Right(datePeriod, 2)
mth = Left(datePeriod, 2)
dy = Mid(datePeriod, 4, 2)
End If

'Given the date entered in cell A1 this will build the the name of the .xls
file.
'Example A1 = 12/22/04 therefore filedate = 12-22-04.xls.

fileDate = mth & "-" & dy & "-" & yr & ".xls"

' 'p' is the pathname of your file. I tested the code using an .xls file on
my desktop.

p = "C:\WINNT\Profiles\387372\Desktop"

'This is the name of the workbook with your target data in. We have
constructed this above.

f = fileDate

'This is the name of the sheet within the Excel worksheet where your data is
held. By default
'I have assumed that it is 'Sheet1'. Obviously you would need to change this
to match whatever
'you have called your worksheet

s = "Sheet1"


Application.ScreenUpdating = False

'This final piece of code retreives the actual data. This code gets the
values of cells A2, A3,
'and A4. The Cells argument takes cells(row, column). So cells(x,1) will
loop through rows
'2,3 and 4 in column 1 of the worksheet specified. This is A2 to A4 in our
example.
'From above the GetValue function takes 4 arguments and so far we only have
3 i.e. p,f,s. The
'final argument is a cell reference and this is given by 'a'. You would need
to make amendments
'here to retrieve the data that you need. This shouldn't be too difficult.

For x = 2 To 4
a = Cells(x, 1).Address
Cells(x, 1) = GetValue(p, f, s, a)
Next x
Application.ScreenUpdating = True

End Sub


"Alex" wrote:

David

I have twice tried to email you the code but I get message from the
mailer-daemon saying that my message has been delayed.

All my other emails have reached their destinations wothout problem. Has
there been a problem with your email recently?

Regards


Alex

"DavidMunday" wrote:


Alex,
I'd be interested in the code you wrote it sounds very interesting, I
have programmed in C and Java before so I think I will be able to
understand it. Is there anything about VB I should know in order to
understand your code?
Thanks for the help. If you would rather e-mail the code I can be
reached at
Thanks,
David

Alex Wrote:
David

I don't think that what you want to do is possible with straight
forward
Excel functions.

I have wriiten some code that will do what you want but is written in
VBA
(not sure if you are familiar with this).

Before I post it, would let me know whether you are happy to receive a
VBA
based solution. If you are unfamilier with it then there maybe little
point
in sending it.

To paraphrase, I have written some code that will enable you to type
the
date of the file you wnat into cell A1 of a spreadsheet. Then you press
a
button and it will draw in the data you want from that excel file. All
this
opertaes without you having to open the excel sheet you wnat the data
from.

You would have to amend the code to set the correct filepath for your
files
as well as the Excel data range that you wnat to import. Obviously I do
not
know those things.

Let me know if you are interested...

Alex

"DavidMunday" wrote:
-

I have a set of xls files that contain data I would like to import
into
another xls sheet, however the file that I need to import from will
depend on a date entered into a cell in the destination file. The
source files are named with the following convention:

Week of 3-22-04.xls

I would like to be able to pull data from a worksheet in that
particular file by putting the date 3/22/04 into my main spreadsheet.

Thanks,
David


--
DavidMunday
-


--
DavidMunday