Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  HOME    TRACKER    Visual Basic

Excel VBA - Format Value In Text Box If..

On userform I have a textbox. Manually a number can be entered.
Want to format such a way that if the number is <10, then it should look, say, 05 istead of 5. If 10 or more, should be as entered.
This texbox has controlsource in excel cell and that also to be formatted same way.
Requirment may look as a 'decorative' purpose, but can this be achieved?

View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Parsing Specific Text Pattern From A Text File And Save It On Excel Format
I just registered as a new member for this forum....i'm only starting to learn VB and hope i can get more information and tips from you guys. Anyone here can help me how to parse specific text pattern and save it on an Excel file?....


My text file would look like this:

Device_name : W8510HA7-BAN-C
Lot No. :14023562.1-Q45218.1
TestProgram :W8510A7_REV06_100C

 FAIL 12 25 12 10.00% Leakage Fail
 FAIL 13 26 42 42.02% bandgap Test Fail
 FAIL 14 27 100 70.11% scan_core Fail

I want to parse the value for the Device_name, Lot No., TestProgram and all others
like QTY and TEST ITEM into an predefined excel format( ex.A1evice_Name, A2:Lot No., ......etc).

I hope you can help....thanks!



Format Excel Cell To Text
Hi, I have created a csv file that I want users to be able to open in excel without it removing the leading zeros of numbers.

If I import the file into the spread sheet and use text as the data type no problem but to open the csv file it treats it as a number and removes the leading zero's.

How can I prevent that?


How To Format Entire Excel Sheet As Text?

I'd like to know how I can format an entire excel sheet in VB6 as Text. I don't want a "0E1" to turn out as 0.00E+00.



Excel Converting Text To Unwanted Date Format
I have a VB6 app that exports information from a purchasing data base to Excel. Vendors get the spreadsheets and then fill them in to "bid" on sales.

The problem lies in the product numbers that are used in the database. The naming convention is six alphanumerics. Apricot jam might have the designation of APR01 in the database. Apricot jelly might be designated as APR02. As you may have already guessed, when I put these product numbers into an Excel spreadsheet, the spreadsheet converts it to 1-Apr or 2-Apr, assuming it is a date.

How can I stop Excel from turning APR01 into 1-Apr?


Change The Format Of Excel Cells From Text To Number
I have an excel macro that gets some sqlserver database stats and puts them into a worksheet, I have the problem that the data that gets extracted and put into the excel spreadsheet is put in as text and I would like to create a chart/graph of this output.

The data looks like this in excel: -

15/10/02dbname1 3136.00 MB3130.35 MB
15/10/02dbname29000.00 MB7971.60 MB
15/10/02dbname32500.00 MB2486.77 MB
15/10/02dbname42500.00 MB2495.15 MB

How do I convert the FREESPACE and TOTAL SIZE column into a number so that I can then create a graph/chart.

Or another solution could be to have the data put into the excel spreadsheet as a number, we have a very strict change process to make changes to the databases so I would prefer to use the first option.

Any help would be appreciated...

Change Excel Sheets Format Cells To Text From VB 6.0
Change Excel Sheets Format Cells to Text from VB 6.0 ...?

Changing Text File To Access/Excel/Word Format
I have a problem. Can you convert the file to only a list in Access/Excel/Word

now the format is like this

anchovy imagesaa00.gif1261.gif ancient imagesaa00.gif1262.gif anciently imagesaa00.gif1263.gif ancillary imagesaa00.gif1264.gif and imagesaa00.gif1265.gif anecdotal imagesaa00.gif1266.gif

and i want it like this:-

anchovy imagesaa00.gif1261.gif
ancient imagesaa00.gif1262.gif
anciently imagesaa00.gif1263.gif
ancillary imagesaa00.gif1264.gif
and imagesaa00.gif1265.gif
anecdotal imagesaa00.gif1266.gif

Please send me a code or any way you can tell me


