Project data exported into Excel

[JB]

At a suggestion from a member of the Project Discussion Goup, I'm duplicating
my question in the Excel group.

Dear Experts,

I'm baffled. I've exported task data from Project 2003 into Excel 2000
using the data map functionality in Project. Once in Excel, I have a
advanced filter macro to filter the data into separate sheets by location (a
text field). The weird thing is two fields/columns are blank after the
filter. The fields are contact and resource_name. The column headers filter
to the new sheets and data exists on the main sheet before the macro is run.

Here's what I've done so far:
I rearranged the columns both pre-export & post export from Project to
ensure it is this data.
I copied the data into a new spreadsheet with same results.
I tested the macro.
I copied all but the questionable data into a new spreadsheet & typed data
into the questionable columns and the typed data filtered to the new sheets.

It seems to be the data. Excel reads it as type 2=text. Any ideas why it
won't transfer to new sheets in Excel?

Thanks,
JB

JB,
Well, this is really a question better directed to an Excel VBA
newsgroup since the problem seems to be after the data is in Excel.
However, several of us have worked extensively with macros that export
data from Project to Excel and therefore have a working knowledge of
Excel VBA. So, you could either try an Excel newsgroup, or, give us more
information about your code and maybe, just maybe, we can help.

John
Project MVP

[Tom Ogilvy]

If you have a completely blank column in your data, then perhaps the filter
isn't picking up all the data.

If you are doing the advanced filter with code, post the code that does the
filter.

--
Regards,
Tom Ogilvy

"JB" <[email protected]> wrote in message
news:[email protected]...
> At a suggestion from a member of the Project Discussion Goup, I'm
duplicating
> my question in the Excel group.
>
> Dear Experts,
>
> I'm baffled. I've exported task data from Project 2003 into Excel 2000
> using the data map functionality in Project. Once in Excel, I have a
> advanced filter macro to filter the data into separate sheets by location
(a
> text field). The weird thing is two fields/columns are blank after the
> filter. The fields are contact and resource_name. The column headers
filter
> to the new sheets and data exists on the main sheet before the macro is
run.
>
> Here's what I've done so far:
> I rearranged the columns both pre-export & post export from Project to
> ensure it is this data.
> I copied the data into a new spreadsheet with same results.
> I tested the macro.
> I copied all but the questionable data into a new spreadsheet & typed data
> into the questionable columns and the typed data filtered to the new
sheets.
>
> It seems to be the data. Excel reads it as type 2=text. Any ideas why it
> won't transfer to new sheets in Excel?
>
> Thanks,
> JB
>
> JB,
> Well, this is really a question better directed to an Excel VBA
> newsgroup since the problem seems to be after the data is in Excel.
> However, several of us have worked extensively with macros that export
> data from Project to Excel and therefore have a working knowledge of
> Excel VBA. So, you could either try an Excel newsgroup, or, give us more
> information about your code and maybe, just maybe, we can help.
>
> John
> Project MVP
>

[JB]

Thanks Tom. The weird thing was as I reordered the columns, the same data
failed to transfer to the new sheets. Here's the code thanks to Debra
Dalgleish:

Sub FilterWorkGroup()
'last edited Feb 28, 2006
Dim myCell As Range
Dim wks As Worksheet
Dim DataBaseWks As Worksheet
Dim ListRange As Range
Dim dummyRng As Range
Dim myDatabase As Range
Dim TempWks As Worksheet
Dim rsp As Integer
Dim i As Long

'include bottom most header row
Const TopLeftCellOfDataBase As String = "A2"

'what column has your key values
Const KeyColumn As String = "A"

'where's your data
Set DataBaseWks = Worksheets("Task_Table1")
i = DataBaseWks.Range(TopLeftCellOfDataBase).Row - 1

rsp = MsgBox("Include headings?", vbYesNo, "Headings")

Set TempWks = Worksheets.Add

With DataBaseWks
Set dummyRng = .UsedRange
Set myDatabase = .Range(TopLeftCellOfDataBase, _
.Cells.SpecialCells(xlCellTypeLastCell))
End With

'rebuild the List
With DataBaseWks
Intersect(myDatabase, .Columns(KeyColumn)).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=TempWks.Range("A1"), _
Unique:=True

'Add the heading to the criteria area
TempWks.Range("D1").Value = _
.Cells(.Range(TopLeftCellOfDataBase).Row, KeyColumn).Value
End With

With TempWks
Set ListRange = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

With ListRange
.Sort Key1:=.Cells(1), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End With

