Can you force Excel to covert ALPHA to a NUMBER?

Mister H

Well-known Member
Joined
Mar 6, 2002
Messages
1,507
Hi All:

I was wondering if there is a VBA Code that will convert a Cheque Number something like:

C3232456 or CS-323232 or SA2323-1A

to a number? Why I ask is that when I try to upload my sheet to Access any cheques with an ALPHA in it are not being uploaded. This is because the table it is uploading to is formatted as number. I tried changing the formatting in Access to Text but it keeps reverting back to Number. I did not create the Access Database so I am not sure exactly how to change it and prevent it from reverting back. Also , there is probably a reason that it is formatted that way.

So what I am looking for is something that would:

Select "Cheque Logging Sheet"
Make anything in C2 to C251 a NUMBER regardless of what is entered.

Any ideas?

THANKS,
Mark :)
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Mark

How exactly are you uploading the data?

If you are importing it in as a new table then Access will try and 'guess' the field types based on the first few values in the column.

If your not I see no reason that you can't change the field to Text permanently.

By the way what would you want these values converted to anyway?
 
Upvote 0
Hi Norie:

THANKS for your reply...

I am importing this via some button and code that someone set up in Access itself. I have tried doing an stepping through the code but i can not see where formatting is being set. I did come across this though and am not certain what the Array is referring to:

Code:
strTableName = "Cheque Log Report"
    
 '   Set appExcel = GetObject(, "Excel.Application") 'look for a running copy of Excel
  '  If Err.Number <> 0 Then 'If Excel is not running then
        Set appExcel = CreateObject("Excel.application") 'Creates an object
  '  End If
    
    fieldinfoGeneral = Array(1, 1)
    fieldinfoText = Array(1, 2)
    fieldinfoDate = Array(1, 4)

I want the Values to remain the same but I want them to be a number instead of text. Is it possible to have something like C323232 in Excel as a Number or does it have to be text due to the fact that it has an ALPHA in it?

Hope this make sense to ya... THANKS Again Norie.

Take Care,
Mark :confused:
 
Upvote 0
Mark

I'm a little confused.:confused:

How can something like C323232 be a number ?:eek:

I'm not quite sure what the array part is but it looks as though it might be something to do with text to columns.

Hard to tell though without seeing morel code, specifically the part that does the actual importing.:)
 
Upvote 0
Hi Norie:

SORRY for the confusion... :biggrin:

How can something like C323232 be a number ?

That pretty much answers my question... I know it is not technically a number but I was hoping that VBA could tell Excel to throw out the logic and make this represent a number.

I was hoping this was possible as I know when you go to sort sometimes Excel asks if you want to consider everything a number or sort it separate (not sure of the exact phrase).

I am Posting a couple of codes below in regards to the import but it jumps around so much I am not sure that what I am posting will help:

This is the main code that Gets (Selects) the file:

Code:
Private Sub cmdGetIt_Click()
    
    Dim strTableName As String
    Dim strTableTest As String
    Dim i As Integer
    Dim ysn As Boolean
    Dim strSQL As String
    Dim appExcel As Object
    Dim appSheet As Object
    Dim rst As Recordset
    Dim fieldinfoGeneral
    Dim fieldinfoText
    Dim fieldinfoDate
    DoCmd.Hourglass (True)

    strTableName = "Cheque Log Report"
    
 '   Set appExcel = GetObject(, "Excel.Application") 'look for a running copy of Excel
  '  If Err.Number <> 0 Then 'If Excel is not running then
        Set appExcel = CreateObject("Excel.application") 'Creates an object
  '  End If
    
    fieldinfoGeneral = Array(1, 1)
    fieldinfoText = Array(1, 2)
    fieldinfoDate = Array(1, 4)
'**************what to add
  Dim xlLastCell As Integer
    Dim xlUP As Integer
    Dim xlAll As Integer
    Dim xlMultiply As Integer
    Dim xlToLeft As Integer
    Dim xltoright As Integer
    Dim currentrow As Integer
    Dim rowID As Integer
    Dim lastrow As Integer
    Dim currentcol As Integer
    Dim colID As Integer
    Dim lastcolID As Integer
    Dim lastcol As String
    Dim startrow As Integer
    Dim startcol As String
    
    xlLastCell = 11
    ' make it a public const xlLastCell as integer = 11
    xlUP = -4162
    xlToLeft = -4159
    xltoright = -4161
    xlAll = -4104
    xlMultiply = 4
    
        appExcel.workbooks.Open Me.txtProgramProductionFromAreasExcelFile
    
    IfTableExistsThenDelete (strTableName)
'Suck the Sheet in
    DoCmd.TransferSpreadsheet acImport, , strTableName, Me.txtProgramProductionFromAreasExcelFile, True
    Call UpdateHistory(Me.txtProgramProductionFromAreasExcelFile, "tblFileHistProgramProductionFromAreas")
    Me.txtProgramProductionFromAreasExcelFile.Requery
