topic Re: In a macro, fields with 8-bit names in QlikView

In a macro, fields with 8-bit names

Fri, 07 Sep 2012 13:32:32 GMT

Several of my fields have 8-bit names ("Année" is the French word for "year", for example).

It seems to be a problem when I try to access those fields in a VB macro by using ActiveDocument.Fields("Année").GetPossibleValues

Is there a proper way to access those fields or do I need to rename all my fields ?

Re: In a macro, fields with 8-bit names

marcus_sommer — Fri, 07 Sep 2012 13:44:05 GMT

One Solution in Excel for an export to *.txt, perhaps it's helpful

Public Const cLetters = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZäöüÄÖÜß"

Public Const cNumbers = "0123456789"

Public Const cWhiteSpaces = " " & vbCr & vbLf & vbTab

Public Const cSigns = """|!#$%&'()*+,-./0123456789:;<=>?@[\]^_`€Ø{}²³´–"

Public Function Filter(str As String, ByVal z As Long, ByVal s As Byte) As String

Dim i As Long

For i = 1 To Len(str)

If InStr(1, cLetters & cNumbers & cWhiteSpaces & cSigns & Chr(133) & Chr(160), Mid$(str, i, 1), vbTextCompare) Then

Filter = Filter & Mid$(str, i, 1)

Else

Filter = Filter & "?"

'MsgBox "Zeile " & z & Chr(10) & "Spalte " & s & Chr(10) & "Zeichen: " & Mid$(str, i, 1) & " - Code: " & Asc(Mid$(str, i, 1))

End If

Next i

End Function

Re: In a macro, fields with 8-bit names

Fri, 07 Sep 2012 14:25:16 GMT

Thanks Marcus for your transform function but my problem is that I cannot access the fields of the table "Année" by calling ActiveDocument.Fields("Annee").GetPossibleValues

Re: In a macro, fields with 8-bit names

jonathandienst — Fri, 07 Sep 2012 14:37:03 GMT

Is your problem trying to enter accented characters in the editor(s)?

You can enter accented characters in the script and module editors using the ALT key. For example é can be entered by holding down the ALT key and typing 0233. You could also select and copy the characters from the Windows character map (charmap.exe).

Hope that helps

Jonathan

Re: In a macro, fields with 8-bit names

Fri, 07 Sep 2012 14:54:42 GMT

Thanks Jonathan, but no: it's not a proble of typing the letters.

My proble is that when I try to get values of the table "DateCde" it works with :

Set objSelected = ActiveDocument.Fields("DateCde").GetPossibleValues

Date= objSelected.item(0).Text

While I cannot get the year in table "AnnéeCde" :

Set objSelected = ActiveDocument.Fields("AnnéeCde").GetPossibleValues

Date= objSelected.item(0).Text

I am quite reluctant in changing (almost) all my table names...

Re: In a macro, fields with 8-bit names

marcus_sommer — Fri, 07 Sep 2012 15:20:53 GMT

Solution?

put your field "AnnéeCde" in Listbox or Tablebox and read there the possible Values

Re: In a macro, fields with 8-bit names

flipside — Fri, 07 Sep 2012 18:32:02 GMT

I've tried to replicate this but your code seems to work fine for me (QV10 SR4) so not sure exactly where the issue lies.

That said, a way round it might be to reference the field using its integer field reference, like this ...

set fld=ActiveDocument.GetFieldDescriptions(7)

Set objSelected = ActiveDocument.Fields(fld.Name).GetPossibleValues

For i = 0 to objSelected.Count-1

MsgBox(objSelected.item(i).Text)

next

The trick will be to identify what the field reference actually is using this ...

set flddescs = ActiveDocument.GetFieldDescriptions

for i = 0 to flddescs.Count - 1

fdesc = flddescs(i)

msgbox(fdesc.Name & " is field ref: " & i)

next

The next problem is: Will the value stay at what you find? - probably not if you insert new fields in your script, so therefore you will need to loop through all fields and set a variable when you find the one you want, something like ...

set flddescs = ActiveDocument.GetFieldDescriptions

for i = 0 to flddescs.Count - 1

fdesc = flddescs(i)

if fdesc.Name ="AnnéeCde" then set fldcode=i

if fdesc.Name ="AnnéeCde" then

fldcode=i

end if

next

and use fldcode as the parameter in the GetFieldDescriptions.

flipside

Re: In a macro, fields with 8-bit names

Wed, 12 Sep 2012 09:26:43 GMT

Thank you flipside for the useful code that enables to get all the names of the fields. I tried to do this but did not succeed in.

Actually, my problem is solved and was not due to 8-bits name (but to an empty field)

Sorry for this and thanks for your help!

Re: In a macro, fields with 8-bit names

flipside — Wed, 12 Sep 2012 09:39:58 GMT

Glad you got it sorted.

I've corrected my dodgy code above in case it is of any use to anyone else.