I am using VB 6 to create Excel files using CreateObject. I pass the ADO recordset and have the code loop through the fields and records writing the info to the spreadsheet. I want to explicitly format columns. Anyone know where I am going wrong with the following code?
Select Case ExportRS.Fields(N).Type
Case adBoolean, adSmallInt
'Boolean:
xlSheet.Range(Cells(2, N + 1), Cells(65501, N + 1)).NumberFormat = "Boolean"
Case adBigInt, adBinary, adInteger, adLongVarBinary, adTinyInt, adUnsignedBigInt, adUnsignedInt, adUnsignedSmallInt, adUnsignedTinyInt, adVarBinary
'Integer:
xlSheet.Range(Cells(2, N + 1), Cells(65501, N + 1)).NumberFormat = "#,##0"
Case adCurrency, adDecimal, adDouble, adNumeric, adSingle
'Number: format x,xxx.xx
xlSheet.Range(Cells(2, N + 1), Cells(65501, N + 1)).NumberFormat = "#,##0.00"
Case adDate, adDBDate, adDBTime, adDBTimeStamp
'Date: format (mm/dd/yyyy)
xlSheet.Range(Cells(2, N + 1), Cells(65501, N + 1)).NumberFormat = "mm/dd/yyyy"
Case Else
xlSheet.Range(Cells(2, N + 1), Cells(65501, N + 1)).NumberFormat = "Text"
End Select
Biggest problem is the last one. Setting NumberFormat as "Text" makes Excel try to create a custom format rather than use the "display everything as text" format that you can manually choose while in Excel.
TIA
Candice
Select Case ExportRS.Fields(N).Type
Case adBoolean, adSmallInt
'Boolean:
xlSheet.Range(Cells(2, N + 1), Cells(65501, N + 1)).NumberFormat = "Boolean"
Case adBigInt, adBinary, adInteger, adLongVarBinary, adTinyInt, adUnsignedBigInt, adUnsignedInt, adUnsignedSmallInt, adUnsignedTinyInt, adVarBinary
'Integer:
xlSheet.Range(Cells(2, N + 1), Cells(65501, N + 1)).NumberFormat = "#,##0"
Case adCurrency, adDecimal, adDouble, adNumeric, adSingle
'Number: format x,xxx.xx
xlSheet.Range(Cells(2, N + 1), Cells(65501, N + 1)).NumberFormat = "#,##0.00"
Case adDate, adDBDate, adDBTime, adDBTimeStamp
'Date: format (mm/dd/yyyy)
xlSheet.Range(Cells(2, N + 1), Cells(65501, N + 1)).NumberFormat = "mm/dd/yyyy"
Case Else
xlSheet.Range(Cells(2, N + 1), Cells(65501, N + 1)).NumberFormat = "Text"
End Select
Biggest problem is the last one. Setting NumberFormat as "Text" makes Excel try to create a custom format rather than use the "display everything as text" format that you can manually choose while in Excel.
TIA
Candice