I have a macro that picks data from certain columns and writes that data into a CSV file, but its really really slow.
Taking nearly 1.5 hours to create a CSV with 64000 records.
I think the reason it is so slow is because I am using a for loop, but I don't know any other way, is there anyone who knows of a faster more efficient way of doing this?
set tb = ActiveDocument.GetSheetObject("TB01")
set XLApp = CreateObject("Excel.Application")
XLApp.Visible = False
set XLDoc = XLApp.Workbooks. Add
set objExcelSheet = XLDoc.Worksheets(1)
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
'Create CSV FILE
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 'Dialler
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
CSVFilname = var & ".csv"
if typeOfFile = "ELocation" then
strFileName = "\\vsdat10\data\Farnborough\MIS\MIS\Vaishali\CM\" & CSVFilname
else 'Dialler
strFileName = "\\vsdat10\data\Farnborough\MIS\MIS\Vaishali\CM\" & CSVFilname
if tpsDataIncluded = true then
msgbox ("Some data has been excluded from the export as it is TPS")
end if
end if
XLDoc.SaveAs(strFilename)
XLDoc.Close()
XLApp.Quit