Want To Read All The Excel Table Cells In Text Format With ADODB
I read an Excel table with ADODB. In some columns of this table, can exist numeric or alphabetic values. I would like to read all the cells in text format, but automatically the columns are considered by ADODB as numeric or alphabetic and if an alphabetic value is read in a "numeric" column the value returned is Null, and, more surprising!, if a numeric value is read in an "alphabetic" column, the value returned is also null.

Is there a solution to force ADODB to process all the colums as Alphanumeric and to return all the values correctly?

Thank you for your help

[solved]How To Format Data Extracted From Text File In Excel?
How am i to format data extracted from text file in Excel? I can't use pivot table as it is only used for 'purely' numbers and for calculation. I have attached the text file and the Excel format. It is supposed to show about a vehicle (TTxxx) against Periods (0,1,2,etc). The data which is to be sorted is the blocks the vehicle went to in the specified periods.Pls have a look. I need help. By the way, The 1st col in the txt file is the TT number(veh num), 2nd col is the blk it went From, 3rd col is the blk it went To, 4th col is the start time period,5th col is the end time period, and the last col is remarks(may be empty).Actually it is a chuncky file, but im sending u only an eg of 5 records. For eg, the first record in the txt file is to be read as "TT100 moved from blk B01 to B03 from start-time period 0 to period 1".... and so on...Pls look at the excel file the way i wish it to be generated. Thanks.

Edited by - Siraj on 4/13/2003 11:57:02 PM

Can Excel Format Be Convert Word Document Format??
can I write the macro that convert the excel data to word?

Exporting Data In "Text" Format To Excel
Hi, I have a project where I am receiving data in text format "12.03 11.11 97.44" through the Communications Port using Visual Basic 6. I need to export this data into an existing excel spreadsheet and be able to analize it. By that I mean format the cells and use the data to create a chart. I have the code to create a chart and format the cells. The section I am still missing is how to export this data to excel from VB6? For instance: Data = "12.03 11.11 97.44", each piece of data in this string consists of 4-digits. Each piece should go into a cell and so on. Data will be continuously be received by VB6. Data will have to be placed in the subsequent rows. Like a data table. What will be the way to export? I need to be able to analyzed the data once it is in excel.
The way I have my program right now is receiving data in Text format.


Public Sub OpenWorkSheet()

Dim oXLApp As Excel.Application 'Declare the object variable
Dim oXLBook As Excel.Workbook
Dim objExcelCI As Excel.Chart
Dim oXLsheet As Excel.Worksheet

Dim rxBuffer As String
LogFile = FreeFile

Set oXLApp = New Excel.Application 'Create a new instance of Excel
Set oXLBook = oXLApp.Workbooks.Open("C:LogAllData.xls")
Set oXLsheet = oXLBook.Worksheets(1)

oXLApp.Visible = True

rxBuffer = "10.32 11.05 2700.45"
oXLsheet.Cells(2, 1).Value = rxBuffer ' Places the whole string in one cell...NOT GOOD!

oXLBook.Close SaveChanges:=True
Set oXLBook = Nothing
Set oXLApp = Nothing

End Sub

Convert The General Format To Text Format
hi all ,
In excel sheet i want to convet the particular from general format to text.can we do in VBA macro?.

when I'm importing the data from xml to excel 0567899 field value is imported as 567899 as a general format in excel.but i need this value shud be 0567899 in text format can any one help this thing.

Thanks and regds,

I Am Exporting An Access Query To Excel, How Can I Define The Excel Cell Size/format
I am using TransferSpreadsheet to Export a Query to Excel with a button from Access.

How can I define in Excel when I export it, the size of the cells, the type of letter (Arial, Bold), The Background color.

Private Sub Impacto_Click()
DoCmd.TransferSpreadsheet transfertype:=acExport, _
spreadsheettype:=acSpreadsheetTypeExcel9, _
TableName:="Impact", _
Filename:="C:Impact.xls", _
End Sub

