Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Project data exported into Excel
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Project data exported into Excel
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Project data exported into Excel
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Project data exported into Excel
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Exported data from Banner database to Excel | Excel Worksheet Functions | |||
Data exported from Lotus approach to excel how do I show it in a u | Excel Discussion (Misc queries) | |||
Truncated Exported Data | Excel Discussion (Misc queries) | |||
Exported Data to excel appearing wrong in Columns | Excel Discussion (Misc queries) | |||
Formating exported data | Excel Worksheet Functions |