'check for individual Work Group worksheets
For Each myCell In ListRange.Cells
If WksExists(myCell.Value) = False Then
Set wks = Sheets.Add
On Error Resume Next
wks.Name = myCell.Value
If Err.Number <> 0 Then
MsgBox "Please rename: " & wks.Name
Err.Clear
End If
On Error GoTo 0
wks.Move After:=Sheets(Sheets.Count)
Else
Set wks = Worksheets(myCell.Value)
wks.Cells.Clear
End If

If rsp = 6 Then
DataBaseWks.Rows("1:" & i).Copy Destination:=wks.Range("A1")
End If

'change the criteria in the Criteria range
TempWks.Range("D2").Value = "=" & Chr(34) & "=" & myCell.Value &
Chr(34)

'transfer data to individual Work Group worksheets
If rsp = 6 Then
myDatabase.AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=TempWks.Range("D1:D2"), _
CopyToRange:=wks.Range("A1").Offset(i, 0), _
Unique:=False
Else
myDatabase.AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=TempWks.Range("D1:D2"), _
CopyToRange:=wks.Range("A1"), _
Unique:=False
End If
Next myCell

Application.DisplayAlerts = False
TempWks.Delete
Application.DisplayAlerts = True


End Sub
Function WksExists(wksName As String) As Boolean
On Error Resume Next
WksExists = CBool(Len(Worksheets(wksName).Name) > 0)
End Function

"Tom Ogilvy" wrote:

> If you have a completely blank column in your data, then perhaps the filter
> isn't picking up all the data.
>
> If you are doing the advanced filter with code, post the code that does the
> filter.
>
> --
> Regards,
> Tom Ogilvy
>
> "JB" <[email protected]> wrote in message
> news:[email protected]...
> > At a suggestion from a member of the Project Discussion Goup, I'm
> duplicating
> > my question in the Excel group.
> >
> > Dear Experts,
> >
> > I'm baffled. I've exported task data from Project 2003 into Excel 2000
> > using the data map functionality in Project. Once in Excel, I have a
> > advanced filter macro to filter the data into separate sheets by location
> (a
> > text field). The weird thing is two fields/columns are blank after the
> > filter. The fields are contact and resource_name. The column headers
> filter
> > to the new sheets and data exists on the main sheet before the macro is
> run.
> >
> > Here's what I've done so far:
> > I rearranged the columns both pre-export & post export from Project to
> > ensure it is this data.
> > I copied the data into a new spreadsheet with same results.
> > I tested the macro.
> > I copied all but the questionable data into a new spreadsheet & typed data
> > into the questionable columns and the typed data filtered to the new
> sheets.
> >
> > It seems to be the data. Excel reads it as type 2=text. Any ideas why it
> > won't transfer to new sheets in Excel?
> >
> > Thanks,
> > JB
> >
> > JB,
> > Well, this is really a question better directed to an Excel VBA
> > newsgroup since the problem seems to be after the data is in Excel.
> > However, several of us have worked extensively with macros that export
> > data from Project to Excel and therefore have a working knowledge of
> > Excel VBA. So, you could either try an Excel newsgroup, or, give us more
> > information about your code and maybe, just maybe, we can help.
> >
> > John
> > Project MVP
> >
>
>
>

[Tom Ogilvy]

code looks good. If you want to send me a copy of the workbook with the
problematic data, I can see if I can figure out what the problem is.

Also, what version of Excel are using.

Send to [email protected]

--
Regards,
Tom Ogilvy

