topic Re: Macro to store Inputfield Values in QlikView

Macro to store Inputfield Values

Fri, 30 Aug 2013 08:01:52 GMT

Hey everyone,

Im developing a production planning report, multiple users will use this report and some sort of inputfield is required to adjust the inventory stocks.

What i want to do to solve this problem is creating a macro to store the inputfield value to qvd (or excel really dont mind) to adjust the inventory.

anyone has ever did something similar and can give me some advice?

Thks

Juan Pedro

Re: Macro to store Inputfield Values

marcus_sommer — Fri, 30 Aug 2013 11:41:00 GMT

Put your inputfields in (several) tables (in a hidden sheet and perhaps mit additional data, like the qvw, object, user, date or timestamp, keys and so on) which only serve to export into a desired format.

Then the user could click on a button and the export-macro runs. Before the export will execute the macro should check if certain selections are set or not and then set the required selections or breakup with message to the user.

- Marcus

Re: Macro to store Inputfield Values

Fri, 30 Aug 2013 12:21:25 GMT

Hey Marcus first of all thnks for your anserw,

Marcus Sommer ha scritto:

Then the user could click on a button and the export-macro runs. Before the export will execute the macro should check if certain selections are set or not and then set the required selections or breakup with message to the user.

- Marcus

This part is more than clear to me and your solution idea is identical to mine.

My doubt is more related to the VBS part of the solution since i dont really know how to extract the value from the inputfield since in my applications i only extracted fields with selections made in it.

Hope my requirment is clear since my english is more than rusty

Cheers

Juan Pedro

Re: Re: Macro to store Inputfield Values

marcus_sommer — Fri, 30 Aug 2013 12:56:24 GMT

It's from an old (dead?) project which is always on an alpha-level, but the export worked fine. I hope it is helpful.

sub export
 
 dim doc, fld, fldArea, fldClear, i, ii, msg, check, ex_shop, ex_wert, path, user, zeit, dauer, puffer, msg1, ex_bruttonetto
 
 Set doc = ActiveDocument
 Set user = CreateObject("WScript.Network")
 user = user.UserName
 dauer = now()
 
 path = "Path"
 zeit = mid(now(), 1, 10) & "-" & mid(now(), 12, 2) & "." & mid(now(), 15, 2)
 fldArea = array("PlanArt", "Szenario", "Version") '"Index_4", 
 fldClear = array("Jahr", "Monat", "Art", "Bereich", "O-Kategorie", "H-Kategorie", "Kategorie", "Kategorie_BN", "Kanal", "Konzept")
 check = true
 msg = ""
 
 for i = 0 to ubound(fldArea)
 set fld = doc.GetField(fldArea(i))
 'msgbox fldArea(i) & chr(10) & fld.GetValueCount(1)
 ii = fld.GetValueCount(1)
 if ii = 0 or ii > 1 then 
 msg = msg & "Bitte das Feld """ & fldArea(i) & """ selektieren." & chr(10)
 check = false
 end if
 next
 
 if check = false then
 msgbox "Die folgenden Felder dürfen jeweils nur eine einzelne Selektion aufweisen!" & chr(10) & chr(10) & msg
 set doc = nothing
 set fld = nothing
 set user = nothing
 exit sub
 end if
 
 
'Feldselektionen aufheben, damit immer ein vollständiger Planungsstand gespeichert wird
 'Ursprungsselektion auslesen und nach Export wieder setzen --> eventl. per Bookmark ???
 for i = 0 to ubound(fldClear)
 doc.Fields(fldClear(i)).Clear next 
 
 'Verhindern, daß der Export über einen Minutenwechsel hin statt findet --> Matching-Index wäre dann nicht mehr identisch
 if right(formatdatetime(now(), vblongtime), 2) * 1 >= 45 then
 puffer = 61 - (right(formatdatetime(now(), vblongtime), 2) * 1)
 ActiveDocument.GetApplication.Sleep 61000 - (right(formatdatetime(now(), vblongtime), 2) * 1000)
 end if
 
 set ex_shop = doc.GetSheetObject("CH284")
 ex_shop.Export path & "Shopanzahlen\ShopAnzahlen" & "_" & user & "_" & zeit & ".txt",";"
 
 set ex_wert = doc.GetSheetObject("CH292")
 ex_wert.Export path & "Wert\Wert" & "_" & user & "_" & zeit & ".txt",";"
 
 set ex_bruttonetto = doc.GetSheetObject("CH316")
 ex_bruttonetto.Export path & "BruttoNetto\BruttoNetto" & "_" & user & "_" & zeit & ".txt",";"
 
 'doc.Reload
 
 dauer = formatdatetime(now() - dauer, 2)
 if puffer > 0 then puffer = "(Pufferzeit: " & puffer & ")"
 msg1 = "Daten erfolgreich gespeichert und neu in die Anwendung eingeladen" & chr(10) & chr(10)
 msg1 = msg1 & "Dauer: " & formatdatetime(now() - dauer, vblongtime) & chr(10) & puffer
 
 msgbox msg1
 
 set doc = nothing
 set fld = nothing
 set user = nothing
 set ex_shop = nothing
 set ex_wert = nothing
 
 end sub

- Marcus

Re: Macro to store Inputfield Values

Fri, 30 Aug 2013 13:06:11 GMT

Thnk You this is what i wanted will adapt the code you posted next monday and mark your anserw as correct as soon as im done!

Have a nice week end.

Juan Pedro

Re: Macro to store Inputfield Values

Andrea_Ghirardello — Fri, 30 Aug 2013 15:07:54 GMT

Hi,

Try the attached example.

You can load source data, edit values, store partial changes or restore last saved data.

Re: Macro to store Inputfield Values

Mon, 02 Sep 2013 07:18:21 GMT

Very Clean and simple solution.

Thnks for sharing. Exactly what i needed ++

Cheers,

Juan Pedro