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

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

Barrett,

Put this in a module in your "All Trend Log" file and run it.


Sub test()
Dim src As Variant, fName As String, wb As Workbook

src = Array("B1","D1","F4","D4","E4","K4","I4","J4","K1" )
fName = Application.GetOpenFilename()

If fName < "False" Then
Set wb = Workbooks.Open(fName)
Else
Exit Sub
End If

For i = 0 To 8
wb.Sheets(1).Range(src(i)).Copy _
ThisWorkbook.Sheets("E2APBX").Cells(6, i + 1)
Next

wb.Close savechanges:=False
End Sub


--
Dan


On Dec 6, 10: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

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

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 -

Thanks again for doing this. I REALLY appreciate it!
One: There will be many more E2APBX files in that folder. Their names are
how we distinguish them. The "E2APBX" part of the name will always be the
same, but the run number, initials and date will always change. I will want
to keep adding rows (A7:B7, A8:B8, etc) up to 15 rows. Then I have the trend
log calculations and I believe they take up 3 rows so the grouping will need
to start again with the E2APBX data at A24 to I24, A25 to I25, et cetera. I'm
at home currently with a sick toddler so I'm going to have my supervisor send
me a copy of the worksheet so I can give you exact cell locations for the
trend log calculations. Accountant... scientist... we're number people any
way you slice it ;-P
Two: The date comparison is just to tell me where I left off last time I
updated the Trend Log workbook. I don't do It every day so when I et back
into it I just look at the trend log workbook and see what the last date I
entered was. Then I go back to the folder with all the E2APBX workbooks, find
the next date after I left off and bring it up to current date.

"ilia" wrote:

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 -

OK,

Here's the first version. Please try this on a copy of your
workbooks, if you have a chance. Paste the into a standard module
(does not have to be ALL Trend Log), and adjust the paths
accordingly. I'll come up with some test data that meet your criteria
and post back a final version with any corrections and error handling
code.

-Ilia


'begin code
Option Explicit

Public Sub lookForFiles()
Const strRunsPath As String = "C:\E2APBBX\"
Const strTrendPath As String = "C:\Quant. Assay Trend Logs\"
Const strTrendFileName As String = "ALL Trend Log.xls"
Const strWshName As String = "E2APBX"
Const strDataRange As String = "B1,D1,F4,D4,E4,K4,I4,J4,K1"

Dim currFileName As String

Dim wshTemp As Excel.Worksheet
Dim wkbTrend As Excel.Workbook
Dim wshTrend As Excel.Worksheet
Dim iFileCount As Long

Dim wkbData As Excel.Workbook
Dim wshData As Excel.Worksheet
Dim rngData As Excel.Range

Dim iCellCount As Long

Dim firstNewRow As Long
Dim iCurrFile As Long

Dim lastDate As Date

Application.ScreenUpdating = False

If ThisWorkbook.Name = strTrendFileName Then
Set wkbTrend = ThisWorkbook
Else
Set wkbTrend = _
Application.Workbooks.Open(strTrendPath & _
strTrendFileName)
End If

' add a temporary sheet to keep track of new data files
Set wshTrend = wkbTrend.Worksheets(strWshName)
Set wshTemp = wkbTrend.Worksheets.Add
wshTemp.Visible = xlSheetVeryHidden

' get the latest date of last run entered
lastDate = getLastDate(wshTrend)

' look for new files
currFileName = Dir(strRunsPath)
With wshTemp
Do While currFileName < ""
If (fileNameMatches(currFileName) And _
extractRunDate(currFileName) lastDate) Then
iFileCount = iFileCount + 1
.Cells(iFileCount, 1).Value = currFileName
End If
currFileName = Dir()
Loop

firstNewRow = getFirstNewRow(wshTrend)
For iCurrFile = 1 To iFileCount
' open each new file
Set wkbData = _
Application.Workbooks.Open(strRunsPath & _
.Cells(iCurrFile, 1))
Set wshData = wkbData.Worksheets("Sheet 1")

' process all cells in range
iCellCount = 0
For Each rngData In wshData.Range(strDataRange)
iCellCount = iCellCount + 1
wshTrend.Cells(firstNewRow, _
iCellCount).Value = rngData.Value
firstNewRow = firstNewRow + 1
Next rngData
wkbData.Close (False)
Next iCurrFile

Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
End With

Application.ScreenUpdating = True
End Sub

Private Function getFirstNewRow(ByRef wsh As _
Excel.Worksheet) As Long
Dim iCount As Long
Dim firstNewRow As Long

