Macro stuck on Executing

[#1]

Hi

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?

[marcus_sommer]

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

- Marcus

[#3]

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.

[#4]

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

  next

  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

[marcus_sommer]

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

[#6]

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)?

[marcus_sommer]

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

- Marcus