Hi Barrett,

I pretty much have the code that does what you want, but I'm still
unclear about a few things.

One: suppose there is more than one E2APBX file in the E2APBX
directory. Does that mean that you want to replace whatever the
content of A6:I6 with the latest file, or do you want to continue
adding rows (A7:B7, A8:B8, etc)? Right now i have code looping
through any file matching the criteria, but if you're just looking for
one file then it doesn't make a difference. I'm sure I'm
demonstrating a lack of knowledge about what you're actually trying to
accomplish, but hey, I'm an accountant not a scientist. =]

Two: what is the basis for the date comparison? I have a function
that will extract the date portion of the file name, but what am i
comparing it to? Am i looking at the last date you have in B6? Or
the most recent date in column B? Today's date?

Let me know and I'll post the finished code some time tomorrow.


On Dec 6, 1:26 pm, ST Jude wrote:
Hi ilia,
Thanks for responding so quickly!

Is run# always four digits?

Run# changes as the number of runs assigned increases. At this point in the
year we're usually in the 1,000 range. Whichever is easier to use is the one
to go with. I prefer 4 digits personally, but whichever is easier to program
is the one to use.

What worksheet in the E2APBX1897DV6-13-07 workbook contains the data
you want (or is there only one sheet)?

There is only one sheet and its named "Sheet 1." E2APBX1897DV6-13-07 was
what I used as a name reference so you could see how its broken down. The
workbook's are actually called (by us) TPQ forms.

Is the date format always m-d-yy?

Yes it is.

How do you determine, when doing this manually, which workbooks are
new?

Basically I look at the workbook title. The (date being at the end,
E2APBX1897DV6-13-07 ) is what I look for. But this date is also found
in cell D1 of the worksheet.

Does one of the cells "B1,D1,F4,D4,E4,K4,I4,J4 and K1" contain
the run number?

Yes. The cells signify the following in this format (TPQ form
cell#)=Signifance=(Trend Log cell#):
B1=Run#=A6, D1=Date=B6, F4=RSQ=C6, D4=Slope=D6, E4=Y-Int=E6, K4=RSQ=F6,
I4=Slope=G6, J4=Y-Int=H6, and K1=Instrument name=I6. Now you'll notice that I
have two sets of R squared, slope and Y-intercept values. The first set is
for our chromosome breakpoint test, the other set is for the GAP DH test.

Just so you know, what we do here at the childrens hospital is test our
patients genes for errors and translocations that result in cancer. We track
EVERYTHING and keep detailed logs on our instruments and tests to make sure
these kids get the best possible treatment. For my own part I greatly
appreciate the time and effort you're putting into this, I know my fellow
employees (who use these sheets daily) will appreciate this, but the ones who
benefit the most are the children. Thank you for all your help.
Sincerely,
Barrett



"ilia" wrote:
Hi Barrett,

I'd like to try this, but some questions first.

Is run# always four digits?

What worksheet in the E2APBX1897DV6-13-07 workbook contains the data
you want (or is there only one sheet)? Is the date format always m-d-
yy?

How do you determine, when doing this manually, which workbooks are
new? Does one of the cells "B1,D1,F4,D4,E4,K4,I4,J4 and K1" contain
the run number?

On Dec 6, 11:47 am, ST Jude wrote:
I need some coding for the following:
I have a workbook titled "ALL Trend Log" that does trend calculations for
me. I manually enter the data to calculate from another workbook and want to
make this automated.

Here's how the process works: people in my department make a new workbook
from a template and enter their data into it, using the exact same cells
every time. They then save these workbooks into a Windows folder titled
"E2APBX" in the format of Testname,run#,initials,date so it looks like this
"E2APBX1897DV6-13-07". The data I want from this sheet is located in cells
B1,D1,F4,D4,E4,K4,I4,J4 and K1. I then take this data and put it into another
workbook titled "ALL Trend Log" (found in Windows folder "Quant. Assay Trend
Logs") under the worksheet titled "E2APBX" into the respective cells A6
through I6. I do this every couple days by finding the new workbooks and
entering the data into the "ALL Trend Log." How can I get this to perform
automatically?

Thanks in advance to everyone for their help and insight. I have come to
really like this board and its professionalism.
Barrett- Hide quoted text -

- Show quoted text -