Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting Cell Values Through Automation
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").Valu e) 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("MajorC hangeRpt").Rows.count, 1).entirerow.delete() .Worksheets("Total Detail").range("TotalDetail").Cells(.Range("TotalD etail").Rows.count, 1).entirerow.delete() .Worksheets("Total Rpt").range("TotalRpt").Cells(.Range("TotalRpt").R ows.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 ================================================== == |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
converting hourly values into daily values | Excel Worksheet Functions | |||
converting hour values to day mean values | Excel Worksheet Functions | |||
Values don't behave when converting text to values in userform | Excel Programming | |||
Pulling values out of cells using automation | Excel Programming | |||
Feeding Date Values From C++ Using Automation | Excel Programming |