jujubeans69
New Member
- Joined
- Aug 17, 2012
- Messages
- 43
Hi all. I currently have files that I track M-F, and some files come in on the weekend. I've been using a macro for a while, but have never been able to tweak it correctly to get the right counts. Below is a sample of files received, and then the macro used to get a count. Shown in orange are files received on the weekend, but are counted on Monday's date. For the most part, all other files are "standard" with 3 coming per day, M-F.
05/06/2013 05:38 AM 108,773,369 100296.20130504120418.zip
05/06/2013 05:39 AM 149,015,522 100296.20130505104307.zip
05/06/2013 05:40 AM 66,485,336 100296.20130506010104.zip
05/06/2013 08:08 AM 26,646,392 100296.20130506063449.zip
05/06/2013 03:48 PM 28,126,364 100296.20130506151928.zip
05/07/2013 01:22 AM 36,114,146 100296.20130507004941.zip
05/07/2013 07:05 AM 346,444 100296.20130507063117.zip
05/07/2013 03:13 PM 173,273,267 100296.20130507144618.zip
05/08/2013 12:57 AM 159,487 100296.20130508003649.zip
05/08/2013 06:51 AM 41,235,506 100296.20130508063742.zip
05/08/2013 03:02 PM 43,185,845 100296.20130508142424.zip
05/09/2013 12:15 AM 1,841,861 100296.20130509001029.zip
05/09/2013 07:16 AM 26,883,396 100296.20130509065859.zip
05/09/2013 04:23 PM 79,716,102 100296.20130509155314.zip
05/10/2013 12:52 AM 26,392,791 100296.20130510002627.zip
05/10/2013 07:06 AM 55,229,309 100296.20130510062450.zip
05/10/2013 02:58 PM 52,153,832 100296.20130510142423.zip
Below is the macro being used. It searches the folder \test for the files, and MOST of the naming format is the same (as shown above), except the last 6 digits before the .zip.
Dim srch2 As String, found2 As Integer, i2 As Integer
For i2 = 5 To 9
srch2 = "\\mb05a\test\" & Range("A" & i2) & "." & Format(Range("C1"), "yyyymmdd") & "??????" & ".zip"
found2 = 0
If Dir(srch2) <> "" Then 'is there such a file?
Do
found2 = found2 + 1
Loop While Dir() <> "" 'are there more such files
End If
Sheets("AMR").Range("E" & i2) = found2
Next i2
My sheet is set up like this. As there is no A, B, C, etc. in the actual file names, it puts 3 for each count in column E. What I NEED it to do is count each file separately, but I do not know how to factor in the "??????" digits to make each row count as 1 file instead of 3. I would LIKE to factor in the date the files come in (FIFO) OR simply use the last 6 digits (as they appear to be in ascending order, with lowest received 1st, mid received 2nd, etc.). Sounds confusing? In summary, I need the 1st file received to be counted as A-cut, 2nd file as B-cut, 3rd file as C-cut, etc. Using Monday's date, I SHOULD have cuts A-F, and any date Tue-Fri, SHOULD have cuts A-B. I hope this explanation and the examples were thorough enough for someone to help me tweak my current macro, or even simplify it. Thanks!!!
<tbody>
</tbody>
05/06/2013 05:38 AM 108,773,369 100296.20130504120418.zip
05/06/2013 05:39 AM 149,015,522 100296.20130505104307.zip
05/06/2013 05:40 AM 66,485,336 100296.20130506010104.zip
05/06/2013 08:08 AM 26,646,392 100296.20130506063449.zip
05/06/2013 03:48 PM 28,126,364 100296.20130506151928.zip
05/07/2013 01:22 AM 36,114,146 100296.20130507004941.zip
05/07/2013 07:05 AM 346,444 100296.20130507063117.zip
05/07/2013 03:13 PM 173,273,267 100296.20130507144618.zip
05/08/2013 12:57 AM 159,487 100296.20130508003649.zip
05/08/2013 06:51 AM 41,235,506 100296.20130508063742.zip
05/08/2013 03:02 PM 43,185,845 100296.20130508142424.zip
05/09/2013 12:15 AM 1,841,861 100296.20130509001029.zip
05/09/2013 07:16 AM 26,883,396 100296.20130509065859.zip
05/09/2013 04:23 PM 79,716,102 100296.20130509155314.zip
05/10/2013 12:52 AM 26,392,791 100296.20130510002627.zip
05/10/2013 07:06 AM 55,229,309 100296.20130510062450.zip
05/10/2013 02:58 PM 52,153,832 100296.20130510142423.zip
Below is the macro being used. It searches the folder \test for the files, and MOST of the naming format is the same (as shown above), except the last 6 digits before the .zip.
Dim srch2 As String, found2 As Integer, i2 As Integer
For i2 = 5 To 9
srch2 = "\\mb05a\test\" & Range("A" & i2) & "." & Format(Range("C1"), "yyyymmdd") & "??????" & ".zip"
found2 = 0
If Dir(srch2) <> "" Then 'is there such a file?
Do
found2 = found2 + 1
Loop While Dir() <> "" 'are there more such files
End If
Sheets("AMR").Range("E" & i2) = found2
Next i2
My sheet is set up like this. As there is no A, B, C, etc. in the actual file names, it puts 3 for each count in column E. What I NEED it to do is count each file separately, but I do not know how to factor in the "??????" digits to make each row count as 1 file instead of 3. I would LIKE to factor in the date the files come in (FIFO) OR simply use the last 6 digits (as they appear to be in ascending order, with lowest received 1st, mid received 2nd, etc.). Sounds confusing? In summary, I need the 1st file received to be counted as A-cut, 2nd file as B-cut, 3rd file as C-cut, etc. Using Monday's date, I SHOULD have cuts A-F, and any date Tue-Fri, SHOULD have cuts A-B. I hope this explanation and the examples were thorough enough for someone to help me tweak my current macro, or even simplify it. Thanks!!!
20130505 | 20130506 | 05/07/13 | MB05A\ftproot\ | MB05A\ftproot\ | |||
Lockbox | Xmit Time | Cut & Site | (Monday) | (x COR / x PAY / 1 ZIP) | |||
| 100296 | A | V45 | 3 | ||||
| 100296 | B | V45 | 3 | ||||
| 100296 | C | V45 | 3 | ||||
| 100296 | D | V45 | |||||
| 100296 | F | V45 |
<tbody>
</tbody>