Format A Number To Text Using Format()
I'd like to take a column of numbers in excel and format them to a fixed number of text carachters with leading zeros. Example:

1 to 001
25 to 025
131 to 131

I can do this with the =text(cell, "000") in the spreadsheet, but would like to use a macro to do it for me.

Here is what I have so far:

data = ActiveCell
ActiveCell = Format(data, "000")
ActiveCell.Offset(1, 0).Select

Loop Until ActiveCell = ""

This script does not format any of the numbers, but I know it is going through the loop. Help Please!!

How To Create Report In Word Format Through Excel Or Excel Macro
I want to create one Report in Word document Format through Excel.
I have some Data in excel.Now How can I create report in word format using Macro or any other feature, if yes then How ??
Can anyone help me in solving this.
Thanks a ton !!!

Create Excel Format File Without Installing MS Excel
Create Excel format file without installing MS Excel.
Is it possible?

Saving In Excel Format Without Using Excel Object
How can I save the report in excel format without using Excel objects.
Thanks in advance.

Adding Text To Rich Textbox Without Changing Format Of Existing Text?
Hello all. This is my first time working with RTB's, and I'm having a little trouble getting started. I'm sure it's some little problem of syntax/semantics, but I can't figure it out. A little help would be greatly appreciated!

I have a RTB to which I am trying to add texts of different formats. When I add each string, the entire box text takes the most current format. This makes perfect sense to me, as I am essentially rewriting the ENTIRE textbox (I am using the ".Text = .Text & "NEWTEXT" format that I am accustomed to using for textboxes.

What am I doing wrong? Thanks!

VB Code:
'1.  Create a new form.'2.  Add a button named butGo.'3.  Add a RTB named rtbText.'4.  Cut & Paste the procedure below. Private Sub butGo_Click()Static Counter As IntegerCounter = Counter + 1With rtbText    Select Case Counter        Case 1: .Font.Size = 16                .Font.Bold = True                .Text = .Text & "This is a test..."        Case 2: .Font.Size = 12                .Font.Bold = False                .Text = .Text & "This is only a test!"        Case 3: .Font.Size = 6                .Font.Italic = True                .Text = .Text & "  Can you see me?"    End SelectEnd WithEnd Sub

Rich Text Format To Text Converter
I need a converter for my vb project to convert rich text format to text file.
are there any dll's out there?



Rich Text Format To Text Converter
I need a rich text format to text converter to use in my Vb project.
Where can I find one?



Text Encryption In Rich Text Format
How can i get ascii text encryption to work with a rich text box. An example i found such as this(below) will not work with a richtext box. Anyone have any ideas or examples to show me?..Working out of a rich text box and a password:
'Encrypt text
Private Function EncryptText(strText As String, ByVal strPwd As String)
Dim pwdpos As Integer, c As Integer
Dim strBuff As String


'Convert password to upper case
'if not case-sensitive
strPwd = UCase$(strPwd)

#End If

'Encrypt string
If Len(strPwd) Then
For pwdpos = 1 To Len(strText)
c = Asc(Mid$(strText, pwdpos, 1))
c = c + Asc(Mid$(strPwd, (pwdpos Mod Len(strPwd)) + 1, 1))
strBuff = strBuff & Chr$(c And &HFF)
Next pwdpos
strBuff = strText
End If
EncryptText = strBuff
End Function


Format Of Text Maintenance In Text Chat
Hi everybody..,

                  We have an Option for text chat through LAN....programed using Winsock.
The text msg typed with formats say for example with size 12,color red by one user...the receiver at the other end who will see the msg also should be able to see ,,the same format of text.

Kindly do help me out.....

Itz very urgent

Thankyou In Advance
Suresh Kumar

Reading Specified Format Text From Text File
I have below Sample text. I need find in the text specfice Key name its Value(HKEY_CURRENT_USERControl PanelInternational), If I find this I need to go inside of this and read Value 0 (Name: Its value and Data: Its value),Value 1 (Name: Its value and Data: Its value)....Value N (Name: Its value and Data: Its value).

Please provide me same code for this. forum leader help on this

Key Name: HKEY_CURRENT_USERControl PanelInput MethodHot Keys0000202
Class Name: <NO CLASS>
Last Write Time: 8/7/2006 - 7:07 PM
Value 0
Name: Key Modifiers
00000000 03 c0 00 00 .À..
Value 1
Name: Target IME
00000000 00 00 00 00 ....
Value 2
Name: Virtual Key
00000000 4c 00 00 00 L...

Key Name: HKEY_CURRENT_USERControl PanelInternational
Class Name: <NO CLASS>
Last Write Time: 8/7/2006 - 7:07 PM
Value 0
Name: iCountry
Type: REG_SZ
Data: 1
Value 1
Name: iCurrDigits
Type: REG_SZ
Data: 2
Value 2
Name: iCurrency
Type: REG_SZ
Data: 0
Value 3
Name: iDate
Type: REG_SZ
Data: 0
Value 4
Name: iDigits
Type: REG_SZ
Data: 2
Value N
Name: iLZero
Type: REG_SZ
Data: 1

Rich Text Box Format To Text File
I want to be able to save the contents of a Rich Text box to a text file so that the formating looks the same as the way it displayed on the screen. The rich text box does wrap the words correclty but when you save the text box conents, it's just one long string. Any ideas?

Format Cel In Excel
When I retrieve a date type field from SQLServer and put it into Excel via VB it formats it in the number format. Anyone know the code to format it?

To Excel Format
good day vb gurus just want to ask, how can i make my report be in excel format. what i mean is, i have this select statement and i just want its result appear in excel. thank you so much. and more power,

How To Convert Pdf File Format To Text File Format
We are about to build an application using VB6 which will parse a PDF data file and update a database with necessary information. The structure of the data file is complex and several calculations are to be performed on some of the columns. Is it possible to read the PDF format directly from VB? If not then, is there any free activeX component available for converting the PDF to text format? Itís worth mentioning here that to retain the exact structure/formatting of the data file after converting to text mode is of prime importance for parsing reason

Auto Format The Text On A Text Box
Hi all,

Hope that somebody can help!!!!

I have a text box which I want to auto format as date as the user enters the numbers. For example,

User types 06 and a "/" is automatically entered (06/); then user types 08 and again a "/" is automatically entereded to look like this 06/08/. Lastly, the user enters the 2005 and the text looks like this 06/08/2005.

Any help would be appreciated.



Text Format In Rich Text Box
i've got richtext box and i've set the font and size, ok, but when you copy and paste from another app into it, the font and size from the copied text is used. anyone know of a way to use the font and size i am using?. The only way i can think of is to select all the text in the rich text box and reset the font immediately after new text is added.

(please excuse the crap typing, got one arm in a sling after a nasty squash accident)



Get AND FORMAT Excel Data
Hi there

please let me know if that is just insane. It seems simple to me (conceptually) but i am new to this, and for now, i've been totally unable to even start it! I hope someone else here will be able to point me into the right direction. Or at least, please have pity of me and just tell me to stop dreaming ;-)

Here's the deal:

1. I have an Excel spreadsheet with a few thousand rows of data in it. They all have the same structure (just like an access table)

2. I want to have a macro that will allow me to insert any one of these rows at a particular place of a word document. I'd have to indicate some id (to identify the row), and then, magically (that is, with VBA), the corresponding data would appear in my document, formated.

I have tried the DATABASE field, and it works allright. *BUT*, it doesn't put the data the way i want it. I want to be able to put the data vertically, not horizontally (to have the column titles on the left side, and the data itself on the right side, one field/value pair above the other). So far, i have been unable to find any switch to do that, so i am just lost and got to think into VBA. There must be something there that will help.

One important aspect, is that i'd like to preserve the link between the word data and the excel source, meaning, that i would like to be able to update (eventually) the Excel spreadsheet and to see the updates being done automatically in the word document (i guess i'd still need to "update fields", but that would be cool enough)

