Converting Cell Values Through Automation

[Orlanzo]

Hello,

I've run into a very peculiar issue when converting cell values to resolve
the "Number Stored as Text" issue. The first code segment is called by a
button on a worksheet from a code module within the workbook. The second
segment is executed within a SQL Server Integration Services (SSIS) Script
Task. The code executed within Excel works as expected. The SSIS executes,
but doesn't resolve the issue. The error persists.

Is there some difference between the code segments being overlooked? I'm
interested in your thoughts and opinions on this.

(FYI - I am aware server side automation is not recommended for several
reasons. Nonetheless, the company has chosen to utilize it. I can only
advise them of the consequences....)

====================================================
Code within Excel Module
====================================================
Public Sub test()
Dim rng As Range
Dim cell As Range

'' Update titles for each report within the workbook to reflect
'' the reporting date.
Worksheets("Total Rpt").Range("A1").Value _
= "Total Position & Risk Change for " & Now()

Worksheets("Major Change Rpt").Range("A1").Value _
= "Daily Major Position Change for " & Now()

Worksheets("Total Detail").Range("A1").Value _
= "Total Position & Risk Change - Detail for " & Now()

Worksheets("Major Change Detail").Range("A1").Value _
= "Daily Major Position Change - Detail for " & Now()

'' Some columns may not be formatted correctly after the import resulting
'' in a number stored as text error. Scan relevant columns within the
sheet
'' and apply the appropriate conversion to cleanse the sheet of this
error.
With Worksheets("Total Rpt").Range("TotalRpt")
Set rng = Range(.Cells(2, 3), .Cells(2, 8).End(xlDown))
For Each cell In rng.Cells
cell.Value = CDbl(cell.Value)
Next cell
End With

With Worksheets("Total Detail").Range("TotalDetail")
Worksheets("Total Detail").Activate
Set rng = Range(.Cells(2, 5), .Cells(2, 13).End(xlDown))
For Each cell In rng.Cells
cell.Value = CDbl(cell.Value)
Next cell
End With

With Worksheets("Major Change Rpt").Range("MajorChangeRpt")
Worksheets("Major Change Rpt").Activate
Set rng = Range(.Cells(2, 5), .Cells(2, 7).End(xlDown))
For Each cell In rng.Cells
cell.Value = CDbl(cell.Value)
Next cell
End With

With Worksheets("Major Change Detail").Range("MajorChangeDetail")
Worksheets("Major Change Detail").Activate
Set rng = Range(.Cells(2, 7), .Cells(2, 9).End(xlDown))
For Each cell In rng.Cells
cell.Value = CDbl(cell.Value)
Next cell

Set rng = Range(.Cells(2, 3), .Cells(2, 3).End(xlDown))
For Each cell In rng.Cells
cell.Value = CStr(cell.Value)
Next cell

Set rng = Range(.Cells(2, 11), .Cells(2, 11).End(xlDown))
For Each cell In rng.Cells
cell.Value = CStr(cell.Value)
Next cell
End With

End Sub

====================================================

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++==

==================================================
Code within the SSIS Script Task
====================================================
Public Sub Main()
Dim ExcelSheet As Object
Dim workbook As Object
Dim sheet As Object
Dim rng As Object
Dim cell As Object
Dim x As Byte()

Dim templateDestinationPath As String
Dim templateFileName As String
Dim destinationPath As String
Dim dataDate As Date

templateDestinationPath =
CStr(Dts.Variables("templateDestinationPath").Value)
templateFileName = CStr(Dts.Variables("templateFileName").Value)
destinationPath = CStr(Dts.Variables("destinationPath").Value)
dataDate = CDate(Dts.Variables("dataDate").Value)

Try
ExcelSheet = CreateObject("Excel.Application")
'workbook = ExcelSheet.Workbooks.Open(templateDestinationPath &
templateFileName)
workbook = ExcelSheet.Workbooks.Open(templateDestinationPath)

With ExcelSheet
'' Delete the formatting rows from the named ranges
.Worksheets("Major Change
Detail").range("MajorChangeDetail").Cells(.Range("MajorChangeDetail").Rows.count, 1).entirerow.delete()
.Worksheets("Major Change
Rpt").range("MajorChangeRpt").Cells(.Range("MajorChangeRpt").Rows.count,
1).entirerow.delete()
.Worksheets("Total
Detail").range("TotalDetail").Cells(.Range("TotalDetail").Rows.count,
1).entirerow.delete()
.Worksheets("Total
Rpt").range("TotalRpt").Cells(.Range("TotalRpt").Rows.count,
1).entirerow.delete()

'' Apply formatting to each section of the sheet.
'' Update titles for each report within the workbook to
reflect
'' the reporting date.

.Worksheets("Total Rpt").Range("G1").Value() _
= "Reporting Date: " + dataDate

.Worksheets("Major Change Rpt").Range("G1").Value() _
= "Reporting Date: " + dataDate

.Worksheets("Total Detail").Range("G1").Value() _
= "Reporting Date: " + dataDate

.Worksheets("Major Change Detail").Range("G1").Value() _
= "Reporting Date: " + dataDate


'' Some columns may not be formatted correctly after the
import resulting
'' in a number stored as text error. Scan relevant columns
within the sheet
'' and apply the appropriate conversion to cleanse the sheet
of this error.
With .Worksheets("Total Rpt").Range("TotalRpt")
rng = ExcelSheet.Range(.Cells(2, 3), .Cells(2,
8).End(ExcelSheet.xlDown))
For Each cell In rng.Cells
cell.Value = CObj(cell.Value)
Next cell
End With

With .Worksheets("Total Detail").Range("TotalDetail")
rng = ExcelSheet.Range(.Cells(2, 5), .Cells(2,
13).End(ExcelSheet.xlDown))
For Each cell In rng.Cells
cell.Value = CDbl(cell.Value)
Next cell
End With

With .Worksheets("Major Change Rpt").Range("MajorChangeRpt")
rng = ExcelSheet.Range(.Cells(2, 5), .Cells(2,
7).End(ExcelSheet.xlDown))
For Each cell In rng.Cells
cell.Value = CDbl(cell.Value)
Next cell
End With

With .Worksheets("Major Change
Detail").Range("MajorChangeDetail")
rng = ExcelSheet.Range(.Cells(2, 7), .Cells(2,
9).End(ExcelSheet.xlDown))
For Each cell In rng.Cells
cell.Value = CDbl(cell.Value)
Next cell

rng = ExcelSheet.Range(.Cells(2, 3), .Cells(2,
3).End(ExcelSheet.xlDown))
For Each cell In rng.Cells
cell.Value = CStr(cell.Value)
Next cell

rng = ExcelSheet.Range(.Cells(2, 11), .Cells(2,
11).End(ExcelSheet.xlDown))
For Each cell In rng.Cells
cell.Value = CStr(cell.Value)
Next cell
End With

'' Save the changes to the workbook
.Workbooks(1).close(True)
End With
Catch ex As Exception
Dts.Log("Error removal of dummy format rows from Excel sheet: "
& ex.Message, 0, x)
Dts.TaskResult = Dts.Results.Failure
Finally
'' ExcelSheet.Workbooks(1).Close()
ExcelSheet.Application.Quit()
ExcelSheet = Nothing
End Try

'--------------------------------------
Dts.TaskResult = Dts.Results.Success
End Sub
End Class

====================================================