firstNewRow = wsh.UsedRange.Rows.Count + 1

' leave 3 rows blank every 15 rows
For iCount = 21 To firstNewRow Step 15
If firstNewRow = iCount Then
firstNewRow = firstNewRow + 3
ElseIf (firstNewRow = iCount + 1) Then
firstNewRow = firstNewRow + 2
ElseIf (firstNewRow = iCount + 2) Then
firstNewRow = firstNewRow + 1
End If
Next iCount

getFirstNewRow = firstNewRow
End Function

Private Function getLastDate(ByRef wsh As _
Excel.Worksheet) As Date
' using a separate function for this
' in case we need to use a cell-by-cell
' algorithm to find latest date, instead of Max
getLastDate = _
Application.WorksheetFunction.Max( _
wsh.Range("$B:$B"))
End Function

Private Function fileNameMatches(fileName As String) _
As Boolean
' file name must start with E2APBX
' run # may be anywhere from 1 to 9999999
' initials may be 2 or 3 characters
' date must be m-d-yy format
' 01-01-07 is invalid because of leading zeroes
' file extension must be .xls, .xlsx, or .xlsm
Const strPattern As String = _
"^E2APBX" & _
"[1-9][0-9]{0,6}" & _
"[A-Z]{2,3}" & _
"[1-9][012]{0,1}-[1-9][0-9]{0,1}-[0-9][0-9]" & _
"[.]xls[xm]{0,1}$"

'VBScript_RegExp_10.RegExp
Dim objRegExp As Object

'New VBScript_RegExp_10.RegExp
Set objRegExp = CreateObject("vbscript.RegExp")

With objRegExp
.Pattern = strPattern
.IgnoreCase = True
fileNameMatches = .test(fileName)
End With
End Function

Private Function extractRunDate(fileName As String) _
As Date
' sample file name: "E2APBX1897DV6-13-07.xls"

Dim iDateBegins As Integer
Dim iDateEnds As Integer

Dim iPointer As Integer

iDateEnds = InStrRev(fileName, ".") - 1
iDateBegins = InStr(fileName, "-") - 2
If Not isDigit(Mid(fileName, iDateBegins, 1)) Then
iDateBegins = iDateBegins + 1
End If

extractRunDate = Mid(fileName, iDateBegins, _
iDateEnds - iDateBegins)
End Function

Private Function isDigit(dig As String) As Boolean
isDigit = (Asc(dig) = Asc(0) And Asc(dig) <= Asc(9))
End Function
' end code


On Dec 7, 7:10 am, ST Jude wrote:
Thanks again for doing this. I REALLY appreciate it!
One: There will be many more E2APBX files in that folder. Their names are
how we distinguish them. The "E2APBX" part of the name will always be the
same, but the run number, initials and date will always change. I will want
to keep adding rows (A7:B7, A8:B8, etc) up to 15 rows. Then I have the trend
log calculations and I believe they take up 3 rows so the grouping will need
to start again with the E2APBX data at A24 to I24, A25 to I25, et cetera. I'm
at home currently with a sick toddler so I'm going to have my supervisor send
me a copy of the worksheet so I can give you exact cell locations for the
trend log calculations. Accountant... scientist... we're number people any
way you slice it ;-P
Two: The date comparison is just to tell me where I left off last time I
updated the Trend Log workbook. I don't do It every day so when I et back
into it I just look at the trend log workbook and see what the last date I
entered was. Then I go back to the folder with all the E2APBX workbooks, find
the next date after I left off and bring it up to current date.



"ilia" wrote:
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 -- Hide quoted text -

- Show quoted text -

Hi Ilia,
I work with Barrett and have been trying the code. It is working but needs
some added code. When it skips 3 rows that is because in those three rose we
have formulas built in that is calculating the mean, standard deviation and
%CV of the data. When i run the macro it starts putting the data below these
formulas instead of the first 15 rows above them.,


Thanks for all the help,
drose

"ilia" wrote:

OK,

Here's the first version. Please try this on a copy of your
workbooks, if you have a chance. Paste the into a standard module
(does not have to be ALL Trend Log), and adjust the paths
accordingly. I'll come up with some test data that meet your criteria
and post back a final version with any corrections and error handling
code.

-Ilia


'begin code
Option Explicit

