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

 

[Will from London]

Apologies for not being clear I was meaning that it does not have a « Long » data type. It has a « LongLong » data type that is the same as a « Long » in the 32bit world. Just using « Long » causes the 64bit version to fall over doesn’t it?...
...or so I thought...
...having just tested a simple example « Long » works in 64bit...so I must have misunderstood something so thanks for correcting my misunderstanding!

 

[Jack in the UK]

Some wordiness in x64 has changed, you should be good with Long changing to LongLong
The hold up will be (crash, lock up etc ) as VBA version can not traslate the code, like some X64 if put in X86 truns red and fails

if you have any issues in x64 from x86, test on a separate workbook the ideas/code at a very high level then you know and can edit tour main codes, keeps it independent and no messing with your work


Remember to back up of cause in case

Office Version	Office Bitnes	OS Bitness	VarType
2007 and Before (VBA6)	32	32	Long = (Signed 32 Bits)
2007 and Before (VBA6)	32	64	Long = (Signed 32 Bits)
2010 and Later (VBA7)	32	32	LongPtr = Long = (Signed 32 Bits)
2010 and Later (VBA7)	32	64	LongPtr = Long = (Signed 32 Bits)
2010 and Later (VBA7)	64	64	LongPtr = LongLong = (Signed 64 Bits)

jiuk

 

[Will from London]

Thanks for your replies.

I’ve identified the cause of my issue and that is that I was updating the StatusBar a couple of hundred thousand times during the run of the macro. Simply inserting a Mod function to update it every 1000 rows made the macro complete in a fraction of a second rather than fall over after a couple of hours. I unhelpfully didn’t mention the status bar in the opening post and everyone’s psychic powers must have been switched off, sorry!

However, that does give rise an interesting observation. Running the code separately for each of the 10 input files and restarting the PC between each file did change the status bar the same amount of times as it would have done if all 10 files could have been processed in turn. Does the StatusBar have some form of cache that gets clogged up? And that this is much worse on Excel 2019 / Windows 10 than on earlier configurations?