Macro stuck on Executing

Fri, 26 Sep 2014 07:29:25 GMT

I am executing a macro that writes data record by record to a database and then writes that data into a CSV file.

When I execute this Macro on a large number of records like 60,000, one it takes a very long time, but once it has written all the data to the database and produced the CSV file, the macro Edit Module pops up and the state says Executing and it stays like this, no in effect the macro never ends execution.

Does anyone have any idea why this might be happening?

Re: Macro stuck on Executing

marcus_sommer — Fri, 26 Sep 2014 10:48:54 GMT

It looks like an error after the produce of the csv-file - without code it's only guessing.

- Marcus

Re: Macro stuck on Executing

Fri, 26 Sep 2014 10:56:15 GMT

Its running at the moment, so will post the code once it has stopped.

It doesn't do this however if I am processing a lower amount of records for instance 20,000 records. It completes successfully.

Re: Re: Macro stuck on Executing

Fri, 26 Sep 2014 11:24:02 GMT

It is quite a long function

Function NewCampaign(typeOfFile)

set campaignName1 = ActiveDocument.GetVariable("CampaignName")

filename = ActiveDocument.GetVariable("Filename").GetContent.string

IF campaignName1.GetContent.string = "" then

call NoCampaignName

elseif filename = "" then

call NoFileName

else

