topic Re: Get special charaters in Macro in QlikView

Get special charaters in Macro

johnan — Tue, 04 Jul 2023 04:07:59 GMT

Hi Forum!

I have a macro that copy data to excel, and set Item + description in the sheetsname and loop. Description can contains special charater,like / and spaces and the macro stops when it finds /,? etc. .

How can i a allow to use them??

1. set Vend = ActiveDocument.Fields("Product Short Description").GetPossibleValues

2. ActiveDocument.Fields("Product Short Description").Select Vend.Item(j).Text

3. appExcel.ActiveSheet.Name = ( Agno.Item(i).Text & " " & Vend.Item(j).Text )

I assume that you should use "" or '' somewhere in the string?

Re: Get special charaters in Macro

Eloiseparkinson — Wed, 05 Jul 2023 04:08:14 GMT

Use double quotes to include the description text within the sheet name. This ensures that special characters are treated as part of the string and not interpreted as code.

In the above code, the Replace function is used to replace the forward slash ("/") with a suitable character like a hyphen ("-"). You can modify the replacement character as per your requirement.

By using the Replace function, you can handle specific characters that might cause issues in the sheet name. This allows you to include special characters in the sheet name without interrupting the macro execution. KFC Customer Satisfaction Survey

Re: Get special charaters in Macro

marcus_sommer — Tue, 04 Jul 2023 11:14:47 GMT

VBS hasn't a direct feature to exclude certain chars. This means you would need an extra logic which looped through the chars of the item and checked it against a list of allowed chars. I remember having done it years ago within a function.

Simpler as this would be you load this field twice and the second time with something like:

keepchar(Field, '...') or the reversed ones per purgechar(Field, '...')

and then grabbing this field within the macro.

Re: Get special charaters in Macro

johnan — Wed, 05 Jul 2023 05:21:57 GMT

Hi, where do i use the double quotes in Macro?

Sub SeleccLista

ActiveDocument.ClearAll false

set doc= ActiveDocument

set docprop = doc.GetProperties

directory = docprop.MyWorkingDirectory

doc.GetApplication.Refresh
set appExcel = CreateObject("Excel.Application")

appExcel.Visible = True
appExcel.WorkBooks.Add()

set Agno =ActiveDocument.Fields("S4 artikelnummer").GetPossibleValues

for i=0 to Agno.Count-1
ActiveDocument.Fields("S4 artikelnummer").Select Agno.Item(i).Text
set Vend =ActiveDocument.Fields("Product Short Description").GetPossibleValues
For j=0 to Vend.Count-1
ActiveDocument.Fields("Product Short Description").Select Vend.Item(j).Text

Set Elemento1 = doc.GetSheetObject("CH02")

appExcel.ActiveSheet.Name = ( Agno.Item(i).Text & " " & Vend.Item(j).Text )
Elemento1.CopyTableToClipboard True
appExcel.ActiveSheet.Paste
appExcel.Sheets.Add
ActiveDocument.Fields("Product Short Description").Clear

next
ActiveDocument.Fields("S4 artikelnummer").Clear

Next

appExcel.ActiveSheet.SaveAs (directory & "\Vendedores " & Hour(Now) & Minute(Now) & Second(Now) & ".XLS")
'appExcel.Application.Quit
End Sub

Re: Get special charaters in Macro

marcus_sommer — Wed, 05 Jul 2023 08:37:35 GMT

It needs to be differentiated which error occurred where to be able to react on them and/or to fetch them in beforehand.

AFAIK there aren't many occasions in which vbs respectively the macro itself will struggle with (invalid) chars. Far more common are the errors caused from the environment - for example by trying to apply a filename with ?\... and so on because they are invalid from Windows storage point of view. Similar things are true for the Excel sheet names and/or the total lengths of the fullpath/sheetnames.

Beside of this many special chars like -+ or a space could be used but then the values needs to be wrapped with an appropriate quoting - most often it will be double-quotes. If strings contain such chars and/or itself some quoting the extra outside-quote-wrapping you need often 3-4 double-quotes. This could be quite tricky but there are plenty of postings about the matter and even a trial and error approach isn't very hard - just comment the naming- and saving-parts and return them within a msgbox. If the strings look like as a hard-coded string - you should apply one just for testing - the entire logic should be working.