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

raven_squire

Board Regular
Joined
Jan 13, 2013
Messages
99
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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.
 
Upvote 0
I don't know what happens if you try to open two files with the same file number. That's what FreeFile is for.

Code:
  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]
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,316
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top