VB and Excel

Does anyone know how to keep the Format to text when a large number is sent to Excel. I use a 12 digit control number and when I write to Excel it automatically changes the Number to scientific Notation. I need to see all of the digits.

-Thanks

-Sir Henry

Hi

You could test first to see if the number has twelve or more digits, if it has, after you have inserted the number into the Excel cell, you could reset the format back to Number. Excel cells are by default General format (as far as I know) so they change as they think neccessary.

To change the cell to Number format with no decimal points do the following:

Range("A2").Select
Selection.NumberFormat = "0"

I hope this helps.

Gazzo.

Thanks, Gazzo. But It tell me that 'Selection' is not supported.

-Sir Henry

All you have to do, is change the format of the cell to "Number" with no Decimal Points.

then, if you still want to keep the format as "Text", you can insert a ' (the symbol on the same button as the @) before the number. but i don't know if it is advisable if you are using the number as a control number.

Hope this helps.

Adi

Keyboards must be different in the UK. My @ sign is above my 2 key. The apostrophe (') is below my double quotes (").

Just a little aside.
images/icons/wink.gif

Hi again,

this should work:

Dim xlApp As Excel.Application
Dim xlwbk As Excel.Workbook
Dim xlwst As Excel.Worksheet

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlwbk = xlApp.Workbooks.Add
Set xlwst = xlwbk.ActiveSheet
'Enter into A1 - 123456789123
'in Excel itself
xlwst.Range("A1").NumberFormat = "0"

You'll need to F8 through the code so that when the worksheet is active you can enter 123456789123 into A1 and then continue with xlwst.range.......

Gazzo

Thanks, Gazzo

I used the Cells(x,y) versus the Range, becasue I'm doing it dynamically. But It worked.


Thanks again,
--Sir Henry