none
VB Script to convert column as number RRS feed

  • Question

  • Hi Folks -

    I'm having an issue with the 1E+ format when opening a CSV file in excel.  I tried to add a formula to get rid of it, (and it does) but when I reopen in excel the scientific notation is back.

    I was hoping to convert the column as number when I open it using excel via Vb script - is that possible?

    Here is the peice of code I want to add it to:

    ElseIf STAGE = "2" Then
    
    	WScript.Echo "Opening the following file(s) for processing:" 
    	WScript.Echo " "
    	WScript.Echo FILE1
    
    	Set objWorkbook1= objExcel.Workbooks.Open( LOCALEXPORTPATH & FILE1 )
    	'objWorkbook2.CheckCompatibility = False
    	
    	'::-- Get LastCell of FILE1 --::'
    	Set objWorksheet1 = objWorkbook1.Worksheets(FILE1TAB)
    	xlUp = -4162
    	Set LastCell1 = objWorksheet1.Range("A" & objWorksheet1.Rows.Count).End(xlUp)
    	
    	'::-- Add 1 Column to FILE1TAB --::'
    	Set objRange = objWorkbook1.Worksheets(FILE1TAB).Range("B:B").EntireColumn
    	objRange.Insert(xlShiftToRight)
    
    	objWorkbook1.Worksheets(FILE1TAB).Range("B2").Value = "=TEXT(A2,""0"")"
    	
    	Set SourceRange = objWorksheet1.Range("B2")
    	Set FillRange = objWorksheet1.Range("B2:B" & LastCell1.Row)
    	SourceRange.AutoFill FillRange
    	
    	objWorkbook1.Worksheets(FILE1TAB).Range("B2:B" & LastCell1.Row).Copy
    	objWorkbook1.Worksheets(FILE1TAB).Range("A2:A" & LastCell1.Row).PasteSpecial -4163
    	
    	objWorkbook1.Worksheets(FILE1TAB).Range("B:B").Delete
    	
    	objWorkbook1.Save
    	objWorkbook1.Close

    As you can, I can get rid of quite of a bit of code if I just convert the column. However, the above method isn't working anyway.

    Thanks!

    • Moved by Bill_Stewart Tuesday, April 10, 2018 3:52 PM Move to more appropriate forum
    Tuesday, April 10, 2018 12:52 AM

All replies

  • This is an Excel issue and cannot be solved with PowerShell.

    When a number exceeds the displayable range it is shown in what is known as "scientific notation" which is an exponentiated display telling use the 9 to 11 significant digits to the power of ten.

    Your issue belongs in the Excel forum.


    \_(ツ)_/

    Tuesday, April 10, 2018 1:30 AM
  • Hi cdtakacs1,

    You had mentioned that,"I was hoping to convert the column as number when I open it using excel via Vb script - is that possible?"

    I assume that you are storing a long numeric value in column which get converted to 1E+.

    Are you using this value in any calculations or you are just storing it in column.

    If you are just storing it and not performing any further calculations then try to store number as text.

    Then it will stay as it is and not get converted in 1E+.

    If you are performing any further calculations then try to store it as text and at the time of calculation first convert it to number and do your calculation.

    As present, We are unaware about your numerical value and we also don't know how you are going to use it.

    So if you have trouble to store it as text or you face any trouble in further processing then try to provide a sample data and detailed information about your calculations.

    We will try to make a test on our side and try to reproduce the issue and try to provide you suggestions to fix it.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 11, 2018 1:35 AM
    Moderator
  • Some notes:

    Above 90Gb the Excel display will convert to E1 notation.  If the cell width is too small the number will be displayed as E1.  You can format the cell to use 1000s separators and the cell will always display the number as text.  If the cell is too small the display wil be #####.  Expanding the cell will cause the number to display fully with commas.  Setting the cell/column format as text will display all digits.

    These are ll Excel issues which need to be understood when choosing to have a display as you want.  Deepak's post is a good step towards getting the results you want.


    \_(ツ)_/

    Wednesday, April 11, 2018 2:33 AM
  • Hi cdtakacs1,

    Is your issue is solved now?

    I find that you did not done any follow up after creating this thread.

    If your issue is fixed by you then I suggest you to post your solution and mark it as an answer.

    If your issue is still exist then try to refer the suggestions given by the community members.

    If you think that the suggestions given by the community member can solve your issue then mark the helpful suggestion as an answer.

    It will help us to close this thread and it also can be helpful to other community members who will meet with same kind of issues in future.

    If you have any further questions then you can let us know about it, We will try to provide you further suggestions to solve it.

    I suggest you to update the status of this thread and take appropriate actions to close it.

    Thanks for your understanding.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, April 26, 2018 8:41 AM
    Moderator