title: How to export Working Time from MS Project to MS Excel?
body: <p>I have a Project file that contains products that are configured on different workstations that have working hours / day. I managed to export the Holiday/Exceptions through VBA/Macro but I need the working hours ex. 06:30 AM to 14:30 PM for each workstation. In the project file I can view this information by clicking <code>Project &gt; Change Working Time</code> - at this point I can select the workstation from the dropdown <code>For calendar</code> and there is a mini calendar where I can select a specific date - by clicking a date I can see the working hours on that day. I can also view this information by clicking the <code>Details</code> button.</p>
  <p>Is there any in built function that I can use to extract that data? Or is it possible to get that information via macro? I need this data to be extracted in a Excel file so I can later import it into an SQL database.</p>
  <p>The code that I use to extract the holidays/exceptions is the following(I copy pasted from a google search, it is not written by me, I am new to MS Project/VBA):</p>
  <pre><code>Option Explicit
  Sub CalendarWeekdays()
  
  Dim MyXL As Object
  Set MyXL = CreateObject(&quot;Excel.Application&quot;)
  Dim i As Integer, j As Integer
  Dim E As Exception
  Dim R As Resource
  Dim xlRng
  
  'open Excel, define workbook, and set column headers
  MyXL.Workbooks.Add
  MyXL.Visible = True
  MyXL.ActiveWorkbook.worksheets.Add.Name = &quot;Exception Report&quot;
  MyXL.ActiveWorkbook.worksheets(&quot;Exception Report&quot;).Activate
  Set xlRng = MyXL.activesheet.Range(&quot;A1&quot;)
  xlRng.Range(&quot;A1&quot;) = &quot;Proj Cal Holidays&quot;
  xlRng.Range(&quot;B1&quot;) = &quot;Start Date&quot;
  xlRng.Range(&quot;C1&quot;) = &quot;Finish Date&quot;
  xlRng.Range(&quot;E1&quot;) = &quot;Res Name&quot;
  xlRng.Range(&quot;F1&quot;) = &quot;Res Base Cal&quot;
  xlRng.Range(&quot;G1&quot;) = &quot;Base Cal Excep&quot;
  xlRng.Range(&quot;H1&quot;) = &quot;Start Date&quot;
  xlRng.Range(&quot;I1&quot;) = &quot;Finish Date&quot;
  xlRng.Range(&quot;K1&quot;) = &quot;Resource Name&quot;
  xlRng.Range(&quot;L1&quot;) = &quot;Res Excep&quot;
  xlRng.Range(&quot;M1&quot;) = &quot;Start Date&quot;
  xlRng.Range(&quot;N1&quot;) = &quot;Finish Date&quot;
  
  'First gather and export Project calendar exceptions
  i = 2
  If ActiveProject.Calendar.Exceptions.Count &gt; 0 Then
      For Each E In ActiveProject.Calendar.Exceptions
          xlRng.Range(&quot;A&quot; &amp; i) = E.Name
          xlRng.Range(&quot;B&quot; &amp; i) = E.Start
          xlRng.Range(&quot;C&quot; &amp; i) = E.Finish
          i = i + 1
      Next
  End If
  
  'Next, gather and export resource base calendar exceptions along with
  '   resource calendar exceptions
  i = 2
  For Each R In ActiveProject.Resources
      If Not R Is Nothing Then
          j = i
          If R.Type = pjResourceTypeWork Then
                  For Each E In R.Calendar.BaseCalendar.Exceptions
                      xlRng.Range(&quot;E&quot; &amp; i) = R.Name
                      xlRng.Range(&quot;F&quot; &amp; i) = R.Calendar.BaseCalendar.Name
                      xlRng.Range(&quot;G&quot; &amp; i) = E.Name
                      xlRng.Range(&quot;H&quot; &amp; i) = E.Start
                      xlRng.Range(&quot;I&quot; &amp; i) = E.Finish
                      i = i + 1
                  Next E
                  For Each E In R.Calendar.Exceptions
                      xlRng.Range(&quot;K&quot; &amp; j) = R.Name
                      xlRng.Range(&quot;L&quot; &amp; j) = E.Name
                      xlRng.Range(&quot;M&quot; &amp; j) = E.Start
                      xlRng.Range(&quot;N&quot; &amp; j) = E.Finish
                      j = j + 1
                  Next E
          End If
      End If
  Next R
  MyXL.ActiveWorkbook.worksheets(&quot;Exception Report&quot;).Columns(&quot;A:N&quot;).AutoFit
  End Sub
  </code></pre>
  <p><strong>UPDATE:</strong></p>
  <p>I managed to get the hours from the Exceptions and Weekdays! Here is my complete working VBA code:</p>
  <pre><code>Option Explicit
  Sub CalendarWeekdays()
  
  Dim MyXL As Object
  Set MyXL = CreateObject(&quot;Excel.Application&quot;)
  Dim i As Integer
  Dim R As Resource
  Dim d As PjWeekday
  Dim E As Exception
  Dim xlRng
  MyXL.Workbooks.Add
  MyXL.Visible = True
  
  ' I. EXCEPTIONS
  
  ' a. Export resource base calendar exceptions along with
  '    resource calendar exceptions
  MyXL.ActiveWorkbook.Worksheets(&quot;Sheet1&quot;).Activate
  MyXL.activesheet.Name = &quot;Base &amp; Resource Exceptions&quot;
  Set xlRng = MyXL.activesheet.Range(&quot;A1&quot;)
  MyXL.activesheet.Range(&quot;A1:K1&quot;).Font.Bold = True
  
  xlRng.Range(&quot;A1&quot;) = &quot;Resource&quot;
  xlRng.Range(&quot;B1&quot;) = &quot;Resource Base Name&quot;
  xlRng.Range(&quot;C1&quot;) = &quot;Name&quot;
  xlRng.Range(&quot;D1&quot;) = &quot;Start&quot;
  xlRng.Range(&quot;E1&quot;) = &quot;Finish&quot;
  xlRng.Range(&quot;F1&quot;) = &quot;S1 Start&quot;
  xlRng.Range(&quot;G1&quot;) = &quot;S1 Finish&quot;
  xlRng.Range(&quot;H1&quot;) = &quot;S2 Start&quot;
  xlRng.Range(&quot;I1&quot;) = &quot;S2 Finish&quot;
  xlRng.Range(&quot;J1&quot;) = &quot;S3 Start&quot;
  xlRng.Range(&quot;K1&quot;) = &quot;S3 Finish&quot;
  
  i = 2
  For Each R In ActiveProject.Resources
      If Not R Is Nothing Then
          If R.Type = pjResourceTypeWork Then
                  For Each E In R.Calendar.Exceptions
                      xlRng.Range(&quot;A&quot; &amp; i) = R.Name
                      xlRng.Range(&quot;B&quot; &amp; i) = R.Calendar.BaseCalendar.Name
                      xlRng.Range(&quot;C&quot; &amp; i) = E.Name
                      xlRng.Range(&quot;D&quot; &amp; i) = E.Start
                      xlRng.Range(&quot;E&quot; &amp; i) = E.Finish
                      xlRng.Range(&quot;F&quot; &amp; i) = E.Shift1.Start
                      xlRng.Range(&quot;G&quot; &amp; i) = E.Shift1.Finish
                      xlRng.Range(&quot;H&quot; &amp; i) = E.Shift2.Start
                      xlRng.Range(&quot;I&quot; &amp; i) = E.Shift2.Finish
                      xlRng.Range(&quot;J&quot; &amp; i) = E.Shift3.Start
                      xlRng.Range(&quot;K&quot; &amp; i) = E.Shift3.Finish
                      i = i + 1
                  Next E
          End If
      End If
  Next R
  
  ' b. Export project calendar exceptions
  MyXL.ActiveWorkbook.Worksheets.Add.Name = &quot;Project Exceptions&quot;
  MyXL.ActiveWorkbook.Worksheets(&quot;Project Exceptions&quot;).Activate
  Set xlRng = MyXL.activesheet.Range(&quot;A1&quot;)
  MyXL.activesheet.Range(&quot;A1:I1&quot;).Font.Bold = True
  
  xlRng.Range(&quot;A1&quot;) = &quot;Name&quot;
  xlRng.Range(&quot;B1&quot;) = &quot;Start&quot;
  xlRng.Range(&quot;C1&quot;) = &quot;Finish&quot;
  xlRng.Range(&quot;D1&quot;) = &quot;S1 Start&quot;
  xlRng.Range(&quot;E1&quot;) = &quot;S1 Finish&quot;
  xlRng.Range(&quot;F1&quot;) = &quot;S2 Start&quot;
  xlRng.Range(&quot;G1&quot;) = &quot;S2 Finish&quot;
  xlRng.Range(&quot;H1&quot;) = &quot;S3 Start&quot;
  xlRng.Range(&quot;I1&quot;) = &quot;S3 Finish&quot;
  
  i = 2
  If ActiveProject.Calendar.Exceptions.Count &gt; 0 Then
      For Each E In ActiveProject.Calendar.Exceptions
          xlRng.Range(&quot;A&quot; &amp; i) = E.Name
          xlRng.Range(&quot;B&quot; &amp; i) = E.Start
          xlRng.Range(&quot;C&quot; &amp; i) = E.Finish
          xlRng.Range(&quot;D&quot; &amp; i) = E.Shift1.Start
          xlRng.Range(&quot;E&quot; &amp; i) = E.Shift1.Finish
          xlRng.Range(&quot;F&quot; &amp; i) = E.Shift2.Start
          xlRng.Range(&quot;G&quot; &amp; i) = E.Shift2.Finish
          xlRng.Range(&quot;H&quot; &amp; i) = E.Shift3.Start
          xlRng.Range(&quot;I&quot; &amp; i) = E.Shift3.Finish
          i = i + 1
      Next
  End If
  
  ' II. WEEKDAYS
  MyXL.ActiveWorkbook.Worksheets.Add.Name = &quot;Weekdays&quot;
  MyXL.ActiveWorkbook.Worksheets(&quot;Weekdays&quot;).Activate
  Set xlRng = MyXL.activesheet.Range(&quot;A1&quot;)
  MyXL.activesheet.Range(&quot;A1:H1&quot;).Font.Bold = True
  
  xlRng.Range(&quot;A1&quot;) = &quot;Resource&quot;
  xlRng.Range(&quot;B1&quot;) = &quot;Weekdays&quot;
  xlRng.Range(&quot;C1&quot;) = &quot;S1 Start&quot;
  xlRng.Range(&quot;D1&quot;) = &quot;S1 Finish&quot;
  xlRng.Range(&quot;E1&quot;) = &quot;S2 Start&quot;
  xlRng.Range(&quot;F1&quot;) = &quot;S2 Finish&quot;
  xlRng.Range(&quot;G1&quot;) = &quot;S3 Start&quot;
  xlRng.Range(&quot;H1&quot;) = &quot;S3 Finish&quot;
  
  i = 2
  For Each R In ActiveProject.Resources
      If Not R Is Nothing Then
          For d = pjSunday To pjSaturday
              xlRng.Range(&quot;A&quot; &amp; i) = R.Name
              xlRng.Range(&quot;B&quot; &amp; i) = WeekdayName(d)
              xlRng.Range(&quot;C&quot; &amp; i) = R.Calendar.WeekDays(d).Shift1.Start
              xlRng.Range(&quot;D&quot; &amp; i) = R.Calendar.WeekDays(d).Shift1.Finish
              xlRng.Range(&quot;E&quot; &amp; i) = R.Calendar.WeekDays(d).Shift2.Start
              xlRng.Range(&quot;F&quot; &amp; i) = R.Calendar.WeekDays(d).Shift2.Finish
              xlRng.Range(&quot;G&quot; &amp; i) = R.Calendar.WeekDays(d).Shift3.Start
              xlRng.Range(&quot;H&quot; &amp; i) = R.Calendar.WeekDays(d).Shift3.Finish
              i = i + 1
          Next d
      End If
  Next R
  
  End Sub
  </code></pre>