Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel Fied headers not displaying properly.

Status
Not open for further replies.

okeyuzosike

Programmer
Jun 15, 2007
15
US
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.
 




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...
Code:
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,

[glasses] [red][/red]
[tongue]
 
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.
 



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

Skip,

[glasses] [red][/red]
[tongue]
 
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
 
Thank you sir. I'll sure try it out and let you know how it worke out.
Have a good evening.
Okey.
 
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.
 
As a programmer you don't know what a variable scope is ?????
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top