I thought of using DATABASE queries and then have a macro read the result and write it again below the DATABASE field, and then hide the field, but i quite didn't find a way to do that. Actually, i didn't find anything at all (beside the DB field)...

All sort of help and/or pointers is most welcomed. If you have the complete solution, then, it would be wonderful. If you don't have it complete, but just some part, please answer anyway. I might be able to use every information drop.

Thanks a lot


VB For Excel - Format Columns
I have formatted an entire column to have the date format yyyy-mm-dd. however not everything in this column is a date. the other value is the number 1. so formatting this column changes it to 1900-01-01. I want to be able to write a macro to find all the 1900-01-01 and change the format to text (numberformat = '@')

Any help would be much appreciated.....

Julian Format Like Excel
I would like a way to get the julian format like excel shows. All the code i have seen does not match the julian format like excel. Does anyone know how this is acheived?

Format Excel Columns Through Vb
Hello fellow vb'ers,
I am creating an app. that creates a spreadsheet in Excel, everything works just fine except, when I pass a 16-digit number to Excel, it converts that number into scientific notation. I tried using the Numberformat method but it rounds the number down. Is there a way to format an individual column to accept it the way it is?

An Excel Format Program?
Iím new to VB, as youíll soon figure out here.
Iíve taken a job that really didnít have much to do with VB but recently been tasked with making a small program in it.
If you guys can think of a better or preferably easier way then Iím all ears.
I have VB6, I donít have .net and probably best as I would be even more lost with it.