var = Replace(filename,"\","_")

var = Replace(filename,"/","_")

var = Replace(filename,":","_")

var = Replace(filename,"*","_")

var = Replace(filename,"?","_")

var = Replace(filename,"<","_")

var = Replace(filename,">","_")

var = Replace(filename,"|","_")

set tb = ActiveDocument.GetSheetObject("TB01")

set XLApp = CreateObject("Excel.Application")

XLApp.Visible = False

set XLDoc = XLApp.Workbooks. Add

set objExcelSheet = XLDoc.Worksheets(1)

if typeOfFile = "ELocation" then

objExcelSheet.Cells(1,1) = "CustomerId"

objExcelSheet.Cells(1,2) = "Company Name"

objExcelSheet.Cells(1,3) = "Title"

objExcelSheet.Cells(1,4) = "First Name"

objExcelSheet.Cells(1,5) = "Last Name"

objExcelSheet.Cells(1,6) = "Telephone1"

objExcelSheet.Cells(1,7) = "Telephone2"

objExcelSheet.Cells(1,8) = "URL"

objExcelSheet.Cells(1,9) = "Email"

objExcelSheet.Cells(1,10) = "Address"

objExcelSheet.Cells(1,11) = "Town"

objExcelSheet.Cells(1,12) = "Postcode"

objExcelSheet.Cells(1,13) = "Heading"

ELSE 'DiallerFile

objExcelSheet.Cells(1,1) = "CustomerId"

objExcelSheet.Cells(1,2) = "Company Name"

objExcelSheet.Cells(1,3) = "Contact Name"

objExcelSheet.Cells(1,4) = "Telephone1"

objExcelSheet.Cells(1,5) = "Telephone2"

objExcelSheet.Cells(1,6) = "URL"

objExcelSheet.Cells(1,7) = "Email"

objExcelSheet.Cells(1,8) = "Address"

objExcelSheet.Cells(1,9) = "Town"

objExcelSheet.Cells(1,10) = "Postcode"

objExcelSheet.Cells(1,11) = "Heading"

END IF

'DATABASE STUFF

dim sServer, sConn, oConn, oRS

sServer = "svpts01"

sConn = "Provider= SQLOLEDB.1;Initial Catalog=CampaignManagement; Data Source=svpts01"

set oConn = CreateObject("ADODB.Connection")

oConn.Open sConn, "app_access", "snapsh0t"

set oRS = CreateObject("ADODB.Recordset")

' if not ActiveDocument.Variables("VarA").getcontent.string = "" Then

'msgbox ActiveDocument.Variables("VarA").getcontent.string

set campaignNotes1 = ActiveDocument.GetVariable("VarA")

set tb = ActiveDocument.GetSheetObject("TB01")

tb.AddField("ExternalId")

columnCount = tb.GetColumnCount

rowCount = tb.GetRowCount - 1

for currentRow = 1 to rowCount

'Does this customer have TPS?

tps = tb.GetCell(currentRow,17).Text

if tps = "Y" then

tpsDataIncluded = true

'msgbox ("Exported Cancelled - The data selected includes phone numbers that are TPS")

'exit For

end if

leadId1 = tb.GetCell(currentRow,1).Text

externalId = tb.GetCell(currentRow,columnCount-1).Text

businessName = tb.GetCell(currentRow,0).Text

title = tb.GetCell(currentRow,3).Text

fname = tb.GetCell(currentRow,4).Text

lname = tb.GetCell(currentRow,5).Text

postcode = tb.GetCell(currentRow,11).Text

companyEmail = tb.GetCell(currentRow,15).Text

otherEmail = tb.GetCell(currentRow,16).Text

telephone = tb.GetCell(currentRow,13).Text

'msgbox tb.GetCell(currentRow,14).Text

position1 = tb.GetCell(currentRow,14).Text

leadId = tb.GetCell(currentRow,1).Text

add1 = tb.GetCell(currentRow,6).Text

add2 = tb.GetCell(currentRow,7).Text

locality = tb.GetCell(currentRow,8).Text

town = tb.GetCell(currentRow,9).Text

county = tb.GetCell(currentRow,10).Text

heading = tb.GetCell(currentRow,19).Text

url = tb.GetCell(currentRow,36).Text

set oCmd = CreateObject("ADODB.Command")

with oCmd

.ActiveConnection = oConn

.CommandText = "InsertCampaignLeadData"

.CommandType = 4

'FOR REFERENCE

'3 = adInteger

'200 = adVarChar

'1 = adParamInput

.Parameters.Append .CreateParameter("@campaignName", 200 , 1, 100, campaignName1.getcontent.string)

.Parameters.Append .CreateParameter("@campaignNotes", 200, 1, 500, campaignNotes1.getcontent.string)

.Parameters.Append .CreateParameter("@leadId", 3, 1, 4, leadId1)

.Parameters.Append .CreateParameter("@id118", 3, 1, 4, externalId)

.Parameters.Append .CreateParameter("@businessName", 200, 1, 200, businessName)

.Parameters.Append .CreateParameter("@post_code", 200, 1, 50, postcode)

.Parameters.Append .CreateParameter("@telno", 200, 1, 50, telephone)

.Parameters.Append .CreateParameter("@company_email", 200, 1, 200, companyEmail)

.Parameters.Append .CreateParameter("@title", 200, 1, 50, title)

.Parameters.Append .CreateParameter("@fname", 200, 1, 200, fname)

.Parameters.Append .CreateParameter("@lname", 200, 1, 200, lname)

.Parameters.Append .CreateParameter("@position", 200, 1, 50, position1)

.Parameters.Append .CreateParameter("@sc_email", 200, 1, 500, otherEmail)

end with

oRS = oCmd.Execute

row = currentRow + 1

customerId = "LeadId_" & leadId

address = add1 & " " & add2 & " " & locality

email = ""

if otherEmail = "" then

if not companyEmail = "" then

email = companyEmail

else

email = ""

end if

else

email = otherEmail

end if

if not telno = "" then

telephone = Right(telno,Len(telno)-1)

telephone = Replace(telephone, " ", "", 1, -1)

end if

if typeOfFile = "ELocation" then

objExcelSheet.Cells(row,1) = customerId

objExcelSheet.Cells(row,2) = businessName

objExcelSheet.Cells(row,3) = title

objExcelSheet.Cells(row,4) = fname

objExcelSheet.Cells(row,5) = lname

objExcelSheet.Cells(row,6) = telephone

objExcelSheet.Cells(row,9) = email

objExcelSheet.Cells(row,10) = address

objExcelSheet.Cells(row,11) = town

objExcelSheet.Cells(row,12) = postcode

objExcelSheet.Cells(row,13) = heading

else

contactName = title & " " & fname & " " & lname

objExcelSheet.Cells(row,1) = customerId

objExcelSheet.Cells(row,2) = businessName

objExcelSheet.Cells(row,3) = contactName

objExcelSheet.Cells(row,4) = telephone

objExcelSheet.Cells(row,6) = url

objExcelSheet.Cells(row,7) = email

objExcelSheet.Cells(row,8) = address

objExcelSheet.Cells(row,9) = town

objExcelSheet.Cells(row,10) = postcode

objExcelSheet.Cells(row,11) = heading

end if

tb.RemoveField columnCount-1

CSVFilname = var & ".csv"

if typeOfFile = "ELocation" then

strFileName = "\\vsdat10\data\Farnborough\MIS\MIS\Vaishali\CM\" & CSVFilname

else

strFileName = "\\vsdat10\data\Farnborough\MIS\MIS\Vaishali\CM\" & CSVFilname

end if

XLDoc.SaveAs(strFilename)

XLDoc.Close()

XLApp.Quit

ActiveDocument.GetVariable("Filename").SetContent "", true

msgbox ("Data has been exported Successfully - Filename: " & strFileName)

msgbox "Finished"

End if

End Function

It shows the msgbox "Finished", but stays as executing on a big number of records like 65000..,but on like 20000 records it completes ok

Re: Macro stuck on Executing

marcus_sommer — Fri, 26 Sep 2014 11:32:42 GMT

I think your xls is by 65536 rows to end. Maybe you could split your output in smaller parts (additionally loop with some selections on your data).

- Marcus

Re: Macro stuck on Executing

Fri, 26 Sep 2014 11:46:10 GMT

I have tried with slightly more, the csv gets created ok, its the macro in qlikview that doesn't stop executing.

What do you mean by - additionally loop with some selections on your data)?

Re: Macro stuck on Executing

marcus_sommer — Fri, 26 Sep 2014 12:01:02 GMT

I meant to split the data to the first char/number from name/postcode or similar through selections or split GetRowCount into slices ....

- Marcus

topic Re: Macro stuck on Executing in QlikView

Macro stuck on Executing

Re: Macro stuck on Executing

Re: Macro stuck on Executing

Re: Re: Macro stuck on Executing

Re: Macro stuck on Executing

Re: Macro stuck on Executing

Re: Macro stuck on Executing