Public Sub lookForFiles()
Const strRunsPath As String = "C:\E2APBBX\"
Const strTrendPath As String = "C:\Quant. Assay Trend Logs\"
Const strTrendFileName As String = "ALL Trend Log.xls"
Const strWshName As String = "E2APBX"
Const strDataRange As String = "B1,D1,F4,D4,E4,K4,I4,J4,K1"

Dim currFileName As String

Dim wshTemp As Excel.Worksheet
Dim wkbTrend As Excel.Workbook
Dim wshTrend As Excel.Worksheet
Dim iFileCount As Long

Dim wkbData As Excel.Workbook
Dim wshData As Excel.Worksheet
Dim rngData As Excel.Range

Dim iCellCount As Long

Dim firstNewRow As Long
Dim iCurrFile As Long

Dim lastDate As Date

Application.ScreenUpdating = False

If ThisWorkbook.Name = strTrendFileName Then
Set wkbTrend = ThisWorkbook
Else
Set wkbTrend = _
Application.Workbooks.Open(strTrendPath & _
strTrendFileName)
End If

' add a temporary sheet to keep track of new data files
Set wshTrend = wkbTrend.Worksheets(strWshName)
Set wshTemp = wkbTrend.Worksheets.Add
wshTemp.Visible = xlSheetVeryHidden

' get the latest date of last run entered
lastDate = getLastDate(wshTrend)

' look for new files
currFileName = Dir(strRunsPath)
With wshTemp
Do While currFileName < ""
If (fileNameMatches(currFileName) And _
extractRunDate(currFileName) lastDate) Then
iFileCount = iFileCount + 1
.Cells(iFileCount, 1).Value = currFileName
End If
currFileName = Dir()
Loop

firstNewRow = getFirstNewRow(wshTrend)
For iCurrFile = 1 To iFileCount
' open each new file
Set wkbData = _
Application.Workbooks.Open(strRunsPath & _
.Cells(iCurrFile, 1))
Set wshData = wkbData.Worksheets("Sheet 1")

' process all cells in range
iCellCount = 0
For Each rngData In wshData.Range(strDataRange)
iCellCount = iCellCount + 1
wshTrend.Cells(firstNewRow, _
iCellCount).Value = rngData.Value
firstNewRow = firstNewRow + 1
Next rngData
wkbData.Close (False)
Next iCurrFile

Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
End With

Application.ScreenUpdating = True
End Sub

Private Function getFirstNewRow(ByRef wsh As _
Excel.Worksheet) As Long
Dim iCount As Long
Dim firstNewRow As Long

firstNewRow = wsh.UsedRange.Rows.Count + 1

' leave 3 rows blank every 15 rows
For iCount = 21 To firstNewRow Step 15
If firstNewRow = iCount Then
firstNewRow = firstNewRow + 3
ElseIf (firstNewRow = iCount + 1) Then
firstNewRow = firstNewRow + 2
ElseIf (firstNewRow = iCount + 2) Then
firstNewRow = firstNewRow + 1
End If
Next iCount

getFirstNewRow = firstNewRow
End Function

Private Function getLastDate(ByRef wsh As _
Excel.Worksheet) As Date
' using a separate function for this
' in case we need to use a cell-by-cell
' algorithm to find latest date, instead of Max
getLastDate = _
Application.WorksheetFunction.Max( _
wsh.Range("$B:$B"))
End Function

Private Function fileNameMatches(fileName As String) _
As Boolean
' file name must start with E2APBX
' run # may be anywhere from 1 to 9999999
' initials may be 2 or 3 characters
' date must be m-d-yy format
' 01-01-07 is invalid because of leading zeroes
' file extension must be .xls, .xlsx, or .xlsm
Const strPattern As String = _
"^E2APBX" & _
"[1-9][0-9]{0,6}" & _
"[A-Z]{2,3}" & _
"[1-9][012]{0,1}-[1-9][0-9]{0,1}-[0-9][0-9]" & _
"[.]xls[xm]{0,1}$"

'VBScript_RegExp_10.RegExp
Dim objRegExp As Object

'New VBScript_RegExp_10.RegExp
Set objRegExp = CreateObject("vbscript.RegExp")

With objRegExp
.Pattern = strPattern
.IgnoreCase = True
fileNameMatches = .test(fileName)
End With
End Function

Private Function extractRunDate(fileName As String) _
As Date
' sample file name: "E2APBX1897DV6-13-07.xls"

Dim iDateBegins As Integer
Dim iDateEnds As Integer

Dim iPointer As Integer

