Format in Excel or in Project?

[Lee]

Hi all,

I've created several procedures which export data from Project into
(currently) a CSV file. My end result (after formatting) needs to be a
standard layout. I can create the VBA code in Excel to format the raw data
into the appropriate layout and format, but this means opening the file and
running the macro.

Is it poissble (or even advisable) to export the data from Project into
Excel and, at the same time, do this formatting?

Thanks for all and any help!

Lee

 

[Jan De Messemaeker]

Hi,

In such a case I indeed do the Excel formatting in the same procedure as
putting the data
But the Excel file has to be open, of course.

 

[Lee]

Hi Jan,

So when I am outputting the data to file, I can use Excel formatting
commands (after declaring the xlApp variables, etc) within my Project VBA
code?

 

[Jan De Messemaeker]

Hi Lee,

Once you have declared the xlapp variable you have access to all Excel
functions.
Good luck!

 

[egun]

Examples:

xlObj.ActiveSheet.Columns(i).HorizontalAlignment = xlLeft
xlObj.Selection.NumberFormat = "m/d/yyyy;@"
xlObj.ActiveSheet.Range(xlObj.ActiveSheet.Cells(1, 1), _
xlObj.ActiveSheet.Cells(theSel.Tasks.Count + 1, _
theSel.FieldIDList.Count)).Select ' Note use of Project objects
(Tasks and FieldIDList)
xlObj.Selection.Columns.AutoFit
xlObj.Selection.RowHeight = 15#

 

[Rod Gill]

Another way is to export your csv file, or xml file or even Excel file, then
open an Excel Template that already has much of your formatting done. An
Excel Macro then imports the data and finishes the formatting.

 

[Lee]

Hi egun and thanks for the examples.

Is there anywhere I can read up on how to properly Dim the Excel objects and
use the Excel VBA code?

Lee

 

[egun]

'*** Here is how I set up Excel inside Project ***
'*** Also, go to http://masamiki.com/project/macros.htm ***
'*** and look at the 'Export Hierarchy to Excel' code ***
'*** Finally, check out http://www.j-walk.com/ss/ and ***
'*** look in the Excel Developer Tips section for basics ***

Dim xlObj As Excel.applicaton
Dim xlWB As Excel.Workbook
dim xlSheet as Excel.Worksheet
Dim xlIsOpen As Boolean
'
' Create a new instance of the MS Excel application, with
' a new workbook, if one does not already exist.
'
' Is Excel already open?
'
xlIsOpen = False
On Error GoTo Excel_Try1
Set xlObj = GetObject(, "excel.application")
xlIsOpen = True
'
Excel_Try1:
'
' No, open Excel and create workbook if required
'
If (xlIsOpen) Then
If (xlObj.Workbooks.Count = 0) Then
Set xlWB = xlObj.Workbooks.Add
Else
Set xlWB = xlObj.ActiveWorkbook
End If
Else ' It's open, but is
there an active workbook?
On Error GoTo Excel_Problem
Set xlObj = New Excel.Application
Set xlWB = xlObj.Workbooks.Add
End If
'
On Error GoTo 0
'
xlObj.Visible = True

and then, down below somewhere...

'
Excel_Problem:
MsgBox ("There was a problem creating the Excel object!")
Exit Sub

 

[Lee]

egun,

Thanks for that.

I think I found a major source to my problems - I did not have the Excel VBA
References loaded! Which begs a question: If I am writing VBA macros to sit
in a company template for all Project managers to use, how can I ensure that
they all have the relevant libraries loaded without actually visiting each
one or writing a set of instructions for them to switch them on themselves ??

 

[Jan De Messemaeker]

Hi,

This is how you can add a reference in Project VBA:

vbe.VBProjects(1).References.AddFromFile(FileNameAsString)
The filename for each reference is displayed when you click the reference.
Unfortunately, everybody has to have the same installation.
HTH