[% Dim xlw, ws, cells, wraptext, country ,savepath ,pageStructuregroup , altpageStructuregroup Dim RowCounter, CurrentCellA, CurrentCellB, CurrentCellC, CurrentCelld, CurrentCellE, CurrentCellF '---========================================================================================== '--- Template name = extract__to_excel '--- for extracting a new car, copy the template and change the value below for the name of the structure group that you want to extract '--- i.e for Yaris pageStructuregroup = "yaris" '--- then create a page at the root level of the course called _course_content_extract using the new template '--- i.e Yaris_course_content_extract '---========================================================================================== '--- change only these values as directed above pageStructuregroup = "RX400h" '--- name of the course AltpageStructuregroup = "RX400h" '--- alternative name of the course - if there is not one use same value as pageStructuregroup '---========================================================================================== '--- leave these values alone country = mid( replace(page.info.Path,"_","") ,2,6) '--- DO NOT EDIT savepath = "c:/" & country & "_" & pageStructuregroup & "_course_content_extract.xls" '---========================================================================================== '---========================================================================================== '--- main code starts here '---========================================================================================== #include "tcm:181-27643-2048" Set xlw = CreateObject("SoftArtisans.ExcelWriter") Set ws = xlw.Worksheets(1) set cells = xlw.worksheets(1).cells '--- write the spreadsheet headers Cells("A1").Value = "TCMID" Cells("B1").Value = "Node name" Cells("C1").Value = "Value" Cells("D1").Value = "Flag" Cells("E1").Value = "Master Text" Cells("F1").Value = "Translated Text" '--- wrap the text of the tranlsation text and change type to text Cells.columns(5,2).ColumnWidth = 40 Cells.columns(2,3).ColumnWidth = 0 RowCounter = 1 '====================================================== '- call functions for processing extact '====================================================== '--- write the folder names to the CSV 'writeout "Getting Foldernames
" GetFolderNames() '-------------------------------------------------------------------------------- '--- write the questions and text writeout "Getting questions
" GetTextValues str_question_1, "single" GetTextValues str_question_2, "single" GetTextValues str_question_3, "single" GetTextValues str_question_4,"single" GetTextValues str_question_6,"single" GetTextValues str_question_5,"single" '-------------------------------------------------------------------------------- '--- get the help, faq, legal,contacts information writeout "Getting sitetools
" GetEmbeddedValueTextValues(str_contact_schema) 'contacts GetTextValues str_faq_schema,"single" 'faqs GetTextValues str_help_schema,"single" 'help GetTextValues str_legal_schema,"single" 'legal '-------------------------------------------------------------------------------- '--- get the DYK text values writeout "Getting DYK
" GetTextValues str_kce_dyk_schema,"multi" '-------------------------------------------------------------------------------- '--- get the Competitor Vehicles text values writeout "Getting Competitor Vehicles
" GetTextValues str_competitor_schema,"multi" '-------------------------------------------------------------------------------- '--- get the global flash objects writeout "Getting global flash
" GetMetaValues str_flash_schema,"flash" '-------------------------------------------------------------------------------- '--- get all content using the learnobject schema writeout "Getting learnobject
" GetTextValues str_learning_schema,"multi" '-------------------------------------------------------------------------------- '--- get the content that uses the common schema writeout "Getting common schema
" GetTextValues str_common_schema,"multi" '-------------------------------------------------------------------------------- '--- get the specific technical data values writeout "Getting technical data
" GetTextValues str_technical_prius,"single" GetTextValues str_technical_engine,"single" GetTextValues str_technical_common,"single" '-------------------------------------------------------------------------------- '--- get all the additional features writeout "Getting additional features
" GetTextValues str_additional_feature,"multi" '-------------------------------------------------------------------------------- '--- get all the elearning homepage values writeout "Getting elearning homepage
" GetGlobalTextValues str_homepage_sel,"single" '-------------------------------------------------------------------------------- '--- get all the evaluation tool parameters writeout "Getting evaluation tool
" GetTextValues str_evaluation_schema,"multi" '-------------------------------------------------------------------------------- '--- get all the globaltranslate values writeout "Getting globaltranslate
" GetGlobalTextValues str_translate_schema,"single" '-------------------------------------------------------------------------------- '--- get all the Product Discovery values writeout "Getting Product Discovery
" GetGlobalTextValues str_prod_discovery,"single" '-------------------------------------------------------------------------------- '--- get all the evaluation result text values writeout "Getting evaluation result
" GetGlobalTextValues str_evaluation_param,"single" '-------------------------------------------------------------------------------- '--- get all the glossary text values writeout "Getting glossary
" GetTextValues str_lexicon_gloss,"single" '====================================================== '- end function call for processing '====================================================== '====================================================== '- functions for processing '====================================================== function GetFolderNames() set RootOrgItem= page.organizationalitem Set objTDSE = CreateObject("TDS.TDSE") Set objListRowFilter = objTDSE.CreateListRowFilter() call objListRowFilter.setCondition("Recursive",true) call objListRowFilter.setCondition("ItemType",4) call objListRowFilter.setCondition("InPublication", page.Publication ) XMLStr = RootOrgItem.GetListItems( 7,objListRowFilter) Set lDomDoc = GetNewDOMDocument() Call lDomDoc.loadXML(XMLStr) writeout XMLStr Set XMLRoot = lDomDoc.selectNodes("//tcm:ListItems") For Each Node In XMLRoot set pagenodes = Node.childnodes for each objchild in pagenodes set objStruct = gettomobject(objchild.getattribute("ID")) Cells("A" & RowCounter).Value = objchild.getattribute("ID") Cells("B" & RowCounter).Value = "title" Cells("C" & RowCounter).Value = "1" Cells("D" & RowCounter).Value = "1" Cells("E" & RowCounter).Value = objStruct.MetadataFields.Item("title").Value(1) Cells("F" & RowCounter).Value = objStruct.MetadataFields.Item("title").Value(1) rowcounter = rowcounter + 1 next Next end function '---========================================================================================== function GetTextValues(tcm, TextType) '---========================================================================================== tcm=get_localised_tcm(tcm) set RootOrgItem= gettomobject(tcm) startpos = instr(page.publication.id,"-") +1 endpos = instrrev(page.publication.id,"-") localpubid = mid(page.publication.id, startpos , endpos - startpos) Set objTDSE = CreateObject("TDS.TDSE") Set objListRowFilter = objTDSE.CreateListRowFilter() call objListRowFilter.setCondition("InclLocalCopies",true) call objListRowFilter.setCondition("ItemType",16) call objListRowFilter.setCondition("InPublication",page.Publication) XMLStr = RootOrgItem.info.GetListUsingItems( 7,objListRowFilter) Set lDomDoc = GetNewDOMDocument() Call lDomDoc.loadXML(XMLStr) Set XMLRoot = lDomDoc.selectNodes("//tcm:ListUsingItems") For Each Node In XMLRoot set pagenodes = Node.childnodes '--- only if in the same publication for each objchild in pagenodes '--- it is a page in this country publication if instr (objchild.getattribute("ID") , localpubid ) > 0 then set objStruct = gettomobject(objchild.getattribute("ID")) '--- it is part of this structuregroup course i.e. prius / avensis etc ' do a write out her to see if goin in the loop if instr(lcase(objStruct.info.path), lcase(pageStructuregroup)) > 0 or instr(lcase(objStruct.info.path), lcase(AltpageStructuregroup)) > 0 or instr(lcase(objStruct.info.path), "global assets") > 0 then for each txtitem in objStruct.fields if txtitem.fieldtype=7 or txtitem.fieldtype=6 or txtitem.fieldtype=0 then if txtitem.value(1) <> "" then select case TextType case "multi" for i = 1 to txtitem.value.count Cells("A" & RowCounter).Value = objchild.getattribute("ID") Cells("B" & RowCounter).Value = txtitem.name Cells("C" & RowCounter).Value = i Cells("D" & RowCounter).Value = "0" Cells("E" & RowCounter).Value = txtitem.value(i) Cells("F" & RowCounter).Value = txtitem.value(i) rowcounter = rowcounter + 1 next case "single" Cells("A" & RowCounter).Value = objchild.getattribute("ID") Cells("B" & RowCounter).Value = txtitem.name Cells("C" & RowCounter).Value = "1" Cells("D" & RowCounter).Value = "0" Cells("E" & RowCounter).Value = txtitem.value(1) Cells("F" & RowCounter).Value = txtitem.value(1) rowcounter = rowcounter + 1 end select end if elseif txtitem.fieldtype=3 then Set multirow = txtitem for each subitem in multirow.value(1) Cells("A" & RowCounter).Value = objchild.getattribute("ID") Cells("B" & RowCounter).Value = txtitem.name & "/" & subitem.name Cells("C" & RowCounter).Value = "1" Cells("D" & RowCounter).Value = "2" Cells("E" & RowCounter).Value = subitem.value(1) Cells("F" & RowCounter).Value = subitem.value(1) rowcounter = rowcounter + 1 next end if next end if end if next Next end function '---========================================================================================== function GetGlobalTextValues(tcm, TextType) '---========================================================================================== tcm=get_localised_tcm(tcm) set RootOrgItem= gettomobject(tcm) startpos = instr(page.publication.id,"-") +1 endpos = instrrev(page.publication.id,"-") localpubid = mid(page.publication.id, startpos , endpos - startpos) Set objTDSE = CreateObject("TDS.TDSE") Set objListRowFilter = objTDSE.CreateListRowFilter() call objListRowFilter.setCondition("InclLocalCopies",true) call objListRowFilter.setCondition("ItemType",16) call objListRowFilter.setCondition("InPublication",page.Publication) XMLStr = RootOrgItem.info.GetListUsingItems( 7,objListRowFilter) Set lDomDoc = GetNewDOMDocument() Call lDomDoc.loadXML(XMLStr) Set XMLRoot = lDomDoc.selectNodes("//tcm:ListUsingItems") For Each Node In XMLRoot set pagenodes = Node.childnodes '--- only if in the same publication for each objchild in pagenodes '--- it is a page in this country publication set objStruct = gettomobject(objchild.getattribute("ID")) if instr (objchild.getattribute("ID") , localpubid ) > 0 then for each txtitem in objStruct.fields if txtitem.fieldtype=7 or txtitem.fieldtype=6 or txtitem.fieldtype=0 then Cells("A" & RowCounter).Value = objchild.getattribute("ID") Cells("B" & RowCounter).Value = txtitem.name Cells("C" & RowCounter).Value = "1" Cells("D" & RowCounter).Value = "0" Cells("E" & RowCounter).Value = txtitem.value(1) Cells("F" & RowCounter).Value = txtitem.value(1) rowcounter = rowcounter + 1 end if next end if next Next end function '---========================================================================================== function GetMetaValues(tcm, TextType) '---========================================================================================== tcm=get_localised_tcm(tcm) set RootOrgItem= gettomobject(tcm) startpos = instr(page.publication.id,"-") +1 endpos = instrrev(page.publication.id,"-") localpubid = mid(page.publication.id, startpos , endpos - startpos) Set objTDSE = CreateObject("TDS.TDSE") Set objListRowFilter = objTDSE.CreateListRowFilter() call objListRowFilter.setCondition("InclLocalCopies",true) call objListRowFilter.setCondition("ItemType",16) call objListRowFilter.setCondition("InPublication",page.Publication) XMLStr = RootOrgItem.info.GetListUsingItems( 7,objListRowFilter) Set lDomDoc = GetNewDOMDocument() Call lDomDoc.loadXML(XMLStr) Set XMLRoot = lDomDoc.selectNodes("//tcm:ListUsingItems") For Each Node In XMLRoot set pagenodes = Node.childnodes '--- only if in the same publication for each objchild in pagenodes '--- it is a page in this country publication if instr (objchild.getattribute("ID") , localpubid ) > 0 then set objStruct = gettomobject(objchild.getattribute("ID")) '--- it is part of this structuregroup course i.e. prius / avensis etc if instr(lcase(objStruct.info.path), lcase(pageStructuregroup)) > 0 or instr(objStruct.info.path, "Global assets") > 0 then for each txtitem in objStruct.MetadataFields select case txtitem.name case "labelvalues", "instructionvalues", "textvalues", "titlevalues" if txtitem.value(1) <> "" then for i = 1 to txtitem.value.count Cells("A" & RowCounter).Value = objchild.getattribute("ID") Cells("B" & RowCounter).Value = txtitem.name Cells("C" & RowCounter).Value = i Cells("D" & RowCounter).Value = "1" Cells("E" & RowCounter).Value = txtitem.value(i) Cells("F" & RowCounter).Value = txtitem.value(i) rowcounter = rowcounter + 1 next end if end select next end if end if next Next end function '---========================================================================================== function GetEmbeddedValueTextValues(tcm) '---========================================================================================== tcm=get_localised_tcm(tcm) set RootOrgItem= gettomobject(tcm) startpos = instr(page.publication.id,"-") +1 endpos = instrrev(page.publication.id,"-") localpubid = mid(page.publication.id, startpos , endpos - startpos) Set objTDSE = CreateObject("TDS.TDSE") Set objListRowFilter = objTDSE.CreateListRowFilter() call objListRowFilter.setCondition("InclLocalCopies",true) call objListRowFilter.setCondition("ItemType",16) call objListRowFilter.setCondition("InPublication",page.Publication) XMLStr = RootOrgItem.info.GetListUsingItems( 7,objListRowFilter) Set lDomDoc = GetNewDOMDocument() Call lDomDoc.loadXML(XMLStr) Set XMLRoot = lDomDoc.selectNodes("//tcm:ListUsingItems") For Each Node In XMLRoot set pagenodes = Node.childnodes '--- only if in the same publication for each objchild in pagenodes '--- it is a page in this country publication if instr (objchild.getattribute("ID") , localpubid ) > 0 then set objStruct = gettomobject(objchild.getattribute("ID")) '--- it is part of this structuregroup course i.e. prius / avensis etc if instr(lcase(objStruct.info.path), lcase(pageStructuregroup)) > 0 then for each txtitem in objStruct.fields if txtitem.fieldtype=7 or txtitem.fieldtype=6 or txtitem.fieldtype=0 then if txtitem.value(1) <> "" then for i = 1 to txtitem.value.count Cells("A" & RowCounter).Value = objchild.getattribute("ID") Cells("B" & RowCounter).Value = txtitem.name Cells("C" & RowCounter).Value = i Cells("D" & RowCounter).Value = "0" Cells("E" & RowCounter).Value = txtitem.value(i) Cells("F" & RowCounter).Value = txtitem.value(i) rowcounter = rowcounter + 1 next end if elseif txtitem.fieldtype=3 then Set multirow = txtitem for each subitem in multirow.value(1) Cells("A" & RowCounter).Value = objchild.getattribute("ID") Cells("B" & RowCounter).Value = txtitem.name & "/" & subitem.name Cells("C" & RowCounter).Value = "1" Cells("D" & RowCounter).Value = "2" Cells("E" & RowCounter).Value = subitem.value(1) Cells("F" & RowCounter).Value = subitem.value(1) rowcounter = rowcounter + 1 next end if next end if end if next Next end function '---========================================================================================== function get_localised_tcm(tcmstr) '---========================================================================================== '--- get the new tcm for the current publication if rendermode="PreviewDynamic" then tcmid = tcmstr startpos = instr(page.publication.id,"-") +1 endpos = instrrev(page.publication.id,"-") localpubid = mid(page.publication.id, startpos , endpos - startpos) endpos = instr(tcmstr,"-") tcmid = "tcm:" & localpubid & "-" & right(tcmstr, len(tcmstr)-endpos) else startpos = instr(page.publication.id,"-") +1 endpos = instrrev(page.publication.id,"-") localpubid = mid(page.publication.id, startpos , endpos - startpos) endpos = instr(tcmstr,"-") tcmid = "tcm:" & localpubid & "-" & right(tcmstr, len(tcmstr)-endpos) end if get_localised_tcm = tcmid end function 'checkcell (Cells("A2")) '====================================================== '--- save the file now xlw.Save savepath set xlw = nothing '---========================================================================================== Function CheckCell(thecell) '---========================================================================================== '--- if the value is yes or no then hightlight not to be changed if thecell.Value = "no" or thecell.Value = "yes" then thecell.format.Font.Color = 255 end if if instr(thecell.value, "xhtml" ) > 0 then thecell.format.Font.Color = 255 end if end function %]