[%
Dim xlw, ws, cells, wraptext, country ,savepath ,pageStructuregroup , altpageStructuregroup 
Dim RowCounter, 	CurrentCellA,	CurrentCellB, 	CurrentCellC,	CurrentCelld,	CurrentCellE,	CurrentCellF
'---==========================================================================================

'--- Template name = extract_<model>_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 <model>_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<br>"
GetFolderNames()
'--------------------------------------------------------------------------------
'--- write the questions and text
writeout "Getting questions <br>"
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<br>"
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 <br>"
GetTextValues str_kce_dyk_schema,"multi"
'--------------------------------------------------------------------------------
'--- get the Competitor Vehicles text values
writeout "Getting Competitor Vehicles <br>"
GetTextValues str_competitor_schema,"multi"
'--------------------------------------------------------------------------------
'--- get the global flash objects 
writeout "Getting global flash <br>"
GetMetaValues str_flash_schema,"flash"
'--------------------------------------------------------------------------------
'--- get all content using the learnobject schema
writeout "Getting learnobject <br>"
GetTextValues str_learning_schema,"multi"
'--------------------------------------------------------------------------------
'--- get the content that uses the common schema
writeout "Getting common schema<br>"
GetTextValues str_common_schema,"multi"
'--------------------------------------------------------------------------------
'--- get the specific technical data values
writeout "Getting technical data <br>"
GetTextValues str_technical_prius,"single"
GetTextValues str_technical_engine,"single"
GetTextValues str_technical_common,"single"
'--------------------------------------------------------------------------------
'--- get all the additional features
writeout "Getting additional features<br>"
GetTextValues str_additional_feature,"multi"
'--------------------------------------------------------------------------------
'--- get all the elearning homepage values
writeout "Getting elearning homepage <br>"
GetGlobalTextValues str_homepage_sel,"single"
'--------------------------------------------------------------------------------
'--- get all the evaluation tool parameters
writeout "Getting evaluation tool <br>"
GetTextValues str_evaluation_schema,"multi"
'--------------------------------------------------------------------------------
'--- get all the globaltranslate values
writeout "Getting globaltranslate <br>"
GetGlobalTextValues str_translate_schema,"single"
'--------------------------------------------------------------------------------
'--- get all the Product Discovery values
writeout "Getting Product Discovery <br>"
GetGlobalTextValues str_prod_discovery,"single"
'--------------------------------------------------------------------------------
'--- get all the evaluation result text values
writeout "Getting evaluation result <br>"
GetGlobalTextValues str_evaluation_param,"single"
'--------------------------------------------------------------------------------
'--- get all the glossary text values
writeout "Getting glossary<br>"
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	

%]