iDateEnds = InStrRev(fileName, ".") - 1
iDateBegins = InStr(fileName, "-") - 2
If Not isDigit(Mid(fileName, iDateBegins, 1)) Then
iDateBegins = iDateBegins + 1
End If

extractRunDate = Mid(fileName, iDateBegins, _
iDateEnds - iDateBegins)
End Function

Private Function isDigit(dig As String) As Boolean
isDigit = (Asc(dig) = Asc(0) And Asc(dig) <= Asc(9))
End Function
' end code


On Dec 7, 7:10 am, ST Jude wrote:
Thanks again for doing this. I REALLY appreciate it!
One: There will be many more E2APBX files in that folder. Their names are
how we distinguish them. The "E2APBX" part of the name will always be the
same, but the run number, initials and date will always change. I will want
to keep adding rows (A7:B7, A8:B8, etc) up to 15 rows. Then I have the trend
log calculations and I believe they take up 3 rows so the grouping will need
to start again with the E2APBX data at A24 to I24, A25 to I25, et cetera. I'm
at home currently with a sick toddler so I'm going to have my supervisor send
me a copy of the worksheet so I can give you exact cell locations for the
trend log calculations. Accountant... scientist... we're number people any
way you slice it ;-P
Two: The date comparison is just to tell me where I left off last time I
updated the Trend Log workbook. I don't do It every day so when I et back
into it I just look at the trend log workbook and see what the last date I
entered was. Then I go back to the folder with all the E2APBX workbooks, find
the next date after I left off and bring it up to current date.



"ilia" wrote:
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

This is the part I don't quite understand. What if there are more
files than will fit in the 15 rows of data? I don't understand how to
pick among them which will be above the summary rows. From what I
understood earlier, the data skips 3 rows every 15 rows and
continues. This is what Barrett wrote:

I will want to keep adding rows (A7:B7, A8:B8, etc) up to 15 rows. Then I have the trend log calculations and I believe they take up 3 rows so the grouping will need to start again with the E2APBX data at A24 to I24, A25 to I25, et cetera.

Feel free to e-mail me regarding this.


On Dec 12, 8:28 am, drose wrote:
Hi Ilia,
I work with Barrett and have been trying the code. It is working but needs
some added code. When it skips 3 rows that is because in those three rose we
have formulas built in that is calculating the mean, standard deviation and
%CV of the data. When i run the macro it starts putting the data below these
formulas instead of the first 15 rows above them.,

Thanks for all the help,
drose



"ilia" wrote:
OK,

Here's the first version. Please try this on a copy of your
workbooks, if you have a chance. Paste the into a standard module
(does not have to be ALL Trend Log), and adjust the paths
accordingly. I'll come up with some test data that meet your criteria
and post back a final version with any corrections and error handling
code.

-Ilia

'begin code
Option Explicit

Public Sub lookForFiles()
Const strRunsPath As String = "C:\E2APBBX\"
Const strTrendPath As String = "C:\Quant. Assay Trend Logs\"
Const strTrendFileName As String = "ALL Trend Log.xls"
Const strWshName As String = "E2APBX"
Const strDataRange As String = "B1,D1,F4,D4,E4,K4,I4,J4,K1"

Dim currFileName As String

Dim wshTemp As Excel.Worksheet
Dim wkbTrend As Excel.Workbook
Dim wshTrend As Excel.Worksheet
Dim iFileCount As Long

Dim wkbData As Excel.Workbook
Dim wshData As Excel.Worksheet
Dim rngData As Excel.Range

Dim iCellCount As Long

Dim firstNewRow As Long
Dim iCurrFile As Long

Dim lastDate As Date

Application.ScreenUpdating = False

If ThisWorkbook.Name = strTrendFileName Then
Set wkbTrend = ThisWorkbook
Else
Set wkbTrend = _
Application.Workbooks.Open(strTrendPath & _
strTrendFileName)
End If

' add a temporary sheet to keep track of new data files
Set wshTrend = wkbTrend.Worksheets(strWshName)
Set wshTemp = wkbTrend.Worksheets.Add
wshTemp.Visible = xlSheetVeryHidden

' get the latest date of last run entered
lastDate = getLastDate(wshTrend)

' look for new files
currFileName = Dir(strRunsPath)
With wshTemp
Do While currFileName < ""
If (fileNameMatches(currFileName) And _
extractRunDate(currFileName) lastDate) Then
iFileCount = iFileCount + 1
.Cells(iFileCount, 1).Value = currFileName
End If
currFileName = Dir()
Loop

