compile invalid objects sorted parent-child wise (dependency wise)

compile invalid objects sorted parent-child wise (dependency wise)

set serveroutput on
declare
 v_invl_count number :=0;
 v_obj_type varchar2(32);
 v_obj_name varchar2(32);
 v_error_msg varchar2(1000);
begin
 select count(1)
   into v_invl_count
   from user_objects
  where object_type in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','TRIGGER','VIEW','MATERIALISED VIEW')
    and status = 'INVALID';

 if v_invl_count = 0 then
  dbms_output.put_line ('No invalid objects...');
 else
  for rec in (select distinct object_name,object_type
         from (select uo.status,uo.object_name,ud.name,uo.object_type,ud.type,ud.referenced_type,ud.referenced_name
                        from user_objects uo
            left join (select name,type,referenced_name,referenced_type
                                              from user_dependencies
                                             where referenced_type in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','TRIGGER','VIEW','MATERIALISED VIEW')) ud
                                        on (uo.object_name = ud.name)
                                     where uo.object_type in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','TRIGGER','VIEW','MATERIALISED VIEW'))
                             where status = 'INVALID'
                        start with referenced_name is null
                                or referenced_name in ('STANDARD','DBMS_STANDARD')
          connect by nocycle prior object_name = referenced_name
                 order siblings by object_name)
  loop
   begin
    v_obj_type := rec.object_type;
    if rec.object_type = 'PACKAGE BODY' then
     dbms_output.put_line ('Query :-'||'alter package '||rec.object_name ||' compile body');
     execute immediate 'alter package '||rec.object_name ||' compile body';
    else
     dbms_output.put_line ('Query :-'||'alter '||rec.object_type||' '||rec.object_name ||' compile');
     execute immediate 'alter '||rec.object_type||' '||rec.object_name ||' compile';
    end if;
   exception
    when others then
     dbms_output.put_line ('Error1 :-'||sqlerrm);
   end;
  end loop;
 end if;
exception
 when others then
  dbms_output.put_line ('Error2 :-'||sqlerrm);
end;
/

Load DTS packages -- VBS

'To create DTS packages from saved DTS files
' Author : Biju George
' Date   : 30-Jan-2013

Option Explicit

'On Error Resume Next
msgbox "Starting to load DTS packages..."

Dim srcFolder, dtsFolder, dtsPath ' Folder paths
Dim FSO, FLD, FIL ' File handles
Dim sqlServerName ' SQL server
Dim dtsPackage ' As DTS.Package2
Dim objConnection ' As DTS.Connection2
Dim pck_count ' Count flag
Dim objRegExpr,objRegExpr2 ' As regexp
Dim newPackageName, replaceString ' Ppackage names
Dim newDBInstance ' New DB instance in the connection property
Dim newDBInstancePswd ' New DB instance password in the connection property
Dim newSchm1Pswd,newSchm4Pswd,newSchm2Pswd,newSchm3Pswd ' New DB instance schema passwords in the connection property
Dim oTask, oProperty ' DTS tasks and properties
Dim newPathName, replaceString2 ' New batch file path
Dim logFileName, logFile, logFSO ' Log file handles

set logFSO = CreateObject("Scripting.FileSystemObject")

sqlServerName = "SQL_Server_Name"
pck_count = 0

Set objRegExpr = New regexp
objRegExpr.Pattern = "STORED_BKUP"
objRegExpr.Global = True
objRegExpr.IgnoreCase = True

Set objRegExpr2 = New regexp
objRegExpr2.Pattern = "TMPFLDR"
objRegExpr2.Global = True
objRegExpr2.IgnoreCase = True

Set FSO = CreateObject("Scripting.FileSystemObject")
Set srcFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Select the location folder of DTS files...", 0)
If (Not srcFolder Is Nothing) Then
dtsFolder = srcFolder.Items.Item.Path
Else
WScript.Quit
End If
Set srcFolder = Nothing
'MsgBox " Folder --> " & dtsFolder
dtsPath = dtsFolder & "\*.dts"
'MsgBox "DTS Path -->" & dtsPath

logFileName = ""
logFileName = dtsFolder & "\" & "DTS_creation_log.log"

If  logFSO.FileExists(logFileName) Then 'FileSys.DeleteFile(DocFileName)
' logFSO.GetFile (logFileName)
set logFile = logFSO.OpenTextFile (logFileName,8) 'OpenTextFile(strFile [,IOMode (8=append, 1=Read, 2=Write) [,Create (True/False)]])
else
logFSO.CreateTextFile (logFileName)
set logFile = logFSO.OpenTextFile (logFileName,2)
end if
logFile.write (vbCrLf)
logFile.write ("---------DTS Creation logging on "  & Date & "---------")
logFile.write (vbCrLf)
logFile.write ("Folder where DTS files reside : " & dtsFolder)
logFile.write (vbCrLf)
logFile.write ("Complete path of DTS files : " & dtsPath)

newDBInstance = inputbox("Provide the new database instance SID (like MYSID1 or MYSID2...)","New Database Instance SID")
'newDBInstancePswd = inputbox("Provide the new database instance password","New Database Instance Password")

newSchm1Pswd = inputbox("Provide the new DB instance My_Schema1 schema password","My_Schema1 schema Password")
newSchm4Pswd = inputbox("Provide the new DB instance My_Schema4 schema password","My_Schema4 schema Password")
newSchm2Pswd = inputbox("Provide the new DB instance My_Schema2 schema password","My_Schema2 schema Password")
newSchm3Pswd = inputbox("Provide the new DB instance My_Schema3 schema password","My_Schema3 schema Password")

'replaceString = "CRTD_FRM_BKUP"
replaceString = inputbox("Provide the new package name pattern (like My_new_DTS_package...)","New Package Name Pattern")
'replaceString2 = "TEMPFOLDER"
replaceString2 = inputbox("Provide the new folder for batch files (like my_folder3 or my_folder4...)" _
                          & chr(13) & "viz. D:\My folder1\My folder2\My folder3\my_batch_file.bat" _
                          & chr(13) & "to D:\Your folder1\Your folder2\Your folder3\your_batch_file.bat","New Folder/Path")

logFile.write (vbCrLf)
logFile.write ("New DB Instance               : " & newDBInstance)
logFile.write (vbCrLf)
logFile.write ("My_Schema1 Password : " & newSchm1Pswd)
logFile.write (vbCrLf)
logFile.write ("My_Schema4 Password : " & newSchm4Pswd)
logFile.write (vbCrLf)
logFile.write ("My_Schema2 Password : " & newSchm2Pswd)
logFile.write (vbCrLf)
logFile.write ("My_Schema3 Password : " & newSchm3Pswd)
logFile.write (vbCrLf)
logFile.write ("New DTS package name pattern : " & replaceString)
logFile.write (vbCrLf)
logFile.write ("New folder name for batch files : " & replaceString2)

'Get a reference to the folder you want to search
set FLD = FSO.GetFolder(dtsFolder)

'loop through the folder and get the file names
For Each FIL in FLD.Files
' MsgBox FIL.Name
' MsgBox FIL.Type
if UCase(right(FIL.Name, 4)) = ".DTS" then
Set dtsPackage = CreateObject("DTS.Package2")
dtsPackage.LoadFromStorageFile FIL.path,""
logFile.write (vbCrLf)
logFile.write ("DTS file name : " & FIL.Name)
logFile.write (vbCrLf)
logFile.write ("File type : " & FIL.Type)
logFile.write (vbCrLf)
logFile.write ("DTS file path : " & FIL.path)
logFile.write (vbCrLf)
logFile.write ("DTS package name in file : " & dtsPackage.Name)
Set objConnection = dtsPackage.Connections
For Each objConnection In dtsPackage.Connections
      if objConnection.DataSource = "TEMPDB" then
      objConnection.DataSource = newDBInstance
if objConnection.userid = "My_Schema1" then
objConnection.Password = newSchm1Pswd
elseif objConnection.userid = "My_Schema4" then
objConnection.Password = newSchm4Pswd
elseif objConnection.userid = "My_Schema2" then
objConnection.Password = newSchm2Pswd
elseif objConnection.userid = "My_Schema3" then
objConnection.Password = newSchm3Pswd
end if
end if
Next

newPathName = ""

'changes in task properties
Set oTask = dtsPackage.Tasks
For each oTask in dtsPackage.Tasks
Set oProperty = oTask.Properties
For Each oProperty In oTask.Properties
if oProperty.Name = "ProcessCommandLine" then
'MsgBox "PropertyName: " &  oProperty.Name & " Value="  & oProperty.Value
if objRegExpr2.Test(cstr(oProperty.Value)) then
                newPathName = objRegExpr2.replace(cstr(oProperty.Value),replaceString2)
logFile.write (vbCrLf)
logFile.write ("Existing path for batch file : " & oProperty.Value)
logFile.write (vbCrLf)
logFile.write ("New path for batch file : " & newPathName)
oProperty.Value = newPathName
end if
end if
Next
Next

' dtsPackage.SaveToSQLServer sqlServerName, , , 256, "", "", ""

newPackageName = ""

if objRegExpr.Test(cstr(dtsPackage.Name)) then
                newPackageName  = objRegExpr.replace(cstr(dtsPackage.Name),replaceString)
logFile.write (vbCrLf)
logFile.write ("New DTS pacakage name : " & newPackageName)
dtsPackage.SaveToSQLServerAs newPackageName, sqlServerName, , , 256, "", "", , nothing, 0
end if
Set dtsPackage = Nothing
Set objConnection = Nothing
Set oTask = Nothing
Set oProperty = Nothing
     
        pck_count = pck_count + 1
end if
next

'msgbox "Total packages created : " & pck_count
logFile.write (vbCrLf)
logFile.write ("Total packages created : " & pck_count)
logFile.write (vbCrLf)

Set FSO = Nothing

' Main = DTSTaskExecResult_Success

msgbox "All Packages loaded. Check log file " & logFileName & " for details..."

logFile.close
Set logFile = Nothing
Set logFSO = Nothing

If Err.Number <> 0 Then
logFile.write (vbCrLf)
logFile.write ("Error : " & Err.Description)
Err.Clear
End If
WScript.Quit

Save DTS packages -- VBS

'To save DTS packages as per a certain search pattern for package names
' Author : Biju George
' Date   : 30-Jan-2013

Option Explicit

'On Error Resume Next
msgbox "Starting to save DTS packages..."

Dim srcFolder, dtsFolder ' Folder paths
Dim FSO, FLD, FIL ' File handles
dim sqlServerName ' SQL server
Dim dtsPackage ' As DTS.Package2
Dim objConnection ' As DTS.Connection2
Dim pck_count,pck_count2 ' Count flag
Dim objRegExpr,objRegExpr2 ' As regexp
Dim newPackageName, replaceString ' Ppackage names
dim dtsFileName ' DTS file name
Dim dtsFile ' DTS file
Dim oApplication   ' As DTS.Application
Dim oPackageSQLServer   ' As DTS.PackageSQLServer
Dim oPackageInfos   ' As DTS.PackageInfos
Dim oPackageInfo     ' As DTS.PackageInfo
Dim existingDBInstance ' Existing DB instance in the connection property
Dim oTask, oProperty ' DTS tasks and properties
Dim newPathName, replaceString2 ' New batch file path
Dim logFileName, logFile, logFSO ' Log file handles
Dim pckgPattern,btchFilePath ' Regular expression search strings

set logFSO = CreateObject("Scripting.FileSystemObject")

sqlServerName = "SQL_Server_Name"
pck_count = 0
pck_count2 = 0

pckgPattern = inputbox("Provide the source package name pattern (like My_DTS_package...)","Source Package Name Pattern")
btchFilePath = inputbox("Provide the source package batch file folder name (like D:\my_folder1\my_folder2\...)","Source Batch File Folder Name")

Set objRegExpr = New regexp
'objRegExpr.Pattern = "ABIJU"
objRegExpr.Pattern = pckgPattern
objRegExpr.Global = True
objRegExpr.IgnoreCase = True

Set objRegExpr2 = New regexp
'objRegExpr2.Pattern = "my_folder1"
objRegExpr2.Pattern = btchFilePath
objRegExpr2.Global = True
objRegExpr2.IgnoreCase = True

existingDBInstance = inputbox("Provide the existing database instance SID (like MYSID1 or MYSID2...)","Existing Database Instance SID")

Set FSO = CreateObject("Scripting.FileSystemObject")
Set srcFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Select the location folder for DTS file storage...", 0)
If (Not srcFolder Is Nothing) Then
dtsFolder = srcFolder.Items.Item.Path
Else
WScript.Quit
End If
Set srcFolder = Nothing
'MsgBox " Folder --> " & dtsFolder

logFileName = ""
logFileName = dtsFolder & "\" & "DTS_storage_log.log"

If  logFSO.FileExists(logFileName) Then 'FileSys.DeleteFile(DocFileName)
' logFSO.GetFile (logFileName)
set logFile = logFSO.OpenTextFile (logFileName,8) 'OpenTextFile(strFile [,IOMode (8=append, 1=Read, 2=Write) [,Create (True/False)]])
else
logFSO.CreateTextFile (logFileName)
set logFile = logFSO.OpenTextFile (logFileName,2)
end if
logFile.write (vbCrLf)
logFile.write ("---------DTS Storage logging on "  & Date & "---------")
logFile.write (vbCrLf)
logFile.write ("Folder where DTS files are stored : " & dtsFolder)
logFile.write (vbCrLf)
logFile.write ("Name pattern of DTS Packages to be saved : " & pckgPattern)
logFile.write (vbCrLf)
logFile.write ("Name of folder where batch files reside : " & btchFilePath)

'Get a reference to the folder you want to search
set FLD = FSO.GetFolder(dtsFolder)

Set oApplication = CreateObject("DTS.Application")
Set oPackageSQLServer = oApplication.GetPackageSQLServer(sqlServerName,"" ,"" , 256) 'DTSSQLStgFlag_UseTrustedConnection = 256

Set oPackageInfos = oPackageSQLServer.EnumPackageInfos("", True, "")  'EnumPackageInfos (PackageName,ReturnLatest,PackageID )

Set oPackageInfo = oPackageInfos.Next

pck_count = 0

'msgbox "match found numer#0 : " & pck_count

Do Until oPackageInfos.EOF

Set dtsPackage = CreateObject("DTS.Package2")

dtsPackage.LoadFromSQLServer sqlServerName, , ,256, , , , oPackageInfo.Name

'msgbox "match found numer#1 : " & pck_count

'msgbox "pkc count : " & pck_count & ". Package : " & cstr(oPackageInfo.Name)

replaceString = "STORED_BKUP"
replaceString2 = "TMPFLDR"
newPackageName = ""

        if objRegExpr.Test(cstr(oPackageInfo.Name)) then

logFile.write (vbCrLf)
logFile.write ("DTS package getting saved : " & oPackageInfo.Name)

                newPackageName  = objRegExpr.replace(cstr(oPackageInfo.Name),replaceString)

'msgbox "match found. pck count : " & pck_count & ". Package : " & cstr(oPackageInfo.Name) & ". New : " & newPackageName      

                pck_count = pck_count + 1
dtsFileName = ""
dtsFileName = dtsFolder & "\"& newPackageName & ".dts"

logFile.write (vbCrLf)
logFile.write ("New Package Name in the file : " & newPackageName)
logFile.write (vbCrLf)
logFile.write ("DTS File Name in storage location : " & dtsFileName)

Set objConnection = dtsPackage.Connections
For Each objConnection In dtsPackage.Connections
'       if objConnection.DataSource = existingDBInstance then
logFile.write (vbCrLf)
logFile.write ("Existing DB instance SID in the DTS packages : " & objConnection.DataSource)
      objConnection.DataSource = "TEMPDB"
objConnection.Password = ""
' end if
Next

newPathName = ""

'changes in task properties
Set oTask = dtsPackage.Tasks
For each oTask in dtsPackage.Tasks
Set oProperty = oTask.Properties
For Each oProperty In oTask.Properties
if oProperty.Name = "ProcessCommandLine" then
'MsgBox "PropertyName: " &  oProperty.Name & " Value="  & oProperty.Value
if objRegExpr2.Test(cstr(oProperty.Value)) then
                newPathName = objRegExpr2.replace(cstr(oProperty.Value),replaceString2)
logFile.write (vbCrLf)
logFile.write ("Existing path for batch file : " & oProperty.Value)
logFile.write (vbCrLf)
logFile.write ("New path for batch file : " & newPathName)
oProperty.Value = newPathName
end if
end if
Next
Next

Set objConnection = Nothing


dtsPackage.SaveToStorageFileAs newPackageName,dtsFileName

Set dtsPackage = Nothing
Set objConnection = Nothing
Set oTask = Nothing
Set oProperty = Nothing
else
'logFile.write (vbCrLf)
'logFile.write ("DTS pacakge match not found : " + dtsPackage.Name)
pck_count2 = pck_count2 + 1
end if
Set oPackageInfo = oPackageInfos.Next

'if pck_count = 0 then
'msgbox "just quit without looping"
'exit do
'end if
Loop

logFile.write (vbCrLf)
logFile.write ("Total packages saved : " & pck_count)
logFile.write (vbCrLf)
logFile.write ("Total packages that did not match the criteria : " & pck_count2)
logFile.write (vbCrLf)

'Clean up and free resources
Set oApplication = Nothing
Set oPackageSQLServer = Nothing
Set oPackageInfos = Nothing
Set oPackageInfo = Nothing
Set dtsPackage = Nothing
Set FSO = Nothing

' Main = DTSTaskExecResult_Success

msgbox "All Packages saved. Check log file " & logFileName & " for details..."

logFile.close
Set logFile = Nothing
Set logFSO = Nothing

If Err.Number <> 0 Then
logFile.write (vbCrLf)
logFile.write ("Error : " & Err.Description)
Err.Clear
End If
WScript.Quit

CSV to XLS -- VBS

'Convert .csv file to an .xls file
'Author – bijunator


Option Explicit
On Error Resume Next

Const xlDelimited = 1
Const xlNormal = -4143
Const xlTextFormat = 2
Const xlGeneralFormat = 1
Const xlTextQualifierNone = -4142
Const xlTextQualifierDoubleQuote = 1
Const xlYMDFormat = 5
Const xlWindows = 2
Const xlOverwriteCells = 0

Dim source_filename, destination_filename, exlObj, wBkObj, wrkShtObj
'filename = WScript.Arguments.Item(0)

source_filename = "C:\temp\my_file_name.csv"
destination_filename = "C:\temp\my_file_name_"

Set exlObj = CreateObject("Excel.Application")
exlObj.Application.DisplayAlerts = False
exlObj.Application.Visible = False
Set wBkObj = exlObj.workbooks.Add()
Set wrkShtObj = wBkObj.Worksheets(1)

With wrkShtObj.QueryTables.Add("TEXT;" & filename, wrkShtObj.Range("$A$1"))
 .FieldNames = True
 .RowNumbers = False
 .FillAdjacentFormulas = False
 .RefreshOnFileOpen = False
 .RefreshStyle = xlOverwriteCells
 .SavePassword = False
 .SaveData = True
 .AdjustColumnWidth = False
 .RefreshPeriod = 0
 .TextFilePromptOnRefresh = False
 .TextFilePlatform = 65001
 .TextFileStartRow = 1
 .TextFileParseType = xlDelimited
 .TextFileTextQualifier = xlTextQualifierNone
 .TextFileConsecutiveDelimiter = False
 .TextFileTabDelimiter = False
 .TextFileSemicolonDelimiter = True
 .TextFileCommaDelimiter = False
 .TextFileSpaceDelimiter = False
 .TextFileColumnDataTypes = Array(1,1,5,1,1,1,2,2,1,2,1,2) 'indicates 12 columns where 1 are general format, 2 are text format and 5 is date in YMD format
 .TextFileTrailingMinusNumbers = True
 .Refresh False
end with
    wBkObj.ActiveSheet.UsedRange.EntireColumn.Autofit()  
    wBkObj.ActiveSheet.Name = "my_sheet_name"
    wBkObj.ActiveSheet.Columns("C").Select
    exlObj.Selection.NumberFormat = "yyyy-mm-dd"
    wBkObj.SaveAs  destination_filename + CStr(Year(Date)) + CStr(Month(Date)) + CStr(Day(Date)) + "_"+ CStr(Hour(Time)) +CStr(Minute(Time))+CStr(Second(Time)), xlNormal
    exlObj.Quit

If Err.Number <> 0 Then
Msgbox "Error : " & Err.Description
Err.Clear
End If
WScript.Quit

Comment extractor -- VBS

'Extract comments from word documents and save it in excel file
'Author – bijunator

Option Explicit

On Error Resume Next

Dim appObj, exlObj, wBkObj, wShtObj, wrdObj

Dim p, q, r

Dim srcFolder, strFolder, strFile, wordDoc, fileName, docPath, sheetName

Dim myStr

Dim FSO, FLD, FIL

Dim totCmnts, cmntObj, cmntCntr

Dim wordDocName

Dim cmnDlgObj

Dim bullet

Dim response

bullet = Chr(10) & " " & Chr(149) & " "

Do

    response = InputBox("Please enter 1 if want to extract comments from all word files inside a folder" _

    & chr(13) & "Please enter 2 if you want to extract comments from individual word files" _

    & Chr(10) & bullet & "1) Folder" & bullet & "2) File" & Chr(10), "Choose your option...")

    If response = "" Then WScript.Quit  'Detect Cancel

    If IsNumeric(response) Then

      If response =1 or response = 2 Then Exit Do 'Detect value response.

    End If

      MsgBox "You must enter either numeric value 1 or 2", 48, "Invalid Entry"

Loop

MsgBox "You chose : " & response, 64, "Selection..."

'Create the filesystem object

Set FSO = CreateObject("Scripting.FileSystemObject")

Set wrdObj = createObject("Word.Application")

Set exlObj = CreateObject("Excel.Application")

If response = 1 Then '************************first option************************

'MsgBox "Inside option 1"

Set srcFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder...", 0)

If (Not srcFolder Is Nothing) Then

strFolder = srcFolder.Items.Item.Path

Else

WScript.Quit

End If

Set srcFolder = Nothing

'MsgBox " Folder --> " & strFolder

fileName = strFolder & "\" & "review_comments_details.xls"

'MsgBox "XLFileName -->" & fileName

q = 0

docPath = strFolder & "\*.doc"

'MsgBox "Word Path -->" & docPath

'Get a reference to the folder you want to search

set FLD = FSO.GetFolder(strFolder)

'loop through the folder and get the file names

For Each Fil In FLD.Files

'     MsgBox Fil.Name

'     MsgBox Fil.Type

     if Fil.Type = "Microsoft Word Document" or Fil.Type = "Microsoft Office Word Document" or Fil.Type = "Word 2007 Document" then

         Set wordDoc = wrdObj.Documents.Open(strFolder & "\" & Fil.Name)

        wrdObj.Application.visible = false

        'wrdObj.Application.DisplayAlerts = False

         With wordDoc

            wordDoc.Activate

            wordDoc.ActiveWindow.ActivePane.View.Type = 3

'           MsgBox "opened word document -->" & wordDoc

           sheetName = Mid(Fil.Name, 1, InStrRev(Fil.Name,".") - 1)

'           MsgBox "Sheet Name -->" & sheetName

           p = 0

           r = 1

           totCmnts = wordDoc.Comments.count

           If totCmnts > 0 Then

              q = q + 1

'              MsgBox "Document No -->" & q

              'Start Excel

'              MsgBox "No of comments -->" & totCmnts

              If q = 1 Then

'                 MsgBox "Opening Excel Workbook"

                 Set wBkObj = exlObj.workbooks.Add()

              End If

'              MsgBox "Adding Workbook Sheet --> " & q

               Set wShtObj = wBkObj.Worksheets(q)

               wShtObj.Name = sheetName

              wShtObj.Cells(r, 1) = "Serial No"

               wShtObj.Cells(r, 2) = "Page No"

               wShtObj.Cells(r, 3) = "Line No"

               wShtObj.Cells(r, 4) = "Author"

               wShtObj.Cells(r, 5) = "Comment Text"

               wShtObj.Cells(r, 6) = "Status"

              For Each cmntObj In wordDoc.Comments

                 r = r + 1

                 p = p + 1

                 wShtObj.Cells(r, 1) = p

                 wShtObj.Cells(r, 2) = cmntObj.Scope.Paragraphs(1).Range.Information(3)

                 wShtObj.Cells(r, 3) = cmntObj.Scope.Paragraphs(1).Range.Information(10)

                 wShtObj.Cells(r, 4) = cmntObj.Author

                 wShtObj.Cells(r, 5) = cmntObj.Range.Text

'                 MsgBox "Comment Serial --> " & p

              Next

           End If

        end with

        wordDoc.Close

     end if

Next

wrdObj.quit

If Not exlObj Is Nothing Then

      MsgBox "Saving Excel Workbook"

     wBkObj.SaveAs fileName

End If

exlObj.quit

ElseIf response = 2 then '************************second option************************

'MsgBox "Inside option 2"

docPath = FSO.GetParentFolderName(WScript.ScriptFullName)

Set cmnDlgObj = CreateObject("UserAccounts.CommonDialog")

cmnDlgObj.Filter = "Word Files|*.doc;*.docx"

cmnDlgObj.FilterIndex = 1

cmnDlgObj.InitialDir = docPath

cmnDlgObj.Flags = 1

If cmnDlgObj.ShowOpen = 0 Then

  MsgBox "No document chosen!" & vbCrLf & "Exiting the program..."

  exlObj.Quit

  wrdObj.Quit

  WScript.Quit

End If

wordDocName = cmnDlgObj.FileName

'Msgbox "word file -->" & wordDocName

Set wordDoc = wrdObj.Documents.Open(wordDocName)

Set wBkObj = exlObj.workbooks.Add()

docPath = FSO.GetParentFolderName(wordDocName)

sheetName = FSO.GetFileName(wordDocName)

'MsgBox "position of dot -->" & InStrRev(sheetName,".")

sheetName = Mid(sheetName, 1, InStrRev(sheetName,".") - 1)

fileName = docPath & "\" & sheetName & "_review_comments_details.xls"

'MsgBox "excel name -->" & fileName

exlObj.Application.DisplayAlerts = False

exlObj.Application.Visible = False

wrdObj.Application.DisplayAlerts = False

wrdObj.Application.Visible = False

totCmnts = wordDoc.Comments.Count

If totCmnts > 0 Then

      wBkObj.Sheets(1).cells(1, 1) = "Serial No"

      wBkObj.Sheets(1).cells(1, 2) = "Page No"

      wBkObj.Sheets(1).cells(1, 3) = "Line No"

      wBkObj.Sheets(1).cells(1, 4) = "Author"

      wBkObj.Sheets(1).cells(1, 5) = "Comment Text"

      wBkObj.Sheets(1).cells(1, 6) = "Status"

      For cmntCntr = 1 to totCmnts

            wBkObj.sheets(1).cells(cmntCntr+1, 1) = cmntCntr

            wBkObj.sheets(1).cells(cmntCntr+1, 2) = wordDoc.Comments(cmntCntr).Scope.Information(3)

            wBkObj.sheets(1).cells(cmntCntr+1, 3) = wordDoc.Comments(cmntCntr).Scope.Information(10)

            wBkObj.sheets(1).cells(cmntCntr+1, 4) = wordDoc.Comments(cmntCntr).Author

            wBkObj.sheets(1).cells(cmntCntr+1, 5) = wordDoc.Comments(cmntCntr).Range.Text

      Next

End If

wrdObj.quit

If Not exlObj Is Nothing Then

     MsgBox "Saving Excel Workbook"

     wBkObj.SaveAs fileName

End If

exlObj.quit

End if '************************end of options************************

If Err.Number <> 0 Then

Msgbox "Error : " & Err.Description

Err.Clear

End If

WScript.Quit