Setting Excel Cell Type From Word Macro

[IWT]

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

 

[Perry]

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

 

[zkid]

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)

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,

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

 

[Jean-Guy Marcil]

IWT was telling us:
IWT nous racontait que :

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.

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!

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Zoltar54]

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

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.