firstNewRow = getFirstNewRow(wshTrend)
For iCurrFile = 1 To iFileCount
' open each new file
Set wkbData = _
Application.Workbooks.Open(strRunsPath & _
.Cells(iCurrFile, 1))
Set wshData = wkbData.Worksheets("Sheet 1")

' process all cells in range
iCellCount = 0
For Each rngData In wshData.Range(strDataRange)
iCellCount = iCellCount + 1
wshTrend.Cells(firstNewRow, _
iCellCount).Value = rngData.Value
firstNewRow = firstNewRow + 1
Next rngData
wkbData.Close (False)
Next iCurrFile

Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
End With

Application.ScreenUpdating = True
End Sub

Private Function getFirstNewRow(ByRef wsh As _
Excel.Worksheet) As Long
Dim iCount As Long
Dim firstNewRow As Long

firstNewRow = wsh.UsedRange.Rows.Count + 1

' leave 3 rows blank every 15 rows
For iCount = 21 To firstNewRow Step 15
If firstNewRow = iCount Then
firstNewRow = firstNewRow + 3
ElseIf (firstNewRow = iCount + 1) Then
firstNewRow = firstNewRow + 2
ElseIf (firstNewRow = iCount + 2) Then
firstNewRow = firstNewRow + 1
End If
Next iCount

getFirstNewRow = firstNewRow
End Function

Private Function getLastDate(ByRef wsh As _
Excel.Worksheet) As Date
' using a separate function for this
' in case we need to use a cell-by-cell
' algorithm to find latest date, instead of Max
getLastDate = _
Application.WorksheetFunction.Max( _
wsh.Range("$B:$B"))
End Function

Private Function fileNameMatches(fileName As String) _
As Boolean
' file name must start with E2APBX
' run # may be anywhere from 1 to 9999999
' initials may be 2 or 3 characters
' date must be m-d-yy format
' 01-01-07 is invalid because of leading zeroes
' file extension must be .xls, .xlsx, or .xlsm
Const strPattern As String = _
"^E2APBX" & _
"[1-9][0-9]{0,6}" & _
"[A-Z]{2,3}" & _
"[1-9][012]{0,1}-[1-9][0-9]{0,1}-[0-9][0-9]" & _
"[.]xls[xm]{0,1}$"

'VBScript_RegExp_10.RegExp
Dim objRegExp As Object

'New VBScript_RegExp_10.RegExp
Set objRegExp = CreateObject("vbscript.RegExp")

With objRegExp
.Pattern = strPattern
.IgnoreCase = True
fileNameMatches = .test(fileName)
End With
End Function

Private Function extractRunDate(fileName As String) _
As Date
' sample file name: "E2APBX1897DV6-13-07.xls"

Dim iDateBegins As Integer
Dim iDateEnds As Integer

Dim iPointer As Integer

iDateEnds = InStrRev(fileName, ".") - 1
iDateBegins = InStr(fileName, "-") - 2
If Not isDigit(Mid(fileName, iDateBegins, 1)) Then
iDateBegins = iDateBegins + 1
End If

extractRunDate = Mid(fileName, iDateBegins, _
iDateEnds - iDateBegins)
End Function

Private Function isDigit(dig As String) As Boolean
isDigit = (Asc(dig) = Asc(0) And Asc(dig) <= Asc(9))
End Function
' end code

On Dec 7, 7:10 am, ST Jude wrote:
Thanks again for doing this. I REALLY appreciate it!
One: There will be many more E2APBX files in that folder. Their names are
how we distinguish them. The "E2APBX" part of the name will always be the
same, but the run number, initials and date will always change. I will want
to keep adding rows (A7:B7, A8:B8, etc) up to 15 rows. Then I have the trend
log calculations and I believe they take up 3 rows so the grouping will need
to start again with the E2APBX data at A24 to I24, A25 to I25, et cetera. I'm
at home currently with a sick toddler so I'm going to have my supervisor send
me a copy of the worksheet so I can give you exact cell locations for the
trend log calculations. Accountant... scientist... we're number people any
way you slice it ;-P
Two: The date comparison is just to tell me where I left off last time I
updated the Trend Log workbook. I don't do It every day so when I et back
into it I just look at the trend log workbook and see what the last date I
entered was. Then I go back to the folder with all the E2APBX workbooks, find
the next date after I left off and bring it up to current date.

"ilia" wrote:
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- Hide quoted text -

- Show quoted text -...

read more