Contact Centers, CRM Customer Engagement

With CMS Designer Reports using tab delimited exports and time (duration) fields formatted as h:mm:ss,  CMS exports the time as 'tab & :mm:ss' if value is less than an hour. Excel does not 'recognize this as time. Logic in this code snippet easily corrects this for the entire file when using excel VBA to ingest and report on the data. 

The Power BI team presently uses Designer Report exports with duration in seconds, and can't handle this h:mm:ss format. CMS is temperamental and sometimes scripted reports fail. We want to cease producing duplicate exports in seconds and avoid inter-positioning the Excel reporting in front of the PBI reporting. Can anyone suggest a way for PBI to ingest the file as exported in the h:mm:ss format, albeit sometimes without the 'h'. 

'Duration format example:
'Item                                         Align        Format
'STARTTIME   Time                Right        13:00
'ABNTIME       ABNTIME        Right        4:05:10
'ACDTIME       ACDTIME        Right        4:05:10
'ACWINTIME  ACWINTIME    Right        4:05:10
'etc. This format is used for all Time Duration fields.

'Snippet of code to fix format for Excel
'**********
private strbuff as string
private file1 as string            'Tab delimited file exported from the Designer Report
private macpath as string     'path of this macro

sub snippet
macpath=thisworkbook.path
strBuff = Space$(FileLen(file1))    'sizes strbuff to the size of the input file
Open file1 For Binary As #1        'open file in binary mode 
Get #1, 1, strBuff                  'stuffs entire file into strbuff
Close #1
'*****
'Clean up CMS time format in two steps
strBuff = Replace(strBuff, vbTab & ":00:00", vbTab)
strBuff = Replace(strBuff, vbTab & ":", vbTab & "0:")
'**************
'CMS outputs :mm:ss. Excel needs h:mm:ss so these two 'replace' statements delete all "tab & :00:00"; and convert "tab & :" to "vbtab & 0:" 
'(inserts a 0 in front of durations less than an hour. No change to durations of an hour or more.
'
'Un-comment to see if :mm:ss was corrected (this will overwrite the file)
'Open file1 For Output As #1
'Print #1, strBuff
'Close #1
end sub

With CMS Designer Reports using tab delimited exports and time (duration) fields formatted as h:mm:ss,  CMS exports the time as 'tab & :mm:ss' if value is less than an hour. Excel does not 'recognize this as time. Logic in this code snippet easily corrects this for the entire file when using excel VBA to ingest and report on the data. 

The Power BI team presently uses Designer Report exports with duration in seconds, and can't handle this h:mm:ss format. CMS is temperamental and sometimes scripted reports fail. We want to cease producing duplicate exports in seconds and avoid inter-positioning the Excel reporting in front of the PBI reporting. Can anyone suggest a way for PBI to ingest the file as exported in the h:mm:ss format, albeit sometimes without the 'h'. 

'Duration format example:
'Item                                         Align        Format
'STARTTIME   Time                Right        13:00
'ABNTIME       ABNTIME        Right        4:05:10
'ACDTIME       ACDTIME        Right        4:05:10
'ACWINTIME  ACWINTIME    Right        4:05:10
'etc. This format is used for all Time Duration fields.

'Snippet of code to fix format for Excel
'**********
private strbuff as string
private file1 as string            'Tab delimited file exported from the Designer Report
private macpath as string     'path of this macro

sub snippet
macpath=thisworkbook.path
strBuff = Space$(FileLen(file1))    'sizes strbuff to the size of the input file
Open file1 For Binary As #1        'open file in binary mode 
Get #1, 1, strBuff                  'stuffs entire file into strbuff
Close #1
'*****
'Clean up CMS time format in two steps
strBuff = Replace(strBuff, vbTab & ":00:00", vbTab)
strBuff = Replace(strBuff, vbTab & ":", vbTab & "0:")
'**************
'CMS outputs :mm:ss. Excel needs h:mm:ss so these two 'replace' statements delete all "tab & :00:00"; and convert "tab & :" to "vbtab & 0:" 
'(inserts a 0 in front of durations less than an hour. No change to durations of an hour or more.
'
'Un-comment to see if :mm:ss was corrected (this will overwrite the file)
'Open file1 For Output As #1
'Print #1, strBuff
'Close #1
end sub

With CMS Designer Reports using tab delimited exports and time (duration) fields formatted as h:mm:ss,  CMS exports the time as 'tab & :mm:ss' if value is less than an hour. Excel does not 'recognize this as time. Logic in this code snippet easily corrects this for the entire file when using excel VBA to ingest and report on the data. 

The Power BI team presently uses Designer Report exports with duration in seconds, and can't handle this h:mm:ss format. CMS is temperamental and sometimes scripted reports fail. We want to cease producing duplicate exports in seconds and avoid inter-positioning the Excel reporting in front of the PBI reporting. Can anyone suggest a way for PBI to ingest the file as exported in the h:mm:ss format, albeit sometimes without the 'h'. 

'Duration format example:
'Item                                         Align        Format
'STARTTIME   Time                Right        13:00
'ABNTIME       ABNTIME        Right        4:05:10
'ACDTIME       ACDTIME        Right        4:05:10
'ACWINTIME  ACWINTIME    Right        4:05:10
'etc. This format is used for all Time Duration fields.

'Snippet of code to fix format for Excel
'**********
private strbuff as string
private file1 as string            'Tab delimited file exported from the Designer Report
private macpath as string     'path of this macro

sub snippet
macpath=thisworkbook.path
strBuff = Space$(FileLen(file1))    'sizes strbuff to the size of the input file
Open file1 For Binary As #1        'open file in binary mode 
Get #1, 1, strBuff                  'stuffs entire file into strbuff
Close #1
'*****
'Clean up CMS time format in two steps
strBuff = Replace(strBuff, vbTab & ":00:00", vbTab)
strBuff = Replace(strBuff, vbTab & ":", vbTab & "0:")
'**************
'CMS outputs :mm:ss. Excel needs h:mm:ss so these two 'replace' statements delete all "tab & :00:00"; and convert "tab & :" to "vbtab & 0:" 
'(inserts a 0 in front of durations less than an hour. No change to durations of an hour or more.
'
'Un-comment to see if :mm:ss was corrected (this will overwrite the file)
'Open file1 For Output As #1
'Print #1, strBuff
'Close #1
end sub