Hello Thai,
In this Post, Post #14 ( http://www.excelfox.com/forum/showth...age2#post10705 ) I am explaining 3 files which I will send to you via our shared Email address
excellearning12@gmail.com
All files use the same code. The code is very similar to the very first one that I gave and explained.
http://www.excelfox.com/forum/showth...0669#post10669 http://www.excelfox.com/forum/showth...0670#post10670
All files have a code in the Worksheet code module, ( https://imgur.com/rx4EnWP https://imgur.com/dFUcwtP ) You should run the codes, as you have been doing so in F5
The purpose of this Post, Post #14 ( http://www.excelfox.com/forum/showth...age2#post10705 ) is to help you better understand how the code works.
For each file I have changed/manipulated the test data so that the code will give different results on different days. The File name includes the date on which you should run the code.
I hope that this will help explain the code to you.
The test data I have changed/manipulated such that there is maintenance due 3 days ahead of the date or the File name.
The only modification I have made is to comment out the Test line, so that the test date is removed:
Code:
' Let TdyDbl = CLng(DateSerial(2018, 3, 15)) - 3 ' To test only #####
Because of this modification, the current days date is now determined by this line:
Code:
Let TdyDbl = CLng(Now()) ' like 43233 for 13 May 2018
This returns the date for the day in which the code is run.
So I have changed/manipulated the data so that if you run the code on the date of the file, then certain data will meet the criteria for the reminder to be sent of the maintenance required in 3 days
File “RunOnTuesday5thJune.xlsm”
I have modified the data thus:
Row\Col |
A |
B |
C |
D |
E |
F |
G |
H |
I |
J |
K |
19 |
141 |
Heller |
1826 MK5 |
Reflow Oven |
A138 |
N/A |
N/A |
N/A |
N/A |
N/A |
N/A |
20 |
142 |
NISSAN |
MCU-112A331.V |
Forklift |
A139 |
N/A |
N/A |
N/A |
N/A |
11.05.2018 |
08.06.2018 |
21 |
142 |
NISSAN/yearly oil change and lube |
MCU-112A331.V |
Forklift |
A140 |
N/A |
N/A |
N/A |
N/A |
N/A |
N/A |
Worksheet: Equipment PM
So , as 08.06.2018 is 3 days ahead of 5th June , then ,
If you run this file on Tuesday 5th June, then you should you should receive per EMail the appropriate info for the due maintenance on 8th June.
142 NISSAN MCU-112A331.V Forklift A139
If you wish to test the code on any other day than 5th June, then add this test line:
Code:
Let TdyDbl = CLng(DateSerial(2018, 6, 5)) ' - 'To test only for Tuesday5thMay =43256
_.________________________________________________ ________________________
File: RunOnWednesday6thJune.xlsm
I have modified the data thus:
Row\Col |
F |
G |
H |
I |
J |
K |
18 |
06.04.2018 |
13.04.2018 |
N/A |
N/A |
N/A |
N/A |
19 |
N/A |
N/A |
12.05.2018 |
09.06.2018 |
N/A |
N/A |
20 |
N/A |
N/A |
N/A |
N/A |
12.05.2018 |
09.06.2018 |
21 |
N/A |
N/A |
N/A |
N/A |
N/A |
N/A |
Worksheet: Equipment PM
So , as 09.06.2018 is 3 days ahead of 6th June , then ,
If you run this file on Wednesday 6thJune May, then you should you should receive per EMail the appropriate info for the due maintenance on 8th June.
141 Heller 1826 MK5 Reflow Oven A138
142 NISSAN MCU-112A331.V Forklift A139
If you wish to test the code on any other day than 6th June, then add this test line:
Code:
Let TdyDbl = CLng(DateSerial(2018, 6, 6)) ' - 'To test only for Wednesday6thJune =43257
_.________________________________________________ ________
File RunOnThursday7thJune.xlsm
I have modified the data thus:
Row\Col |
E |
F |
G |
H |
I |
J |
K |
17 |
A136 |
N/A |
N/A |
N/A |
N/A |
N/A |
N/A |
18 |
A137 |
03.06.2018 |
10.06.2018 |
N/A |
N/A |
N/A |
N/A |
19 |
A138 |
N/A |
N/A |
13.05.2018 |
10.06.2018 |
N/A |
N/A |
20 |
A139 |
N/A |
N/A |
N/A |
N/A |
13.05.2018 |
10.06.2018 |
21 |
A140 |
N/A |
N/A |
N/A |
N/A |
N/A |
N/A |
Worksheet: Equipment PM
Because of the modifications, we have a Weekly, a Quarterly, and a Monthly maintenance due on 10 June.
So if you run the code 3 days earlier, on the 7th June, then you should receive the appropriate info per Email, thus:
137 Juki K3 Screen printer A137
141 Heller 1826 MK5 Reflow Oven A138
142 NISSAN MCU-112A331.V Forklift A139
gmailInputRunOnThursday7thJune bw table.JPG : https://imgur.com/Dh38G4O
gmailInputRunOnThursday7thJune bw table.JPG
If you wish to test this code on a day other than Thursday, 7th June, then add this code line:
Code:
Let TdyDbl = CLng(DateSerial(2018, 6, 7)) ' - 'To test only for Thursday7thJune =43258
_.________________________________________________ _-
See how you get on.
Please report back to me.
I will post again in a few days.
Alan
_.__________________________
The three files, RunOnTusday5thJune.xlsm , RunOnWednesday6thJune.xlsm , RunOnThursday7thJune.xlsm , I will send now via our shared Email, excellearning12@gmail.com
Bookmarks