The company I work for had a contractor create the program I am going to have to try and re-create. He did a good job, and thereís nothing wrong with it but when the current contract ends they may want it back.
The program, from what all I am able to tell without seeing any source code performs a relatively simple function.
Everyday we get a zipped file that has 5 excel files in it.
The program in question takes in the zipped file, unzips all five excel files and then Ďcleans them upí.
There are a lot of stray characters and screwey formatting.
It process one by one and shows the progress of each and when completed it converts each one to the correct format which is still in excel.
It essentially gets rid of the garbage from the original file.
I wish I had more info and when I do Iíll post it but Iíve only been lucky enough to have this task for like 2 days.
Any info will be much appreciated.

About The Date Format In Excel
Hey guys:

Thanks for your reading. My question is if it is possible to construct a date format of "mm/dd/yyyy" in excel, here "mm/dd/yyyy" is not the regular one, I mean I need the exact "mm/dd/yyyy". For example, in terms of regular counterpart of "mm/dd/yyyy", "1-Jan-2008" appears as "1/1/2008", but I need it to appear as "01/01/2008". So, is it possible to make it? If anyone knows, please reply the solution to this thread or drop me an email at summeryxx at aol dot com.

Thanks again for your time!


Excel VBA-Number Format If...
I have 4 nos. in validations (10,12, 15.2, 16) for a cell in excel sheet. I want to format the cell such that 15.2 if selected will display 15.2 but if any other is selected will not display decimal (say, should not display 10.0 if 10 is selected).
I put code in VBA as:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("A1").Value = 15.2 Then
Range("A1").Value = 15.2
Else: Range("A1").Value = Range("A1").Value
End If
End Sub

This works fine, but...
the undo button is inactive, that means if want to undo the input, I cannot.
For all the things in the 'Worksheet_SelectionChange' this happens (the Undo button cannot be clicked)
Please explain why and provide a solution.

How Can We Format Font In Excel?
I try to format font size, font color, font bold in cells(1,1)
So, I created a macro like this:
When I run the macro, the font dialog pop up...Then I don't know how to code after that...Can anyone help me?




Convert Pdf To Another Format (rtf Or Txt Or Excel)
Hello everybody,
I'm working on an application that suppose to take pdf files and import them into some other format and parse the data out and extract specific information.

How can i go about doing this?

Any input would be greatly appreciated.


