KEMBAR78
1 "Listbox1" "Listbox1": For To If Then | PDF | Computer Programming | Computing
Scribd
Upload
45 views3 pages

1 "Listbox1" "Listbox1": For To If Then

This document contains code to export project task data from Microsoft Project to Excel. It loops through each task in a project, writes the task ID, name, start date, and finish date to cells. It then calculates the task duration in days and adds day of week headers to subsequent columns. The code colors cells for each task's duration to mimic a Gantt chart view.

Uploaded by

franklaer-2
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
45 views3 pages

1 "Listbox1" "Listbox1": For To If Then

This document contains code to export project task data from Microsoft Project to Excel. It loops through each task in a project, writes the task ID, name, start date, and finish date to cells. It then calculates the task duration in days and adds day of week headers to subsequent columns. The code colors cells for each task's duration to mimic a Gantt chart view.

Uploaded by

franklaer-2
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 3

For i = 1 To ActiveSheet.ListBoxes("ListBox1").

ListCount
If ActiveSheet.ListBoxes("ListBox1").Selected(i) Then strLB = strLB & etc.etc.
Next i

Private Sub CommandButton2_Click()

Dim lItem As Long

For lItem = 0 To ListBox1.ListCount - 1

If ListBox1.Selected(lItem) = True Then

MsgBox(ListBox1.List(lItem))

End If

Next

End Sub

To get the value of the selected item of a listbox then use the following.

For Single Column ListBox: ListBox1.List(ListBox1.ListIndex)

For Multi Column ListBox: ListBox1.Column(column_number, ListBox1.ListIndex)


Sub ExportToExcel()

Dim xlApp As Excel.Application

Dim xlBook As Excel.Workbook

Dim xlSheet As Excel.Worksheet

Dim proj As Project

Dim t As Task

Dim pj As Project

Dim i As Integer

Set pj = ActiveProject

Set xlApp = New Excel.Application

xlApp.Visible = True

AppActivate "Excel"

Set xlBook = xlApp.Workbooks.Add

Set xlSheet = xlBook.Worksheets(1)

xlSheet.Cells(1, 1).Value = "Project Name"

xlSheet.Cells(1, 2).Value = pj.Name

xlSheet.Cells(2, 1).Value = "Project Title"

xlSheet.Cells(2, 2).Value = pj.Title

xlSheet.Cells(4, 1).Value = "Task ID"

xlSheet.Cells(4, 2).Value = "Task Name"

xlSheet.Cells(4, 3).Value = "Task Start"

xlSheet.Cells(4, 4).Value = "Task Finish"

For Each t In pj.Tasks

xlSheet.Cells(t.ID + 4, 1).Value = t.ID

xlSheet.Cells(t.ID + 4, 2).Value = t.Name

xlSheet.Cells(t.ID + 4, 3).Value = t.Start

xlSheet.Cells(t.ID + 4, 4).Value = t.Finish


Dim x As Integer

'x is the duration of task in days(i.e. half a day long task is 0.5)

x = t.Finish - t.Start

'Loop to add day of week headers and color cells to mimic Gantt chart

For i = 0 To x

xlSheet.Cells(4, (7 * i) + 5).Value = "S"

xlSheet.Cells(4, (7 * i) + 6).Value = "M"

xlSheet.Cells(4, (7 * i) + 7).Value = "T"

xlSheet.Cells(4, (7 * i) + 8).Value = "W"

xlSheet.Cells(4, (7 * i) + 9).Value = "T"

xlSheet.Cells(4, (7 * i) + 10).Value = "F"

xlSheet.Cells(4, (7 * i) + 11).Value = "S"

xlSheet.Cells(t.ID + 4, ((7 * i) + 5)).Interior.ColorIndex = 37

xlSheet.Cells(t.ID + 4, (7 * i) + 6).Interior.ColorIndex = 37

xlSheet.Cells(t.ID + 4, (7 * i) + 7).Interior.ColorIndex = 37

xlSheet.Cells(t.ID + 4, (7 * i) + 8).Interior.ColorIndex = 37

xlSheet.Cells(t.ID + 4, (7 * i) + 9).Interior.ColorIndex = 37

xlSheet.Cells(t.ID + 4, (7 * i) + 10).Interior.ColorIndex = 37

xlSheet.Cells(t.ID + 4, (7 * i) + 11).Interior.ColorIndex = 37

Next i

Next t

End Sub

You might also like