Tab delimited text file to Excel - how to speed up code

[Rudolf Bargholz]

I have a tab delimited text file (iso-8859-1 encoding) that I need to copy into an Excel file, from the second row, starting at the cell "A2". The QM code I have written, with the help of the QM halp and the forum, works, but it is a bit slow. Does anyone have an idea how I can speed up the code?

Macro Macro3
 

Code:

Copy      Help

str s

SYSTEMTIME st
GetLocalTime &st
out s.format("%02i:%02i:%02i.%03i" st.wHour st.wMinute st.wSecond st.wMilliseconds)

ICsv v._create
v.Separator="[9]"
;v.FromFile("C:\Users\rb\AppData\Local\Temp\olt\tmp\167014016675436\export.txt")
;v.ToString(s)
;s.ConvertEncoding("iso-8859-1" _unicode)

str fs = "Arrecife Gran Hotel & Spa *****[9]inklusive Halbpension[9]half board[9][9][9]2[9]01.11.2023[9]0[9]2[9]Doppelzimmer Meersicht"
fs + "[]Arrecife Gran Hotel & Spa *****[9]inklusive Halbpension[9]half board[9][9][9]2[9]02.11.2023[9]0[9]2[9]Doppelzimmer Meersicht"
fs + "[]Arrecife Gran Hotel & Spa *****[9]inklusive Halbpension[9]half board[9][9][9]2[9]03.11.2023[9]0[9]2[9]Doppelzimmer Meersicht"
v.FromString(fs)

int nr=v.RowCount
int nc=v.ColumnCount
int r c

;start Excel and create worksheet
Excel.Application xlApp._create
Excel.Worksheet xlSheet=xlApp.Workbooks.Add(Excel.xlWBATWorksheet).ActiveSheet ;;add workbook and get worksheet

;get cells
Excel.Range allcells=xlSheet.Cells

for r 0 nr ;;for each row
,for c 0 nc ;;for each column
,,s=v.Cell(r c)
,,Excel.Range cell=+allcells.Item(r+2 c+1)
,,cell.Value=s.ConvertEncoding("iso-8859-1" _unicode)
,,;cell.Value=s

;make visible
xlApp.Visible = 1
xlApp.UserControl=1

GetLocalTime &st
out s.format("%02i:%02i:%02i.%03i" st.wHour st.wMinute st.wSecond st.wMilliseconds)

The current list will have 3500 to 5000 rows, and this list has 11 columns, but the idea is to make a macro that can take an existing Excel file, make a copy, and then paste any list from a tab delimited text file into a specific worksheet and a specific cell of the copied Excel. In this way I can retain the formatting of the original 

Perhaps there is something I can do here to optimize the code? In this case the macro above, for 4500 rows and 11 columns, takes 30 seconds to complete, and my PC is no slouch. This is a lot slower than I was expecting, but perhaps this is just an performance issue with the COM interface provided by Excel. My hope is that there is something I can to to speed up the processing.

Regards

Rudolf Bargholz