Loading CSV in to ram from network location is taking too long.

[raven_squire]

Hello,

I have had a worksheet working fine for 9 months. It normally takes 2 minutes to execute the macro in this worksheet, but for some reason in the last two months it started taking 6-8 minutes to execute.

I was able to isolate the code that was taking so long.

Open kmfile For Input As #1
StringKMFile = Input$(LOF(1), #1 )
Close #1

The CSV file "kmfile" lives on a network drive so that different users can access it. The file is 12mb. It took 10 seconds to copy the file to my desktop. I ran the macro using the file on my desktop and it took the regular 2 minutes to execute.

Is there any reason this process might have ballooned in execution time? Is there any way to increase the speed without first copying it to the users computer?
Thanks

 

[raven_squire]

Also there is a second CSV file that his macro loads into memory from the same network location. This file is only 1MB and does not appear to be slowing down execution.

 

[shg]

I don't know what happens if you try to open two files with the same file number. That's what FreeFile is for.

  Dim iFF           As Integer
  Dim kmFile        As String
  Dim sInp          As String

  ' ...

  iFF = FreeFile()

  Open kmFile For Input As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=iFF]#iFF[/URL] 
  sInp = Input(LOF(iFF), [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=iFF]#iFF[/URL] )
  Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=iFF]#iFF[/URL]