Excel Fied headers not displaying properly.

[okeyuzosike]

Good morning everyone,
Please I need your assistance. I generated an excel spreadsheet (report) from access file. I have the report headings in Cell (A5) all the way accross to Cell(Q5). I mean, each cell, starting from A5 through Q5 contains the entire 17 headings(fields). On display, the headings looks like this:
N|
A|
M|
E|

instead of displaying like this:

"NAME".
Note: This also affects other fields that has big chunk of characters, like in the "description" field.

I have tried doing: "WS.UsedRange.MergeCells = True", but it was giving me a message that only the upper leftside data will be displayed.

How can I do a global adjustment to either the cells or rows to display normally?
Thank you.

Okey.

 

[SkipVought]

Hi,

Seems that you have EITHER a vbCr, vbLf or vbCrLf character(s) between each alpha character.
You could use this to assign num heading values...

function NewValue(sVal as string)
dim i as integer
for each i in len(sVal)
  select case ucase(mid(sVal,i,1))
    case " ", "_", "A" to "Z", "0" to "9"
      NewValue = NewValue & mid(sVal,i,1)
  end select
Next
end function

Skip,

_[red][/red]

 

[okeyuzosike]

Thank you for your response. What does the above function do?
Below is the routine that I am using to write my report headings in the excel report, then after the headers are written, I then do my CopyFromRecordset to import/write the detail section.

CODE:
=====
Dim i As integer
Dim rsin As Recordset

For i = 0 To rsin.Fields.Count - 1
WS.Cells(5, i + 1).Value = rsin.Fields(i).Name
Next

Thanks.
Okey.

 

[SkipVought]

It removes any characters other than the ones stated in the Case statment.

Skip,

_[red][/red]

 

[PHV]

Example of use of Skip's function:
For i = 0 To rsin.Fields.Count - 1
WS.Cells(5, i + 1).Value = [!]NewValue([/!]rsin.Fields(i).Name[!])[/!]
Next

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[okeyuzosike]

Thank you sir. I'll sure try it out and let you know how it worke out.
Have a good evening.
Okey.

 

[okeyuzosike]

Hello Mr. PHV,
I've got one more question: "How then do I combine the skip function and my heading module since both are using integer (i)?

I mean;

function NewValue(sVal as string)
dim i as integer
for each i in len(sVal)
select case ucase(mid(sVal,i,1))
case " ", "_", "A" to "Z", "0" to "9"
NewValue = NewValue & mid(sVal,i,1)
end select
Next
end function

AND

This module:
For i = 0 To rsin.Fields.Count - 1
WS.Cells(5, i + 1).Value = rsin.Fields(i).Name
Next

Thanks.
Okey.

 

[PHV]

As a programmer you don't know what a variable scope is ?????

 

[okeyuzosike]

If you don't mind, please enlighten me.
Thanks.

 

[okeyuzosike]

By variable cope, I am assuming you mean using a variable depending on where it is defined: either locally or globally or within procedures.
Thanks.
Okey.