ADO - SQL Server to Excel drops currency format. 1

[DirectDrive]

I wrote a function that passes data from a SQL server to an Excel sheet. The function makes a copy of an Excel template file and populates the copy with the data set that was passed to it. The data makes it into the Excel sheet, but the currency data is showing up in Excel as text (it has a leading '). The Excel template already has the columns formatted to currency, but this doesn't seem to stay. I have even tried t use CCur(), but had no luck.
Any help would be appreciated.


Public Sub CreateXLS(NewFileName As String, rsS As ADODB.Recordset)
Dim i as Integer, iFieldCount as Integer, strSQL as String
Dim oconnX As ADODB.Connection 'Excel File connection
Dim rsX As ADODB.Recordset 'Excel file recordset
Set oconnX = New ADODB.Connection
Set rsX = New ADODB.Recordset

On Error GoTo err_Trap

rsS.MoveFirst 'if recordset is empty this wil generate an error

strSQL = "SELECT * FROM [Sheet1$]"
strPath = "\\webcluster1a\remote\downloads\"




Call FileCopy(strPath & "FileTemplates\StMasterCash.xls", strPath & NewFileName & ".xls&quot

oconnX.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPath & NewFileName & ".xls;" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"";"


rsX.Open strSQL, oconnX, adOpenDynamic, adLockOptimistic

iFieldCount = rsS.Fields.Count
i = 1 'set to 1 so we skip the first field of the rsS recordset

Do While Not rsS.EOF
rsX.AddNew
rsX.Update

Do While i < iFieldCount

If IsNull(rsS(i)) Then
rsX(i - 1) = &quot;NULL&quot;
Else
If i > 6 Then
rsX(i - 1) = CCur(Trim(rsS(i)))
Else
rsX(i - 1) = Trim(rsS(i))
End If

rsX.Update
i = i + 1
Loop
rsX.Update
rsS.MoveNext
i = 1
Loop

rsX.Close
Set rsX = Nothing
oconnX.Close
Set oconnX = Nothing

exit_Sb:
Exit Sub


err_Trap:
If Err.Number = 70 Then
'code here for file already open
ElseIf Err.Number = 3021 Then
'code here for empty recordset
Else

End If

Resume exit_Sb


End Sub

 

[bjd4jc]

You will most likely have to use the excel object library and open the excel file up AFTER you have written it to the file and change the format then. What I have done is dump the whole thing into an excel file and then format the whole thing afterward and not worry about what it looks like while actually creating it. If you need help with this I'm sure you can search this forum or post again

 

[MichaelRed]

while bjd4jc's advice is probably a reasonable soloution, the actual culprit here it the leading apostrophy. Excel doesn't care about the value and / format of the cell content when the entered value doesn't match the format, and it treats the apostrophy as a text indicator. So the SPECIFIC issue is that your SQL export is including the spare character, not that Excel is not honoring the format of the cells.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[bjd4jc]

However, when you write a cell like this through code, excel automatically puts the apostrophe there. I don't know why, but it my coding, I don't add any apostrophes to anything but they are still there automatically. I've never figured out how to get rid of them automaticallly being there...

 

[MichaelRed]

hmmmmmmmmmmmmmmmmmm,

I guess I am going to be from MO (show Me state), for this bit. I just don't quite believe it. I admit I haven't tried YOUR code, but then I ALSO don't have access to the template or the file generated by SQL server.

What I DO have is the generic experience in manipulating Excel spread sheets with VB and have been quite able to extract, edit and input data in 'correct' format from a variety of sources and formats. Some of the processes are a bit less than obvious - but that does not generally make them 'transmorgify' the format or type of information being manipulated.

I would also wander into the realm of pure speculation on WHY you would want to do this bit on a cell by cell basis? Generally speaking, MS. products are quite reasonable in manipulating recordset information between application formats, so I would expect that SQL Server would be quite capable of generating your 'data' in a completly compatible format and then your code should be able to just import the whole thing?

Obviously you have gone quite beyond MY simple minded thoughts and capabilities. I concentrqte on the KISS principle and studiously avoid compounding problems ...




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[DirectDrive]

bjd4jc, i did have to use the excel object to scrub the data. I did something like the code below.

Do While i <= RecordCt + 1
x = Range(&quot;H&quot; & i)
Range(&quot;H&quot; & i) = x
Range(&quot;H&quot; & i).Style = &quot;Currency&quot;

i = i + 1
Loop
apparantly when passing the value to a varaible Excel does not pass the apostrophe and when passing the variable back to the cell Excel does not add another apostrophe. It is unfortunate that i can not do this with ADO since the Excel object is slow.

MichaelRed,
it is Excel that is doint this. Excel uses a leading apostrophe to force a cell to be text. i know you can not see the data i am passing to the Excel sheet, but you can try this code:
x = &quot;'5&quot;

Debug.Print CCur(x)

CCur will blow up (type mismatch) when an apostrope is passed to it. Since I am running the data through CCur, to try to force the data to Excel as currency, we know that the SQL Server data does not contain a leading apostrope.


thank you both for your input.