No code changes. It was working fine 2 days back. From yesterday, I am
receiving the error. PFB the entire asp page code.
<%@ LANGUAGE=VBScript %>
<% OPTION EXPLICIT %>
<% ON ERROR RESUME NEXT %>
<html>
<body>
<%
Dim IntRecordCount
Dim rsBillingRpt
Dim intLoop_i
Dim intLoop_j
Dim intIndexRow
Dim intHours
Dim strDay
'Dim objConnIntratime
Dim month_id
dim strSp
Dim strAsscID
Dim strRange
Dim objWorkSheet
Dim intTotalNoOfSheets
Dim intCurrentSheet
Dim strCurrentSheetName
Dim intNoOfResources
Dim intTaskRow
Dim intTaskCol
dim intTaskSNo
Dim ExcelApp
Dim ExcelBook
Dim PROJECT_NAME_OLD
Dim ASSOCIATE_NAME_OLD
Dim LOCATION_OLD
Dim TASK_NAME
Dim PROJECT_NAME
Dim ASSOCIATE_NAME
Dim LOCATION
Dim PROJECT_ID
Dim CLIENT_MANAGER
Dim DIVISION
Dim SUB_PROJECT_ID
Dim SUB_PROJECT_NAME
Dim SUB_TASK_ID
Dim SUB_TASK_NAME
Dim ASSOCIATE_ID
Dim NO_OF_DAYS
Dim total_HOURS
Dim strServerPath
Dim strExcelFileName
Dim strExcelFilePath
Dim intResourceHours
Dim intProjectHours
Dim blnNewResource
Dim blnNewProject
Dim strTemp
Dim intSaturdayStCol
Dim intSundayCol
Dim intResourceStRow
Dim intResourceEndRow
Dim blnSaturDayFound
Dim blnSundayFound
Dim intHolidayColor
Dim strResourceLink
Dim intNoOfRecords
Dim iArrHoliday
Dim iArrLeave
Dim strHoliday
Dim strLeave
Dim iTemp
Dim iHeaderRow
%>
<!-- #include file="../include/common.asp"-->
<!-- #include file="../include/timeSheet.inc" -->
<%
strResourceLink = ""
intTotalNoOfSheets = 3
intCurrentSheet = 1
intNoOfResources = 0
blnSaturDayFound = false
blnSundayFound = false
intSaturdayStCol = 4
intSundayCol = 0
intHolidayColor = lightGray
intLoop_i = 2
intIndexRow = 1
PROJECT_NAME = ""
ASSOCIATE_NAME = ""
intResourceHours = 0
intProjectHours = 0
blnNewResource = false
blnNewProject = false
intNoOfRecords = 0
strHoliday = ""
strLeave = ""
month_id = Request("MONTH_YEAR")
if ( month_id = "" or len(month_id) = 0 ) THEN
Response.Write ("Month and Year is mandatory to create billing report.")
response.end
end if
if ( NOT isdate("01-" & month_id ) ) THEN
Response.Write ("<BR><Font color=red size=5>Invalid Month and Year.</Font>")
response.end
end if
'For Testing
'month_id = "Jul-2006"
strSp = SP_MNTH_BILLING_REPORT & " '" & month_id &"'"
'OPEN EXCEL Application
Set ExcelApp = CreateObject("Excel.Application")
if ( Err.number <> 0) THEN
Response.Write ("Err.number[" & Err.number & "] Err.Description[" &
Err.Description & "]")
response.end
end if
ExcelApp.Application.Visible = True
Set ExcelBook = ExcelApp.Workbooks.Add
'Set the first sheet as the index sheet and insert sheet headings
ExcelBook.Worksheets("sheet1").Name = "Index"
strCurrentSheetName = "Index"
strRange = "B" & intIndexRow & ":E" & intIndexRow
ExcelBook.Worksheets("Index").Cells(intIndexRow,3).value = "Timesheet for
the month of " & month_id
SetSelection strCurrentSheetName, intIndexRow, "B", "E"
SetAllBorder ExcelApp.Selection, xlNone, true, true, xlCenter, xlBottom
intIndexRow = intIndexRow + 1
intIndexRow = intIndexRow + 1
ExcelBook.Worksheets("Index").Cells(intIndexRow,3).value = "Billing Details"
SetSelection strCurrentSheetName, intIndexRow, "B", "E"
SetAllBorder ExcelApp.Selection, lightOrange, true, true, xlCenter, xlBottom
intIndexRow = intIndexRow + 1
ExcelBook.Worksheets("Index").Cells(intIndexRow,2).value = "S.NO"
ExcelBook.Worksheets("Index").Cells(intIndexRow,3).value = "Resource Name"
ExcelBook.Worksheets("Index").Cells(intIndexRow,4).value = "Location"
ExcelBook.Worksheets("Index").Cells(intIndexRow,5).value = "Billable Hours"
ExcelBook.Worksheets("Index").Columns("C:C").ColumnWidth = 30
ExcelBook.Worksheets("Index").Columns(BillableHoursCol & ":" &
BillableHoursCol).ColumnWidth = 13.2
SetSelection strCurrentSheetName, intIndexRow, "B", "E"
SetAllBorder ExcelApp.Selection, lightOrange, true, false, xlLeft, xlBottom
intIndexRow = intIndexRow + 1
'OPEN SQL CONNECTION
'Set objConnIntratime = server.createobject("adodb.connection")
'objConnIntratime.ConnectionString = "DRIVER={SQL
Server};SERVER=HQSQ5610D;DATABASE=PUBS;UID=Intratime;PWD=Intratime;"
'objConnIntratime.ConnectionString = "DRIVER={SQL
Server};SERVER=HQWLC831428\MYSQL_INSTANCE;DATABASE=PUBS;UID=TAPPMO;PWD=TAPPMO;"
''Response.Write("<BR>" & objConnIntratime.ConnectionString)
'objConnIntratime.Open
'objConnIntratime.CommandTimeout = 360
EstablishConnection
set rsBillingRpt = objConnIntratime.execute(strSp)
'LOOP through the result_set
DO WHILE NOT rsBillingRpt.EOF
intNoOfRecords = intNoOfRecords + 1
PROJECT_NAME_OLD = PROJECT_NAME
ASSOCIATE_NAME_OLD = ASSOCIATE_NAME
LOCATION_OLD = LOCATION
'OLD VALUE SAVED FOR FOLLOWING FIELDS
PROJECT_NAME =rsBillingRpt("PROJECT_NAME")
ASSOCIATE_NAME =rsBillingRpt("ASSOCIATE_LAST_NAME") & ", " &
rsBillingRpt("ASSOCIATE_FIRST_NAME")
PROJECT_ID =cint(rsBillingRpt("PROJECT_ID"))
CLIENT_MANAGER =rsBillingRpt("CLIENT_MANAGER")
LOCATION =rsBillingRpt("LOCATION")
'check if new project
if PROJECT_NAME_OLD <> PROJECT_NAME then
blnNewProject = true
blnNewResource = true
intIndexRow = intIndexRow + 1
ExcelBook.Worksheets("Index").Cells(intIndexRow,3).value = PROJECT_NAME &
"(" & CLIENT_MANAGER & ")"
SetSelection "Index", intIndexRow, "B", "E"
SetAllBorder ExcelApp.Selection, lightBlue, true, false, xlLeft, xlBottom
intIndexRow = intIndexRow + 1
if( PROJECT_NAME_OLD <> "" ) THEN
Response.Write("<BR> NEW PROJECT intResourceHours[" & intResourceHours &
"] ASSOCIATE_NAME[" & ASSOCIATE_NAME & "] ASSOCIATE_NAME_OLD[" &
ASSOCIATE_NAME_OLD & "]")
end if
intProjectHours = 0
intResourceHours = 0
else
blnNewProject = false
blnNewResource = false
end if
'
'OLD VALUE NOT SAVED FOR FOLLOWING FIELDS
DIVISION =rsBillingRpt("DIVISION")
SUB_PROJECT_ID =rsBillingRpt("SUB_PROJECT_ID")
SUB_PROJECT_NAME =rsBillingRpt("SUB_PROJECT_NAME")
SUB_TASK_ID =rsBillingRpt("SUB_TASK_ID")
SUB_TASK_NAME =rsBillingRpt("SUB_TASK_NAME")
ASSOCIATE_ID =rsBillingRpt("ASSOCIATE_ID")
NO_OF_DAYS = cint(rsBillingRpt("NO_OF_DAYS") )
'total_HOURS = cint(rsBillingRpt("total_HOURS") )
total_HOURS = cdbl(rsBillingRpt("total_HOURS"))
IF ( SUB_TASK_NAME = "Total Hours" ) THEN
ExcelBook.Worksheets("Index").Cells(intIndexRow,2).value = intNoOfResources
ExcelBook.Worksheets("Index").Cells(intIndexRow,3).value =
ASSOCIATE_NAME_OLD
ExcelBook.Worksheets("Index").Cells(intIndexRow,4).value = LOCATION_OLD
ExcelBook.Worksheets("Index").Cells(intIndexRow,5).value = intResourceHours
SetSelection "Index", intIndexRow, "B", "E"
SetAllBorder ExcelApp.Selection, xlNone, false, false, xlLeft, xlBottom
'SET hyperLink in the INDEX Sheet
strTemp = "C" & intIndexRow
ExcelBook.Worksheets("Index").Select
ExcelBook.Worksheets("Index").Range(strTemp).Select
ExcelApp.ActiveSheet.Hyperlinks.Add ExcelApp.Selection, "",
strResourceLink, ASSOCIATE_NAME_OLD
Response.Write ("<BR>strResourceLink[" & strResourceLink & "]
ASSOCIATE_NAME_OLD[" & ASSOCIATE_NAME_OLD & "]")
intIndexRow = intIndexRow + 1
END IF
'check new resource
if ASSOCIATE_NAME_OLD <> ASSOCIATE_NAME then
blnNewResource = true
if (ASSOCIATE_NAME_OLD <> "" ) then
Response.Write("<BR> NEW RESOURCE intResourceHours[" & intResourceHours &
"] ASSOCIATE_NAME[" & ASSOCIATE_NAME & "] ASSOCIATE_NAME_OLD[" &
ASSOCIATE_NAME_OLD & "]")
end IF
intResourceHours = 0
intNoOfResources = intNoOfResources + 1
else
blnNewResource = false
end if
'New project create new worksheet
if ( blnNewProject ) then
intCurrentSheet = intCurrentSheet + 1
iHeaderRow = 1
intTaskRow = 5
intTaskCol = 2
ExcelBook.Worksheets("Sheet2").Select
ExcelBook.Worksheets.add
ExcelBook.Worksheets(intCurrentSheet).Columns(TaskNameCol & ":" &
TaskNameCol).ColumnWidth = 30
strCurrentSheetName = mid(PROJECT_NAME,1,30)
ExcelBook.Worksheets(intCurrentSheet).Name = strCurrentSheetName
ExcelBook.Worksheets(intCurrentSheet).Cells(iHeaderRow,3).value =
"Timesheet for the month of " & month_id
SetSelection strCurrentSheetName, iHeaderRow, "B", "AH"
SetAllBorder ExcelApp.Selection, xlNone, true, true, xlCenter, xlBottom
iHeaderRow = iHeaderRow + 1
ExcelBook.Worksheets(intCurrentSheet).Cells(iHeaderRow,4).value = "Legend"
SetSelection strCurrentSheetName, iHeaderRow, "D", "G"
SetAllBorder ExcelApp.Selection, xlNone, true, true, xlCenter, xlBottom
iHeaderRow = iHeaderRow + 1
ExcelBook.Worksheets(intCurrentSheet).Cells(iHeaderRow,5).value = "Holiday"
SetSelection strCurrentSheetName, iHeaderRow, "E", "H"
SetAllBorder ExcelApp.Selection, xlNone, true, true, xlCenter, xlBottom
ExcelBook.Worksheets(intCurrentSheet).Cells(iHeaderRow,13).value = "Leave"
SetSelection strCurrentSheetName, iHeaderRow, "M", "P"
SetAllBorder ExcelApp.Selection, xlNone, true, true, xlCenter, xlBottom
ExcelBook.Worksheets(intCurrentSheet).Cells(intTaskRow, 3).Value = "Client
Manager: " & CLIENT_MANAGER
'Set the new sheets font Size to default size
ExcelBook.Worksheets(intCurrentSheet).Cells.Select
ExcelApp.Selection.Font.Size = NormalFont
SetSelection strCurrentSheetName, intTaskRow, "B", "AJ"
SetFont BigFont, DarkBlue, true
ExcelApp.WorkSheets(intCurrentSheet).Cells(iHeaderRow,
4).Interior.ColorIndex = intHolidayColor
ExcelApp.WorkSheets(intCurrentSheet).Cells(iHeaderRow,
12).Interior.ColorIndex = 39
Dim objRange
Set objRange = ExcelApp.Range("C1").EntireColumn
objRange.WrapText = TRUE
intTaskRow = intTaskRow + 1
ExcelBook.Worksheets(intCurrentSheet).Columns("D
").ColumnWidth = 30
END if
IF (blnNewResource OR blnNewProject) then
intTaskRow = intTaskRow + 1
ExcelBook.Worksheets(intCurrentSheet).Cells(intTaskRow, 3).Value =
"Resource Name: " & ASSOCIATE_NAME
strResourceLink = "'" & strCurrentSheetName & "'!C" & intTaskRow
SetSelection strCurrentSheetName, intTaskRow, "B", "AJ"
SetFont NormalFont, Black, true
intTaskRow = intTaskRow + 1
ExcelBook.Worksheets(intCurrentSheet).Cells(intTaskRow, 3).Value =
"Location: " & LOCATION
SetSelection strCurrentSheetName, intTaskRow, "B", "AJ"
SetFont NormalFont, Black, true
intTaskRow = intTaskRow + 1
intResourceStRow = intTaskRow
ExcelBook.Worksheets(intCurrentSheet).Cells(intTaskRow, 2).Value = "S.NO"
ExcelBook.Worksheets(intCurrentSheet).Cells(intTaskRow, 3).Value = "Task
Name"
for intLoop_i = 1 to NO_OF_DAYS
strDay = rsBillingRpt("day_" & intLoop_i & "_day") & ""
if ( (ucase(strDay) = "SAT") and (not blnSaturDayFound)) then
blnSaturDayFound = true
intSaturdayStCol = intLoop_i + 3
elseif ((ucase(strDay) = "SUN") and (not blnSaturDayFound) and (not
blnSundayFound)) then
intSundayCol = intLoop_i + 3
blnSundayFound = true
end if
strDay = strDay & " " & intLoop_i
'ExcelBook.Worksheets(intCurrentSheet).Cells(intTaskRow, (intLoop_i
+ 3)).value = strDay
if intLoop_i <= 9 then
ExcelBook.Worksheets(intCurrentSheet).Cells(intTaskRow, (intLoop_i +
3)).value = cstr(intLoop_i) & space(1)
else
ExcelBook.Worksheets(intCurrentSheet).Cells(intTaskRow, (intLoop_i +
3)).value = intLoop_i
end if
next
ExcelBook.Worksheets(intCurrentSheet).Cells(intTaskRow, (intLoop_i +
3)).value = "Total Hours"
ExcelBook.Worksheets(intCurrentSheet).Cells(intTaskRow, (intLoop_i +
4)).value = "Department"
'Color code the Resource-task header
SetSelection strCurrentSheetName, intTaskRow, "B", "AJ"
SetAllBorder ExcelApp.Selection, lightOrange, true, false, xlLeft, xlBottom
Response.Write("strTemp[" & strTemp & "] <BR>")
intTaskRow = intTaskRow + 1
intTaskSNo = 1
strHoliday = ""
strLeave = ""
end if
if lcase(SUB_TASK_NAME) <> "leave" and lcase(SUB_TASK_NAME) <> "holiday" then
'Fill the Resource hours
if ( SUB_TASK_NAME <> "Total Hours" ) THEN
ExcelBook.Worksheets(intCurrentSheet).Cells(intTaskRow, 2).Value = intTaskSNo
if ( SUB_TASK_NAME <> "Total Hours" ) THEN
TASK_NAME = SUB_PROJECT_NAME & "-" & SUB_TASK_NAME
else
TASK_NAME = SUB_TASK_NAME
end if
ExcelBook.Worksheets(intCurrentSheet).Cells(intTaskRow, 3).Value = TASK_NAME
for intLoop_i = 1 to NO_OF_DAYS
'intHours = cint(rsBillingRpt("day_" & intLoop_i & "_hours"))
intHours = cdbl(rsBillingRpt("day_" & intLoop_i & "_hours"))
if( intHours <> 0) THEN
'ExcelBook.Worksheets(intCurrentSheet).Cells(intTaskRow, (intLoop_i +
3)).value = cint(intHours)
ExcelBook.Worksheets(intCurrentSheet).Cells(intTaskRow, (intLoop_i
+ 3)).value = cdbl(intHours)
end if
next
'Add Total Hours for the resource
'ExcelBook.Worksheets(intCurrentSheet).Cells(intTaskRow, (intLoop_i +
3)).value = cint(total_HOURS)
ExcelBook.Worksheets(intCurrentSheet).Cells(intTaskRow, (intLoop_i +
3)).value = cdbl(total_HOURS)
ExcelBook.Worksheets(intCurrentSheet).Cells(intTaskRow, (intLoop_i +
4)).value = rsBillingRpt("Department")
else
for intLoop_i = 1 to NO_OF_DAYS
iTemp = intLoop_i + 3
intHours = cdbl(rsBillingRpt("day_" & intLoop_i & "_hours"))
if (intHours <> 0) THEN
if lcase(SUB_TASK_NAME) = "leave" then
strLeave = strLeave & cstr(iTemp) & ","
elseif lcase(SUB_TASK_NAME) = "holiday" then
strHoliday = strHoliday & cstr(iTemp) & ","
end if
end if
next
end if
SetSelection strCurrentSheetName, intTaskRow, "B", "AJ"
if lcase(SUB_TASK_NAME) <> "leave" and lcase(SUB_TASK_NAME) <> "holiday" then
'if ( SUB_TASK_NAME <> "Total Hours" ) THEN intResourceHours =
intResourceHours + cint(total_HOURS)
if ( SUB_TASK_NAME <> "Total Hours" ) THEN intResourceHours =
intResourceHours + cdbl(total_HOURS)
'if ( SUB_TASK_NAME <> "Total Hours" ) THEN intProjectHours =
intProjectHours + cint(total_HOURS)
if ( SUB_TASK_NAME <> "Total Hours" ) THEN intProjectHours =
intProjectHours + cdbl(total_HOURS)
end if
if ( SUB_TASK_NAME <> "Total Hours" ) THEN
SetAllBorder ExcelApp.Selection, xlNone, false, false, xlLeft, xlBottom
else 'TOTAL row
SetAllBorder ExcelApp.Selection, lightOrange, TRUE, false, xlLeft, xlBottom
intResourceEndRow = intTaskRow
SetHolidayColor
SetVacationColor
intTaskRow = intTaskRow + 2
ExcelBook.Worksheets(intCurrentSheet).Cells(intTaskRow, 3).value = "Total
Billable Hours =" & intResourceHours & ""
SetSelection strCurrentSheetName, intTaskRow, "B", "AJ"
SetAllBorder ExcelApp.Selection,lightGreen, true, true, xlCenter,
xlBottom
end if
if lcase(SUB_TASK_NAME) <> "leave" and lcase(SUB_TASK_NAME) <> "holiday" then
intTaskRow = intTaskRow + 1
intTaskSNo = intTaskSNo + 1
Response.Write( "<BR> NORMAL intResourceHours[" & intResourceHours & "]
ASSOCIATE_NAME[" & ASSOCIATE_NAME & "] ASSOCIATE_NAME_OLD[" &
ASSOCIATE_NAME_OLD & "]")
end if
rsBillingRpt.MoveNext
Loop
'#################################### FORMATING START
########################################
IntRecordCount = rsBillingRpt.RecordCount
SetSelection "Index", intIndexRow, "B", "E"
SetAllBorder ExcelApp.Selection, xlNone, false, false, xlLeft, xlBottom
intIndexRow = intIndexRow + 1
'Delete unwanted sheets
ExcelBook.Worksheets("Sheet2").delete
ExcelBook.Worksheets("Sheet3").delete
'Set the days column width
FOR intLoop_i = 2 to intCurrentSheet
ExcelBook.Worksheets(intLoop_i).Columns("D:AJ").EntireColumn.AutoFit
Next
ExcelBook.Worksheets("Index").Select
'#################################### FORMATING END
########################################
strServerPath = Server.MapPath(".")
strExcelFileName = "./BillingReport/" & Session.SessionID & "_" &
year(now()) & month(now()) & day(now()) & hour(now()) & minute(now()) &
second(now()) & ".XLS"
strExcelFilePath = strServerPath & strExcelFileName
ExcelBook.Worksheets("Index").Select
ExcelBook.Worksheets("Index").Range("A1:A1").Select
ExcelBook.SaveAs strExcelFilePath
'Response.Write("strExcelFilePath[" & strExcelFilePath & "]
strExcelFileName[" & strExcelFileName & "] strServerPath[" & strServerPath &
"]")
on error resume next
'Close EXCEL Application EXCEL.EXE
ExcelApp.Application.Quit
Set ExcelApp = Nothing
'Close Record-set
Set rsBillingRpt = Nothing
objConnIntratime.close()
'Close Connection
set objConnIntratime = nothing
if ( intNoOfRecords = 0 ) then
Response.Write("<BR><Font color=red size=5>No resources entered time for
" & month_id & "</font>")
Response.end
end if
'Redirect the user to EXCEL
if( Err.number = 0 ) THEN
Response.Redirect(strExcelFileName)
Response.end
else
Response.Write("ERROR: Unknown error occured Err.number[" & Err.number &
"] Err.Description[" & Err.Description & "]")
End IF
%>
<a href='.<%= strExcelFileName %>' target="EXCEL_REPORT">Billing_report</a>
</body>
</html>
<%
'###################################################################################################################
'Sub Routine Name: SetSelection
'Paramaters : 1) SheetName 2) Row# 3) startColumn# 4)End Colummn#
'Description : Sets the EXCEL selection for the given cell row and
column ranges
'
'###################################################################################################################
public Sub SetSelection ( ByVal p_SheetName, ByVal p_row, ByVal p_startCol,
ByVal p_endCol)
ExcelBook.Worksheets(p_SheetName).Select
strTemp = p_startCol & p_row & ":" & p_endCol & p_row
ExcelBook.Worksheets(p_SheetName).Range(strTemp).Select
End Sub
'###################################################################################################################
'Sub Routine Name: SetHolidayColor
'Paramaters : None
'Description : Sets the holiday color for the Saturday and Sunday
'
'###################################################################################################################
Public Sub SetHolidayColor
Dim intI
Dim intJ
Dim intK
intJ = intSaturdayStCol
intK = intSundayCol
Do while (intJ <= (NO_OF_DAYS+3))
For intI = intResourceStRow to intResourceEndRow
ExcelApp.WorkSheets(intCurrentSheet).Cells(intI,
intJ).Interior.ColorIndex = intHolidayColor
if ( (intJ + 1) <= (NO_OF_DAYS+3) ) then
ExcelApp.WorkSheets(intCurrentSheet).Cells(intI, (intJ
+1)).Interior.ColorIndex = intHolidayColor
End IF
next
intJ = intJ + 7
Loop
if intK <= (NO_OF_DAYS+3) and intK <> 0 then
For intI = intResourceStRow to intResourceEndRow
ExcelApp.WorkSheets(intCurrentSheet).Cells(intI,
intK).Interior.ColorIndex = intHolidayColor
next
end if
End Sub
Public Sub SetVacationColor
Dim intI
Dim intJ
if strHoliday <> "" then
strHoliday = Left(strHoliday,len(strHoliday)-1)
iArrHoliday = Split(strHoliday,",")
For intJ = intResourceStRow to intResourceEndRow
For intI = 0 to ubound(iArrHoliday)
ExcelApp.WorkSheets(intCurrentSheet).Cells(intJ,
cint(iArrHoliday(intI))).Interior.ColorIndex = intHolidayColor
next
next
end if
if strLeave <> "" then
strLeave = Left(strLeave,len(strLeave)-1)
iArrLeave = Split(strLeave,",")
For intJ = intResourceStRow to intResourceEndRow
For intI = 0 to ubound(iArrLeave)
ExcelApp.WorkSheets(intCurrentSheet).Cells(intJ,
cint(iArrLeave(intI))).Interior.ColorIndex = 39
next
next
end if
End Sub
'###################################################################################################################
'Sub Routine Name: SetFont
'Paramaters : 1) Font Size 2) Font Color Index 3)Font Bold (Y/N)
'Description : Sets the given font properties for the cells that are
already selected
'
'###################################################################################################################
Public Sub SetFont (ByRef p_intFontSize, ByRef p_intFontColor, ByRef
p_blnBold)
ExcelApp.Selection.Font.Bold = p_blnBold
ExcelApp.Selection.Font.Size = p_intFontSize
ExcelApp.Selection.Font.ColorIndex = p_intFontColor
End Sub
'###################################################################################################################
'Sub Routine Name: SetAllBorder
'Paramaters : 1) Selection 2) Interior Color Index 3)Font Bold (Y/N) 4)
Merge Cells(Y/N) 5)Horizantal alignment 6) Vertical Alignment
'Description : Sets the given font properties for the cells that are
already selected
'
'###################################################################################################################
Public Sub SetAllBorder(Byref selection, ByVal p_intColor, ByVal p_blnBold,
ByVal p_blnMerge, ByVal p_intHAlign, ByVal p_intVAlign)
ExcelApp.Selection.Font.Bold = p_blnBold
ExcelApp.Selection.Interior.ColorIndex = p_intColor
ExcelApp.Selection.Borders(xlDiagonalDown).LineStyle = xlNone
ExcelApp.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
ExcelApp.Selection.Borders(xlEdgeLeft).LineStyle = xlContinuous
ExcelApp.Selection.Borders(xlEdgeLeft).Weight = xlThin
ExcelApp.Selection.Borders(xlEdgeLeft).ColorIndex = xlAutomatic
ExcelApp.Selection.Borders(xlEdgeTop).LineStyle = xlContinuous
ExcelApp.Selection.Borders(xlEdgeTop).Weight = xlThin
ExcelApp.Selection.Borders(xlEdgeTop).ColorIndex = xlAutomatic
ExcelApp.Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous
ExcelApp.Selection.Borders(xlEdgeBottom).Weight = xlThin
ExcelApp.Selection.Borders(xlEdgeBottom).ColorIndex = xlAutomatic
ExcelApp.Selection.Borders(xlEdgeRight).LineStyle = xlContinuous
ExcelApp.Selection.Borders(xlEdgeRight).Weight = xlThin
ExcelApp.Selection.Borders(xlEdgeRight).ColorIndex = xlAutomatic
ExcelApp.Selection.Borders(xlInsideVertical).LineStyle = xlContinuous
ExcelApp.Selection.Borders(xlInsideVertical).Weight = xlThin
ExcelApp.Selection.Borders(xlInsideVertical).ColorIndex = xlAutomatic
ExcelApp.Selection.MergeCells = p_blnMerge
ExcelApp.Selection.HorizontalAlignment = p_intHAlign
ExcelApp.Selection.VerticalAlignment = p_intVAlign
End Sub
%>
