Column Header value for Range Name and Pivot Item 2

[bojzon]

How to control (with VBA) cell value in column header (restricted characters,blanks,...), before creating (changing) range name and before creating (changing) pivot table (excel 2000 and more). Are both limitations the same or different?

Thanks
Bojzon

 

[SkipVought]

Hi,

Please explain in detail, including some examples that demonstrate the problem?

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[bojzon]

When import txt and save as xls need VBA control if first cell in column range (cell value) is not valid to create names:

NAME examples 1
Cell B1=:
B1=111
B1= blank
B1=///
B1=A1%
B1=01_02

Msg = Msg & ActiveSheet.Name
MsgBox Msg
ActiveWorkbook.Names.Add Name:=Range("B1").value, RefersToR1C1:= _
"=OFFSET(" & Msg & "!R1C2,0,0,COUNTA(" & Msg & "!C2),1)"

Err 1004 > name is not valid



NAME examples 2
Cell B1=:
B1=a % /2
B1= blank
B1=%A%
B1=///
B1=01_02

Insert>Name>Create>top row
B1=a % /2 >>>>>>>>>>>>>>>>> a____2
B1= blank >>>>>>>>>>>>>>>>>> nothing
B1=%A% >>>>>>>>>>>>>>> A
B1=/// >>>>>>>>>>>>>>>>>> nothing
B1=01_02 >>>>>>>>>>>>> _01_02

 

[SkipVought]

Please post an example of the first X rows of data you are importing.

You realize that if you use Data > Import External Data > IMPORT, you can control which record becomes the FIRST ROW imported, and you can PARSE the data if necessary.

I would recommend AGAINST OPENING a text file in Excel. Use IMPORT instead!!!

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[bojzon]

With opening txt to xls I add additional data (new columns), calculated from imported columns so I need to open it. First imported row txt to xls become range names and pivot items. I want to examine if imported first txt row
have structure, not conflict with names, created with VBA.
I haven't found if there is any help about non usable characters with "creating names".

txt structure

10OE10 %OE% Naz%iv Ob?ina Nasl / ov Skupna-PC Dat./ akt. Delilno SM 00 Parameter
SM 9060 SM_P STAVBA, GER 5528 900 0 A 25.08.1999
SM 6276 E_GOST.PODJETJE TROJANE 5189 TROJANE 11 0 A 25.08.1999
SM 1884 E_VO? E DELTA 5094 POSTOJANA, TITOVA 2 0 A 06.09.1999


xls names result

Column_DATA Names_created
10OE10 _10OE10 =SM_test3!$A$2:$A$4
%OE% OE =SM_test3!$B$2:$B$4
Naz%iv Naz_iv =SM_test3!$C$2:$C$4
Ob?ina Ob?ina =SM_test3!$D$2:$D$4
Nasl / ov Nasl___ov =SM_test3!$E$2:$E$4
Skupna-PC Skupna_PC =SM_test3!$F$2:$F$4
"blank" nothing
Dat./ akt. Dat.__akt. =SM_test3!$H$2:$H$4
Delilno SM Delilno_SM =SM_test3!$I$2:$I$4
0 nothing
Parameter Parameter =SM_test3!$K$2:$K$4

Thanks
Bojzon

 

[xlbo]

You need to loop through your column headers and test for spaces and illegal characters

Would suggest writing a function to check 1 cell - you can then loop through your header cells and apply that function to each

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

"With opening txt to xls I add additional data (new columns), calculated from imported columns so I need to open it."

There is absolutley no distinction in your statement between OPENING the text file with Excel or IMPORTING the text file into Excel. OPENING a text file leads to the possibility of certain data being misinterpreted by Excel.

I strongly recommend AGAINST OPEN and FOR IMPORT.

I agree with Geoff on the headings.



Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[SkipVought]

Regarding names:
[tt]
Column_DATA Names_created
10OE10 _10OE10 =SM_test3!$A$2:$A$4
%OE% OE =SM_test3!$B$2:$B$4
Naz%iv Naz_iv =SM_test3!$C$2:$C$4
Ob?ina Ob?ina =SM_test3!$D$2:$D$4
Nasl / ov Nasl___ov =SM_test3!$E$2:$E$4
Skupna-PC Skupna_PC =SM_test3!$F$2:$F$4
"blank" nothing
Dat./ akt. Dat.__akt. =SM_test3!$H$2:$H$4
Delilno SM Delilno_SM =SM_test3!$I$2:$I$4
0 nothing
Parameter Parameter =SM_test3!$K$2:$K$4
[/tt]
Values beginning with NUMERIC characters are preceeded by an UNDERLINE.

Certain values like C, R or RC will be given a TRAILING UNDERLINE.

All this to say, WHY AGONIZE over names containing underscores?

Use Insert > Name > Create - Create names in TOP row...

activesheet.cells(1,1).currentregion.createnames true, false, false, false

and be done with it. Reference the Names Collection if necessary.



Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}