Function's output of AlphaNumeric to Excel as numeric only!

[petrosky]

Hi,

I have a function (borrowed from around these parts) that is as follows...

Function PopulateExcel() As String


Dim db As Database
Dim qdf As QueryDef
Dim prm As Parameter
Dim rs As Recordset
Dim objXL As Object, objSheet As Object, objRange As Object
Dim strSaveAs As String, strRecord As String
Dim x As Integer, intRow As Integer

DoCmd.Hourglass False
Set db = CurrentDb()

' Open, and make visible the Excel Template (Request.xlt)
Set objXL = GetObject(XLT_LOCATION)

objXL.Application.Visible = True
objXL.Parent.Windows(1).Visible = True

' Open the recordset, and activate the sheet in the template
Set qdf = db.QueryDefs("TransfertoExcel")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rs = qdf.OpenRecordset(dbOpenSnapshot)
Set objSheet = objXL.Worksheets("Invoice&quot
objSheet.Activate
rs.MoveFirst

' Insert the data from the recordset into the worksheet

objXL.ActiveSheet.Cells(6, 5).Value = rs![InvoiceNumber]
objXL.ActiveSheet.Cells(11, 5).Value = rs![PolicyNumber(IMA)]
objXL.ActiveSheet.Cells(10, 5).Value = rs![PONumber(IMA)]
objXL.ActiveSheet.Cells(12, 5).Value = rs![TheirClaimNumber]
objXL.ActiveSheet.Cells(14, 5).Value = rs![PolicyExcess]
objXL.ActiveSheet.Cells(9, 9).Value = rs![ClaimFirstName]
objXL.ActiveSheet.Cells(10, 9).Value = rs![ClaimAddressline1]
objXL.ActiveSheet.Cells(11, 9).Value = rs![ClaimAddressline2]
objXL.ActiveSheet.Cells(12, 9).Value = rs![ClaimPhone]
objXL.ActiveSheet.Cells(9, 10).Value = rs![ClaimLastName]
objXL.ActiveSheet.Cells(22, 7).Value = rs![SumOfPrice]



rs.Close



End Function

The function is great, but in the Recordset you will notice [PONumber(IMA)] which is an alpha numeric field but when it exports to excel it drops the letters.

These letters are critical because the file is outputed as a CSV for automated billing to a customer of ours. I have tried various input masks in Excel and Access and when I preview the queries (TransferToExcel) output the letters are there???

Any help is appreciated.
Please advise if any clarification is needed.

Regards,

Peter. Remember- It's nice to be important,
but it's important to be nice

 

[ETID]

Have you tried this..

objXL.ActiveSheet.Cells(10, 5).Value = val(rs![PONumber(IMA)])

or maybe...

objXL.ActiveSheet.Cells(10, 5).Value = value(rs![PONumber(IMA)])

 

[petrosky]

Hi ETID,

Thank you for considering my problem.

I have tried your ideas but had no joy with either.

value caused a compile error sub or function not defined.
val dropped the same data into excel.

The strange thing is when I look at the Excel cells in question eg. (10, 5) the cell value is correct in that the letters display but when I click on the cell and view it's value in the formula bar, only the numeric value shows.

I have tried various data types..text, general etc to no joy.

I am using input masks on the form to insure data is entered in the correct manner.

eg. "PN"000000"" this still appears to save the alpha values in my table but maybe this is where it is all going bad???

Any other ideas would be great.

Peter


Remember- It's nice to be important,
but it's important to be nice