Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
noman212
Creator III
Creator III

Insert Macros

Hi Friends,

I want to write macro which takes a value from Variable and insert into the table .

i wrote macro but it doesn't work.

___________________________________________________________________

sub oracle

set db = CreateObject("ADODB.Connection")

db.open "DSN=xyz_server;UID=user;PWD=user"

set Orderfile = db.execute("INSERT INTO capture (Current_date,FNA,TIFF) values ('$(UserID)','$(Password)','$(Full Name)');")

set Orderfile = db.execute("COMMIT;")

db.Close

end sub

_____________________________________________________________________

UserID,Password,Full Name/ These are variable here which i used in Dasbhboard.

Please help. how can i resolve this issue.

1 Reply
Anonymous
Not applicable

Hi

First you need to get the variable in Macro (dashboard variables will not work directly here)

ex. Password= ActiveDocument.GetVariable("Password").GetContent.String

see below Macro . let me know if you need any more help on this.

Sub Insert_Holiday

Dim objADO

Dim objRs

Dim strSQL

Dim vTC_ID

dim vTC_IDs

dim a

dim x

dim j

DIM vHoliday1

DIm vHolidayCount1

vHoliday1=ActiveDocument.GetVariable("vHoliday").GetContent.String

vHolidayCount1=ActiveDocument.GetVariable("vHolidayCount").GetContent.String

if vHolidayCount1=1 then

intAnswer = _

    Msgbox("Do you want to insert the Holiday?", _

        vbYesNo, "Insert Holiday")

If intAnswer = vbYes Then

       

Set objADO = CreateObject("ADODB.Connection")

Set objRS = CreateObject("ADODB.Recordset")

objADO.Open "Provider=SQLOLEDB.1;Data Source=ABC;User ID=A;Password=X;"

Set objRS=objADO.Execute ("select count(*)from .[dbo].[Table1] where Holiday = '"&vHoliday1&"'" )

'Msgbox(objRS(0))

j = objRS(0).Value

if j>0 Then

Msgbox(" This Holiday already preasent in the list" )

Else

strSQL="INSERT INTO .[dbo].[Table1] (Holiday) values ('"&vHoliday1&"')"

Msgbox("Holiday Inserted" )

'end if

objADO.BeginTrans

objRS.Close

objADO.Execute strSQL, iAffected

objADO.CommitTrans

objADO.Close

end if

Else

Msgbox ("Holiday Not Inserted" )

End If

ELSE

Msgbox ("Please Select a Holiday Date!" )

END IF

End Sub