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 said:
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