"JB" <[email protected]> wrote in message
news:[email protected]...
> Thanks Tom. The weird thing was as I reordered the columns, the same data
> failed to transfer to the new sheets. Here's the code thanks to Debra
> Dalgleish:
>
> Sub FilterWorkGroup()
> 'last edited Feb 28, 2006
> Dim myCell As Range
> Dim wks As Worksheet
> Dim DataBaseWks As Worksheet
> Dim ListRange As Range
> Dim dummyRng As Range
> Dim myDatabase As Range
> Dim TempWks As Worksheet
> Dim rsp As Integer
> Dim i As Long
>
> 'include bottom most header row
> Const TopLeftCellOfDataBase As String = "A2"
>
> 'what column has your key values
> Const KeyColumn As String = "A"
>
> 'where's your data
> Set DataBaseWks = Worksheets("Task_Table1")
> i = DataBaseWks.Range(TopLeftCellOfDataBase).Row - 1
>
> rsp = MsgBox("Include headings?", vbYesNo, "Headings")
>
> Set TempWks = Worksheets.Add
>
> With DataBaseWks
> Set dummyRng = .UsedRange
> Set myDatabase = .Range(TopLeftCellOfDataBase, _
> .Cells.SpecialCells(xlCellTypeLastCell))
> End With
>
> 'rebuild the List
> With DataBaseWks
> Intersect(myDatabase, .Columns(KeyColumn)).AdvancedFilter _
> Action:=xlFilterCopy, _
> CopyToRange:=TempWks.Range("A1"), _
> Unique:=True
>
> 'Add the heading to the criteria area
> TempWks.Range("D1").Value = _
> .Cells(.Range(TopLeftCellOfDataBase).Row, KeyColumn).Value
> End With
>
> With TempWks
> Set ListRange = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
> End With
>
> With ListRange
> .Sort Key1:=.Cells(1), Order1:=xlAscending, _
> Header:=xlNo, OrderCustom:=1, _
> MatchCase:=False, Orientation:=xlTopToBottom
> End With
>
> 'check for individual Work Group worksheets
> For Each myCell In ListRange.Cells
> If WksExists(myCell.Value) = False Then
> Set wks = Sheets.Add
> On Error Resume Next
> wks.Name = myCell.Value
> If Err.Number <> 0 Then
> MsgBox "Please rename: " & wks.Name
> Err.Clear
> End If
> On Error GoTo 0
> wks.Move After:=Sheets(Sheets.Count)
> Else
> Set wks = Worksheets(myCell.Value)
> wks.Cells.Clear
> End If
>
> If rsp = 6 Then
> DataBaseWks.Rows("1:" & i).Copy Destination:=wks.Range("A1")
> End If
>
> 'change the criteria in the Criteria range
> TempWks.Range("D2").Value = "=" & Chr(34) & "=" & myCell.Value &
> Chr(34)
>
> 'transfer data to individual Work Group worksheets
> If rsp = 6 Then
> myDatabase.AdvancedFilter _
> Action:=xlFilterCopy, _
> CriteriaRange:=TempWks.Range("D1:D2"), _
> CopyToRange:=wks.Range("A1").Offset(i, 0), _
> Unique:=False
> Else
> myDatabase.AdvancedFilter _
> Action:=xlFilterCopy, _
> CriteriaRange:=TempWks.Range("D1:D2"), _
> CopyToRange:=wks.Range("A1"), _
> Unique:=False
> End If
> Next myCell
>
> Application.DisplayAlerts = False
> TempWks.Delete
> Application.DisplayAlerts = True
>
>
> End Sub
> Function WksExists(wksName As String) As Boolean
> On Error Resume Next
> WksExists = CBool(Len(Worksheets(wksName).Name) > 0)
> End Function
>
> "Tom Ogilvy" wrote:
>
> > If you have a completely blank column in your data, then perhaps the
filter
> > isn't picking up all the data.
> >
> > If you are doing the advanced filter with code, post the code that does
the
> > filter.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "JB" <[email protected]> wrote in message
> > news:[email protected]...
> > > At a suggestion from a member of the Project Discussion Goup, I'm
> > duplicating
> > > my question in the Excel group.
> > >
> > > Dear Experts,
> > >
> > > I'm baffled. I've exported task data from Project 2003 into Excel
2000
> > > using the data map functionality in Project. Once in Excel, I have a
> > > advanced filter macro to filter the data into separate sheets by
location
> > (a
> > > text field). The weird thing is two fields/columns are blank after the
> > > filter. The fields are contact and resource_name. The column headers
> > filter
> > > to the new sheets and data exists on the main sheet before the macro
is
> > run.
> > >
> > > Here's what I've done so far:
> > > I rearranged the columns both pre-export & post export from Project to
> > > ensure it is this data.
> > > I copied the data into a new spreadsheet with same results.
> > > I tested the macro.
> > > I copied all but the questionable data into a new spreadsheet & typed
data
> > > into the questionable columns and the typed data filtered to the new
> > sheets.
> > >
> > > It seems to be the data. Excel reads it as type 2=text. Any ideas why
it
> > > won't transfer to new sheets in Excel?
> > >
> > > Thanks,
> > > JB
> > >
> > > JB,
> > > Well, this is really a question better directed to an Excel VBA
> > > newsgroup since the problem seems to be after the data is in Excel.
> > > However, several of us have worked extensively with macros that export
> > > data from Project to Excel and therefore have a working knowledge of
> > > Excel VBA. So, you could either try an Excel newsgroup, or, give us
more
> > > information about your code and maybe, just maybe, we can help.
> > >
> > > John
> > > Project MVP
> > >
> >
> >
> >