Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
tabletuner
Creator III
Creator III

Macro keeps running after closing document

Dear Forum,

I have to upload the content of chart CH01 (65000 records in size) to a SQL database.

I use the macro below for that task.

The macro works fine. However, when the macro reaches the end, the qlikview program wont close. In the lower left corner of QlikView there is a message: "Macro running".

The document is however closed but the application is not.

Another strange thing to note here is that the application will close if i limit the no of records in the loop to 100. (instead of 65000)

This annoying freeze/hang of the macro prevents my intention of planning the document into task planner. Any help would be appreciated.

Sub upload

Const adOpenStatic = 3

Const adLockOptimistic = 3

'Establish a connection

Set objConnection = CreateObject("ADODB.Connection")

objConnection.open "PROVIDER=SQLOLEDB;DATA SOURCE=xxxxxx;UID=xxxxxx;PWD=xxxxxx;DATABASE=xxxxxx "

Set objRecordSet = CreateObject("ADODB.Recordset")

'' First delete all records from the table.

objRecordSet.Open "DELETE FROM wm_customers",objConnection, adOpenStatic, adLockOptimistic

   

set TableObject = ActiveDocument.GetSheetObject( "CH01" )

For RowIter = 1 to TableObject.GetRowCount-1

set wmcustomer_no = TableObject.GetCell(RowIter,0)

set wmcustomer_name = TableObject.GetCell(RowIter,1)

set wmcustomer_address = TableObject.GetCell(RowIter,2)

    set wmcustomer_place = TableObject.GetCell(RowIter,3)

    set wmcustomer_participantid = TableObject.GetCell(RowIter,4)

    set wmcustomer_id = TableObject.GetCell(RowIter,5)

'Set SQL

objRecordSet.Open _

  "INSERT INTO wm_customers " & _

  "(wmcustomer_no,wmcustomer_name,wmcustomer_address,wmcustomer_place,wmcustomer_participantid,wmcustomer_id ) " & _

    "VALUES ('"&wmcustomer_no.text&"', '"&wmcustomer_name.text&"', '"&wmcustomer_address.text&"', '"&wmcustomer_place.text&"', '"&wmcustomer_participantid.text&"', '"&wmcustomer_id.text&"')", _

        objConnection, adOpenStatic, _

        adLockOptimistic

       

next

Set objRecordSet = Nothing

objConnection.Close

Set objConnection = Nothing

Application.Quit

ActiveDocument.GetApplication.Quit

end sub

0 Replies