Format Number as Text Through VBA 2

[RichardOneil]

Hi All-

I have a Word Document that uses VBA to get user input and then create a merge document based on this information. The user has the option to create an Excel file that contains all the data in the merge source document. Two fields that are being somewhat problematic are the Emp Number and Dept fields because they often contain leading and/or trailing zeroes that Excel drops. I am trying to use VBA to format the column that these fields will be in as text, but I am not having any luck. This is my code:

Set xlApp = CreateObject("Excel.Application")
xlApp.Application.Visible = True
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
xlSheet.Columns("C").Select
xlSheet.Selection.FormatNumber = Text

Everything works except the last line where I get the "Object doesn't support this method or property" error. I have been looking through the Help file, but I can't figure out which object/method to use. Any help would be great. TIA.

Richard

 

[PHV]

You may try this:
xlSheet.Selection.FormatNumber = "@"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RichardOneil]

PHV-

Thank you for the help. You put me on the right track. The FormatNumber method doesn't work with columns, cells etc... but NumberFormat does. The working code is:

xlSheet.Columns("C").NumberFormat = "@"

A star for you.