' Remove the zero amount rows
    strSQL = "DELETE [Cheque Log Report].Amount, [Cheque Log Report].Type "
    strSQL = strSQL & "FROM [Cheque Log Report] "
    strSQL = strSQL & "WHERE ((([Cheque Log Report].Amount)=0 Or ([Cheque Log Report].Amount) Is Null)) OR ((([Cheque Log Report].Type)='0' Or ([Cheque Log Report].Type) Is Null)); "
    CodeDb().Execute strSQL
    
    appExcel.Save
    appExcel.Quit
    MsgBox ("Done")
    DoCmd.Hourglass (False)
End Sub

This is the main code that performs the Import:

Code:
Private Sub Command193_Click()
On Error GoTo Err_Command193_Click
    Dim stDocName As String
    Dim strSQL As String
    Dim rst As Recordset
    Dim rstqapp As Recordset
    Dim ysn As Boolean
    Dim strTableName As String
' doesn't work
    'strSQL = "INSERT INTO tblUnscheduled ( intUserID, intPayStatusID, intPeriodRankID, intAmountBilled, intMinistryID, intBillItemTypeID, Ministry, Client, Program, Description, Contact, PhoneNumber, BillingPeriod, BillDate, MINRC, Comments, intActivityPeriodRankID, intBillItemTypeCategoryID ) "
'    strSQL = strSQL & " SELECT " & [Forms]![frmLogin]![intUserID] & " AS Expr3, 1 AS Expr2, " & Me.ComboPeriod & " AS Expr1, tblProgramProductionFromAreas.Amount, tblProgramProductionFromAreas.intMinistryID, tblProgramProductionFromAreas.intBillItemTypeID, tblProgramProductionFromAreas.Ministry, "
 '   strSQL = strSQL & " tblProgramProductionFromAreas.Client, tblProgramProductionFromAreas.Program, tblProgramProductionFromAreas.Description, tblProgramProductionFromAreas.Contact, tblProgramProductionFromAreas.PhoneNumber, tblProgramProductionFromAreas.BillingPeriod, tblProgramProductionFromAreas.BillDate, "
  '  strSQL = strSQL & " tblProgramProductionFromAreas.MINRC, tblProgramProductionFromAreas.Comments, FindActivityPeriodforDate([Billdate]) AS Expr4, " & FindCategoryfromMinistryProfile([intMinistryID], [intBillItemTypeID]) & " AS Expr5 "
   ' strSQL = strSQL & " FROM tblProgramProductionFromAreas "
 '   strSQL = strSQL & " WHERE (((tblUnscheduled.Program) Is Null) AND ((tblUnscheduled.Description) = null) AND ((tblUnscheduled.BillingPeriod) = null) AND ((tblUnscheduled.BillDate) = null));"
  '  MsgBox (strSQL)
   ' CodeDb.Execute (strSQL)
    strTableName = "tblProgramProductionFromAreas"
    ysn = False
    strSQL = " SELECT tblFileHistProgramProductionFromAreas.MC, tblFileHistProgramProductionFromAreas.Updated, tblFileHistProgramProductionFromAreas.FileHistLocation "
    strSQL = strSQL & " FROM tblFileHistProgramProductionFromAreas "
    strSQL = strSQL & " WHERE (((tblFileHistProgramProductionFromAreas.MC)=True)); "
    Set rst = CodeDb.OpenRecordset(strSQL)
    rst.MoveLast
    rst.MoveFirst
    ysn = MsgBox("Are you sure you really want to do this?", vbYesNo) = vbYes
    If rst![Updated] And ysn Then
        ysn = MsgBox("Our records indicate that this file has already been sucked in.  Are you really really sure you want to do this?", vbYesNo) = vbYes
    End If
        
    If ysn Then
        
        DoCmd.OpenQuery ("qappChequeLog")
            
        rst.Edit
        rst![Updated] = True
        rst.Update
        MsgBox ("Done")
    Else
        MsgBox ("I didn't do anything")
    End If
    rst.Close
Exit_Command193_Click:
    Exit Sub
Err_Command193_Click:
    MsgBox Err.Description
    Resume Exit_Command193_Click
    
End Sub

Like I said I am not sure that the code above will give you any further info but I am hopeful...

THANKS Norie,
Mark :confused:
 
Upvote 0
Mark

It's not the 2nd code that does the import as far as I can see anyway.

The code does give me further information - it's not the best code I've seen recently.

One main thing I'm wondering is why an instance of Excel created and then a workbook is opened in it.

Personally if I inherit code I generally try and find out it's purpose.

Then, if needed, rewrite it.:)
 
Upvote 0
Hi Norie:

I would right the code if I could but I can even understand it when someone else wrote it :biggrin: :LOL:

Anyway you have helped me with this statement:

If you are importing it in as a new table then Access will try and 'guess' the field types based on the first few values in the column.

I have just went into my sheet and re-sorted it as Descending and now my ALPHA cheques are at the top of the list and they seem to have imported correctly. :biggrin: THANKS. :biggrin:

Out of curiosity is there a way to have Excel Sort my data and put the cheques that contain ALPHA at the top and then sort the rest as Ascending? No big deal but if it is possible it will make it easier when searching through the list for a specific cheque number.

THANKS for your help and explanations...
Take Care,
Mark
 
Upvote 0
Mark

Well obviously you can do it manually like you have already.

If you want code to do it automatically I suggest you do that again but with the macro recorder turned on.

That should generate code that can be adapted.

If you need help doing that post back, perhaps in a new thread.:)
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top