Import Linked SQL Server Data into Excel - convert text to numeric (1 Viewer)

Rx_

Nothing In Moderation
Local time
Today, 00:09
Joined
Oct 22, 2009
Messages
2,803
Had a case where the numeric data from SQL Server ended up in Excel as text - (in the Excel cell - a ' was in front of the number ). In Excel, type in '123 in a cell. Text won't work with numeric formulas.

e.g. with code fragments
Code:
470         Set rsReclMonDt = CurrentDb.OpenRecordset(strSQLWF, dbOpenSnapshot, dbReadOnly)
560           Set ObjXL = New Excel.Application
570           ObjXL.EnableEvents = False
620           ObjXL.Workbooks.Add
660         intRowPos = 6   ' Sets starting Row for data xfer in Excel - reference fields to row6 to allow for Title / Formulas on row 1..4
680         ObjXL.DisplayAlerts = False   ' Turn off Display Alerts
690         ObjXL.Worksheets(intWorksheetNum).Cells(intRowPos, 1).CopyFromRecordset rsReclMonDt
1330     ObjXL.Rows((intRowPos - 1) & ":" & (intRowPos - 1)).Select         ' Selection for Bold header column or other formatting
1340     ObjXL.Cells.EntireColumn.AutoFit
1380    ObjXL.Selection.AutoFilter
1390    ObjXL.Rows((intRowPos) & ":" & (intRowPos)).Select   ' first Data row to freeze payne
1400    ObjXL.ActiveWindow.FreezePanes = True
' So import the data on Row 5 with header - set filters and freeze payne

The problem was that some columns with numbers came across in Excel as text. This is where the single quote (') is put in front of a number. This can be really difficult to convert using VBA automation. Had this problem last night on Excel 2010 on the 33rd and 34th column. The exact same query to SQL didn't have any problems on columns 1 to 32. Maybe the source SQL was a different data type for these last two columns? No idea, but this code was a real gem.
Used Excel VBA code to set the number format to numeric. It won't convert text since the (') is in place.

This will take a column range and convert a text to a number in Excel.
The Excel report can vary from 50 to 50,000 rows.
This code will convert a Text numeric cell eg. '123
into a numeric value in the same cell eg. 123
Note: This code is rum form MS Access 2010 where Excel.Application was set to the variable ObjXL:
Code:
Dim rUsedRange As Range 
ObjXL.Columns("AE:AF").Select ' Entire columns AE and AF
' Any other selection will also work.
 
For Each rUsedRange In ObjXL.Intersect(ObjXL.ActiveSheet.UsedRange, ObjXL.Selection).Areas
rUsedRange.value = rUsedRange.value
Next rUsedRange

Recommended testing for all Excel Automation code: if the ObjXL.Intersect is run as just Intersect
it will create a Orphan Excel instance. This can be viewed in the Task Manager as a 2n Excel instance running in memory after the primary Excel instance closes.
Often, the code will run fine the 1st time. Then encournter an error the 2nd time the same code is run. If it doesn't error, this can result in many orphaned Excel instancs running amuck with out supervision. When testing any Excel automation, it is wise to use Task Manager to verify the Excel is being created and destroyed as intended.
 

Users who are viewing this thread

Top Bottom