VFP9SP2 & Excel automation -- how to enter this formula into a cell programatically from VFP?
See screenshot: https://www.screencast.com/t/wzLweduy General scenario: 2 input datetime values, with output being the native Excel command NetworkDays result on those 2 cells using 9 a.m. to 5:30 p.m. bounds, giving me the time elapsed between DateTime1 and DateTime2.
I want to supply a start and end time and use Excel's native NetworkDays function to return the length of time between when we received a ticket and when we responded. So basically I just want to use Excel's formula/calculation rather than recreate this in the Fox. Every time I tried to set it manually from the VFP command window, it failed. See screenshot with my attempts: https://www.screencast.com/t/BtzVOQLu
My current workaround is to have a simple worksheet with the formula predefined in a cell and just populate the input datetimes and then grab the resulting calculation from that NetworkDays(..) cell. I feel like that's a kludge though, and I'd rather just invoke Excel and programatically do it raw.
Your thoughts on how to best proceed for this task?
tia, --Mike
I forgot to post the formula in text here:
=(NETWORKDAYS(A2,B2)-1)*("17:30"-"9:00")+MOD(B2,1)-MOD(A2,1)
I can't get that entered programatically from VFP.
On 2018-10-15 17:05, mbsoftwaresolutions@mbsoftwaresolutions.com wrote:
See screenshot: https://www.screencast.com/t/wzLweduy General scenario: 2 input datetime values, with output being the native Excel command NetworkDays result on those 2 cells using 9 a.m. to 5:30 p.m. bounds, giving me the time elapsed between DateTime1 and DateTime2.
I want to supply a start and end time and use Excel's native NetworkDays function to return the length of time between when we received a ticket and when we responded. So basically I just want to use Excel's formula/calculation rather than recreate this in the Fox. Every time I tried to set it manually from the VFP command window, it failed. See screenshot with my attempts: https://www.screencast.com/t/BtzVOQLu
My current workaround is to have a simple worksheet with the formula predefined in a cell and just populate the input datetimes and then grab the resulting calculation from that NetworkDays(..) cell. I feel like that's a kludge though, and I'd rather just invoke Excel and programatically do it raw.
Your thoughts on how to best proceed for this task?
tia, --Mike
ox.Cells(yourrow,yourcolumn).Formula = [=(NETWORKDAYS(A2,B2)-1)*("17:30"-"9:00")+MOD(B2,1)-MOD(A2,1)]
Fred
On Mon, Oct 15, 2018 at 2:06 PM mbsoftwaresolutions@mbsoftwaresolutions.com wrote:
I forgot to post the formula in text here:
=(NETWORKDAYS(A2,B2)-1)*("17:30"-"9:00")+MOD(B2,1)-MOD(A2,1)
I can't get that entered programatically from VFP.
On 2018-10-15 17:05, mbsoftwaresolutions@mbsoftwaresolutions.com wrote:
See screenshot: https://www.screencast.com/t/wzLweduy General scenario: 2 input datetime values, with output being the native Excel command NetworkDays result on those 2 cells using 9 a.m. to 5:30 p.m. bounds, giving me the time elapsed between DateTime1 and DateTime2.
I want to supply a start and end time and use Excel's native NetworkDays function to return the length of time between when we received a ticket and when we responded. So basically I just want to use Excel's formula/calculation rather than recreate this in the Fox. Every time I tried to set it manually from the VFP command window, it failed. See screenshot with my attempts: https://www.screencast.com/t/BtzVOQLu
My current workaround is to have a simple worksheet with the formula predefined in a cell and just populate the input datetimes and then grab the resulting calculation from that NetworkDays(..) cell. I feel like that's a kludge though, and I'd rather just invoke Excel and programatically do it raw.
Your thoughts on how to best proceed for this task?
tia, --Mike
[excessive quoting removed by server]
Thanks, Fred. I was so close!
On 2018-10-15 17:18, Fred Taylor wrote:
ox.Cells(yourrow,yourcolumn).Formula = [=(NETWORKDAYS(A2,B2)-1)*("17:30"-"9:00")+MOD(B2,1)-MOD(A2,1)]
Fred
On Mon, Oct 15, 2018 at 2:06 PM mbsoftwaresolutions@mbsoftwaresolutions.com wrote:
I forgot to post the formula in text here:
=(NETWORKDAYS(A2,B2)-1)*("17:30"-"9:00")+MOD(B2,1)-MOD(A2,1)
I can't get that entered programatically from VFP.
-
Fred Taylor -
mbsoftwaresolutions＠mbsoftwaresolutions.com