VB To Excel In Tabular Format
I have a VB app, in which I am able to grab data off an application with tables. I wish to export this data out to excel, in table format.

Any help?

txtResult.Text = objCapture.GetTextFromRect(hwnd, CLng(txtX.Text), CLng(txtY.Text), CLng(txtWidth.Text), CLng(txtHeight.Text))

The textbox window, 'txtResult.Text' is what displays my tabular data. However, the text box only displays it wiht spaces, and when that data is copied and pasted into excel, it pastes onto a single cell. I would like to have each entry pasted into a different cell, in tabular format.
The data I receive is displayed like follows.
17 21
18 23
79 77
12 81
I want this data to be aligned in columns and rows.
Any help on how ot send data to excel, and do it in tabular form?

Copy Excel Format
how to copy flexgrid data to excel with cellbackcolor and fore-color
i m having a heirarchichal flexgrid control with lots of data filled with each row having different colors.
i want a way by which i can copy selected potion of flexgrid data to excel sheet with backcolor of rows of selected in flexgrid.
like when we copy one excel sheet contents to another..we get data with its colors.
i able to copy data with cells but not backcolor.
i am now using "vb.clipboard.settext data"
pls help me.

To Open Excel With Format
I need to open excel in my application and display it in a grid ... but i have some formatting in that excel but when i open it it simply shows me data no formatiing is there plz help me

Set oXLBook = oXLApp.Workbooks.Open(Trim(cmnDlgs.filename))
Set oXLSheet = oXLBook.Worksheets(1)

How To: Grid To Excel (with Format)
Hi guys. I need to create an excel sheet with data from a grid. I can do this, but i don't know how to apply a format to target cells. So, if i export a column with numbers, excel see them as text wich forbids me to do any aritmetichal operation with that cell (in excel). The same happens with date information. In excel all is text.

Here is the code:

VB Code:
Dim AppEx As Excel.ApplicationDim AreaLibroex As Excel.WorkbookDim AreaHojaEx As Excel.Worksheet Set AppEx = CreateObject("excel.application")Set AreaLibroex = AppEx.Workbooks.AddSet AreaHojaEx = AreaLibroex.Worksheets(1) For I = 0 To grid.Rows - 1    For J = 0 To grid.Cols - 1        AreaHojaEx.Cells(I + 1, J + 1) = grid.TextMatrix(I, J)    NextNext AppEx.Visible = True Set AppEx = NothingSet AreaLibroex = NothingSet AreaHojaEx = Nothing

How could i do this using dao? I mean, with a recordset or query..

Well, i hope u could understand me (sorry for my english)


VBA - Excel Column Format
In Excel sheet

Select Column--> Right Click --> Format Cell --> Text
This will make the column as text field column, so that we can enter alpha numeric values (eg:0001123).
Is it possible to do from visual basic code to set the column property.

Please do reply @ you earliest possible time

Thanks In Adv

Excel Sheet Format
this code i generate from vba doesn't work with vb6 , any help??


With Selection.Interior
.ColorIndex = 2
.Pattern = xlSolid
End With

I need to select the whole worksheet and to change the color ,pattern.


Format Cells In Excel
In VB, How do I format a column of cells as phone number format,
i.e, something like "XXX-XXX-XXXX" or "(XXX)XXX-XXXX".


Excel File Format?

I'm an old hand at writing to Excel files from VB6 using OLE, but it's so SLOWWWWWWW. I was just wondering, is it not possible to write directly to a file that in the same format that Excel uses? Surely that would be a million times faster (and probably a million times more complicated, but complicated is good, no? ;o)


Losing Excel Format ;-(

Is there any way of locking the excel object created thru VB.I am facing a strange problem.If an excel object is open and when data is written to excel thru vb the formatting is lost.


Format A Excel Cell
Anybody know how to set a cell's format to center in an excel object? I don't seem to be able to do this with the activesheets object?


Copyright © 2005-08, All rights reserved