Macro keeps running after closing document

tabletuner — Wed, 08 Nov 2017 01:45:29 GMT

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

Set objRecordSet = Nothing

objConnection.Close

Set objConnection = Nothing

Application.Quit

ActiveDocument.GetApplication.Quit

end sub

topic Macro keeps running after closing document in QlikView

Macro keeps running after closing document