Excel 2019 VBA (Windows 10) text file processing slower than older versions of Excel / Windows

[Will from London]

Hi

On an Excel worksheet I have a 10 row by 2 column table which has « copy from path and filename» in column A and « copy to path and filename» in column B. The files in column A exist but the code creates the files listed in column B. The macro works down the list of files, (tab delimited .txt files) reads in each row of the text file, performs a small amount of manipulation and then writes each line to the new file as a comma delimited .txt file.

The substantive parts of the code are (I can’t paste the whole code as I’d be breaching my firm’s IT rules) and speechmarks show up as « « on this keyboard / language layout):

Dim tempStr as String

Open « file from column A » For Input as #1
Open « file from column B » For Output as #2
Do Until EOF(1) = True
Line Input #1, tempStr
...use tempStr = Replace(tempStr, « X », « Y ») Three times for different « X » and « Y »
Print #2, tempStr
Loop
Close #1
Close #2

The text files are up to 70,000 rows in length with no more than about 200 characters per row.

On my old PC (Windows 7, 32bit Excel 2013) it took about 5 minutes to do the whole thing but with the new PC it cannot even complete the list. I have found that if I just process one file at a time and restart the PC between each file then it can process all of them but that does not seem to be efficient. Is there something that gets « clogged up » in the way of RAM or other cache that I can clear between files that will mean that I won’t need to restart the PC so often?

My PC is a 64bit Windows 10 (Enterprise LTSC) (Intel i7-8700T 2.40GHz) running Excel (Microsoft Office Professional Plus 2019) with 16GB of RAM.

Any help gratefully received.

Will

 

[Jack in the UK]

Are you running x64 version of 2019 on the spine of x64 Win 10, if so that coupld be it (untested or tried)

Back in the day x64 of 2016 was a pain to use and codes always played up, x86 2016 solved that easy on Win 7 and 8 and now as late of 10

If was me I would always go x86 for Office no matter the base OS

Never user 2019, thou its 365 targeted, not sure what your get for office on line x64 or x86 thou. If your running local look for x86 version and see if tat help

jiuk

 

[Will from London]

Thanks for your reply.

I’m using x64. For most of the things I use Excel for the 64 bit version is much better than the 32bit x86. For a start it doesn’t have the 2GB RAM limit. I needed to make a couple of minor modifications to some macros in my personal workbooks (simply changing « Sub personalMacro » to « Sub personalMacro(dummyObject as object) » for example), but the code I’m having an issue with is not in the personal macro workbook. The code works...but only if I break the process into chunks and restart the PC in each « loop ».

Regards

Will

 

[Jack in the UK]

The only thing to try is to break the procedue up in to chucks and clear out all memory holds, veriables etc as required adn the end of that chunk; then wait few moments before launcing the second, thrid procedure

Your right I did think this code was housed inteh XLSb, Could be work running the code and having Task Manager/processes open to see if there is a spike or high yeald

jiuk

 

[Will from London]

I am using « Option Explicit »

The button in the workbook has a small piece of code attached that is:

Sub createInputsA()
Dim resultStr as String
ResultStr = createInputsB(« name of range containing 10 row by 2 column list of tables »)
If resultStr <> « OK » then msgbox resultStr, vbCritical
End Sub

With the Function createInputsB beginning
Function createInputs(range as string) as string
And it only uses:
4 Integer variables
1 Integer constant
1 LongPtr variable
1 Boolean variables
4 String variables
And at no point are arrays used.

Could it be the String variables occasionally need to be emptied? The others are so small.

It does use the Application.StatusBar = filename & value of LongPtr variable as it loops through each line but that seems unlikely to be the problem.

 

[RoryA]

Why do you need a LongPtr? Are you using API calls?

 

[Will from London]

It was just there as not all of my team’s PCs were upgraded to Office 64 and it needed an integer greater than 32,768. My understanding is that LongPtr becomes a Long on a 32bit version of Excel VBA and LongLong in the 64bit version (as the 64 bit version does not have a Long type).

 

[Jack in the UK]

In practice if you have a office with mixed x64 and x86 versions I would have two procedures not try to make on fit the task

Your need to test the base bit version of Excel and call the required code

Make a function from: call your codes as required

Dim bIs64Bit As Boolean
#If Win64 Then
bIs64Bit = True
#End If[code]

jiuk

 

[Will from London]

Thanks for your thoughts, RoryA and Jack.

My team is all on x64 now so I’ll run some tests with it simply as a LongLong next week and see if that helps the issue.

Do String types get clogged up in anyway though? The code doesn’t do StringA = StringA & StringB anywhere it always loads a single line of the text file in at a time, does stuff and then reads the next one in using « Line Input » as described above.

 

[RoryA]

(as the 64 bit version does not have a Long type).

64bit does have a Long data type. You only need LongPtr for things like window handles/memory pointers when using API calls.