473,320 Members | 1,848 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

VB Excel Cell Format

1
I have written a VB code to export the List View contents to XLS file. But the problem is whenever i try to write a value like "0001", its removing all the trailing zeros and showing only "1" in the cell. I think its becuase of the default cell format (General) in Excel. Is there any way I can change the Cell Format to "Text" through VB code?

thanks,
natesh.
Jun 19 '06 #1
8 64918
CK Ong
2
I have written a VB code to export the List View contents to XLS file. But the problem is whenever i try to write a value like "0001", its removing all the trailing zeros and showing only "1" in the cell. I think its becuase of the default cell format (General) in Excel. Is there any way I can change the Cell Format to "Text" through VB code?

thanks,
natesh.
I know one easier way, which is to add a ' to the value, in which it becomes '0001. In Excel, it will then retain all the 0s.

Regards,
CK
Jun 20 '06 #2
khan
5
I have written a VB code to export the List View contents to XLS file. But the problem is whenever i try to write a value like "0001", its removing all the trailing zeros and showing only "1" in the cell. I think its becuase of the default cell format (General) in Excel. Is there any way I can change the Cell Format to "Text" through VB code?

thanks,
natesh.
Reply:

Select the Cells , Right Click and Click on "Format Cell" option and set the Type as String
Jun 20 '06 #3
ITM
1
'This example will change the format of the range to Text

Sub test()

Range("A1:A100").NumberFormat = "@"

End Sub
Jul 21 '06 #4
EBG
2
If you want all the data to have 4 digits then an alternative would be

Sub test()

Range("A1:A100").NumberFormat = "0000"

End Sub
Jul 25 '06 #5
Hi friends,
can any body help me to convert format of the cells of excel from date to general and how to pass value to min value and max value of a chart through VB.
Thanks
Payel
Apr 2 '07 #6
June7
1
Hi friends,
can any body help me to convert format of the cells of excel from date to general and how to pass value to min value and max value of a chart through VB.
Thanks
Payel
Hope you found your answers by now. But in case you are still looking, here is code I used.

Cell formatting: Worksheets("Sheet1").Range("A1").Cells.NumberForma t = "General"

Graph procedure is attached to the sheet that has the graph under Activate event.

Expand|Select|Wrap|Line Numbers
  1. 'Plot Graphs procedure
  2.  
  3. Worksheets("ReportGraph").Unprotect "mat"
  4.  
  5. Dim MinAC As Double, MaxAC As Double, MnrAC As Double, MajAC As Double
  6. Dim MinUWT As Double, MaxUWT As Double, MnrUWT As Double, MajUWT As Double
  7.  
  8. If Not IsError(Range("P52")) Then
  9.     MinAC = Range("P52")
  10.     MaxAC = Range("Q52")
  11.     MajAC = Range("R52")
  12.     MnrAC = Range("S52")
  13.     MinUWT = Range("P54")
  14.     MaxUWT = Range("Q54")
  15.     MajUWT = Range("R54")
  16.     MnrUWT = Range("S54")
  17.  
  18.     Worksheets("ReportGraph").ChartObjects("Chart 21").Activate
  19.         With ActiveChart.Axes(xlCategory)
  20.             .MinimumScale = MinAC
  21.             .MaximumScale = MaxAC
  22.             .MinorUnit = MnrAC
  23.             .MajorUnit = MajAC
  24.         End With
  25.         With ActiveChart.Axes(xlValue)
  26.             .MinimumScale = MinUWT
  27.             .MaximumScale = MaxUWT
  28.             .MinorUnit = MnrUWT
  29.             .MajorUnit = MajUWT
  30.         End With
  31.         ActiveChart.ChartArea.Select
  32.         With ActiveChart
  33.             .Axes(xlValue, xlPrimary).HasTitle = True
  34.             If Worksheets("Program").Range("D18") = True Then
  35.             .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Unit Weight, kg/cu.m"
  36.             Else
  37.             .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Unit Weight, pcf"
  38.             End If
  39.         End With
  40.  
  41.     Range("A1").Select
  42.  
  43.     Worksheets("ReportGraph").Protect "mat"
  44.  
  45. End If
  46.  
Oct 22 '07 #7
I have written a VB code to export the List View contents to XLS file. But the problem is whenever i try to write a value like "0001", its removing all the trailing zeros and showing only "1" in the cell. I think its becuase of the default cell format (General) in Excel. Is there any way I can change the Cell Format to "Text" through VB code?

thanks,
natesh.
Well when ever you are trying to insert a value like 0001 please make sure you prepend a single quote character before putting it into excel.

so instead of 0001 is should be '0001
Oct 24 '07 #8
>>"whenever i try to write a value like "0001", its removing all the trailing zeros "

I had the same problem even after I formatted the column as text. But I realized that I set the format to Text AFTER I exported the data. So make sure you format it as Text and do so BEFORE you actually write the data.

In my case, column D was the problem. So here's how I set it to Text format ("@") prior to exporting the data. (Mine is in C#, but the same solution should work in VB .NET):

object misValue = System.Reflection.Missing.Value;
Excel.Range range = (Excel.Range)xlWorkSheetBankData.Columns["D", misValue];
range.EntireColumn.NumberFormat = "@";

Carlos A Merighe
Apr 5 '10 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Alan | last post by:
I'm just starting out in C# so this is really a basic question. I have an Excel cell reference for which I need to generate offsets It seems to me that the best way is to start off with a string...
8
by: John Brock | last post by:
I am creating an Excel workbook using VB.NET, and have run into a problem. Excel at times insists on reformatting data that I enter into cells, e.g., converting "01234" to "1234", and this screws...
3
by: natrajsr | last post by:
Hi, I want to load the data of a excel sheet or in the exact excel sheet format into a Rich TextBox control. I have already worked with loading WORD into a Rich TextBox. It is working fine.;...
0
by: nickyr | last post by:
Hi, I want to get the data from one excel cell to a textbox. Nothing more than that. What I have now is this: Dim myxl As Object Dim strdata As String myxl = GetObject ("d:\test.xls")
0
by: payelit21 | last post by:
Hi Friends, can any body help me to convert excel cell from date to general and passinfg value to min value and max value of axis in chart through VB. Thanks Payel
1
by: datttanand | last post by:
how to add drop down list in excel cell using javascript code?
6
by: joemo2003 | last post by:
in excel VBA, we can use cells(1,1).Font.ColorIndex to find out that text's color in cell(1,1), but how can we find out the font, font style (bold, italic, and underline) about that text? thanks
3
TcoUpLoad
by: TcoUpLoad | last post by:
Hi, Anyone knows how i can change cell format in excel with a code line?
4
juve11
by: juve11 | last post by:
hello, i made alot of changes to an excel sheet from visual basic like bordering,setting landscape,margins... but there is a thing a didnt managed to change : the cell format. i have two columns...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.