Setting Excel Cell Type From Word Macro

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

IWT - 04 Jan 2006 14:05 GMT

Hi,

I'm writing a macro in Word that copies some data over from Word and
puts it into an Excel spreadsheet.

My question is, how do you define the type (e.g. text/number/date etc)
of a cell in Excel. I have some values with an undefined number of
leading zeros so would like to define them as text instead.

Many thanks,

Tom

Reply to this Message

Perry - 04 Jan 2006 17:03 GMT

Below will output the value of the activecell in Excel to: [0000123]
where the original value was [123] (from Word VBA)

Dim xl As New Excel.Application
Dim wb As Excel.Workbook
Dim cl As Excel.Range
Set wb = xl.Workbooks.Add
Set cl = xl.ActiveCell
cl.NumberFormat = "0000000"
cl.Value = "123"

So:
[NumberFormat] is the property of the Range object, you would be looking
for.

Note: always clean up the object variables pointing to whatever OLE
Automation server.
In this case Excel.

Krgrds,
Perry

> Hi,
>
[quoted text clipped - 8 lines]
>
> Tom

Reply to this Message

zkid - 08 Jan 2006 23:27 GMT

Perry,

If you were asking how set the cell's format to be text, then then change
the code provided below where it says:

.NumberFormat = "0000000" to .NumberFormat = "@"

If you want to send the data over as "text," which will just override the
cell's current format without permanantly changing it, then prepend the text
with an appostrophe. For example, instead of sending "00000123", send
"'00000123"
(0000123 becomes '0000123).

To determine all of this, I just recorded the keystrokes in Excel 2003.

If none of this still does the trick, you might try searching in the Office
Excel programming forum.

zkid

> Below will output the value of the activecell in Excel to: [0000123]
> where the original value was [123] (from Word VBA)
[quoted text clipped - 30 lines]
> >
> > Tom

Reply to this Message

Jean-Guy Marcil - 09 Jan 2006 02:44 GMT

IWT was telling us:
IWT nous racontait que :

> Hi,
>
[quoted text clipped - 4 lines]
> of a cell in Excel. I have some values with an undefined number of
> leading zeros so would like to define them as text instead.

What you want is:

ActiveCell.NumberFormat = "@"

But remember that these numbers will now be left-aligned instead of
right-aligned, you may need to change the alignment as well:

ActiveCell.HorizontalAlignment = xlRight

Finally, note that this is a Word group, you may want to post Excel
questions in an Excel group for more thorough answers!

Signature

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
jmarcilREMOVE@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org

Reply to this Message

Zoltar54 - 09 Feb 2006 15:17 GMT

> Hi,
>
[quoted text clipped - 8 lines]
>
> Tom

I am currently workling on a Word project in which I want to have the
informatrion entered into the userform to be exported and used to populate an
excel table. As of yet I have not been able to figure it out. I would
appreciate any assistance you could provide.

Reply to this Message