ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   File cannot be accessed (https://www.excelbanter.com/excel-programming/452515-file-cannot-accessed.html)

[email protected] November 5th 16 01:35 PM

File cannot be accessed
 
Hi
I have a macro that sequentially opens data files (csv data in .rnd files), copies data to the main workbook, then closes the file. This normally works well but when the number of files reaches some arbitrary number, around 200, I get the message: Filename "cannot be accessed.The file may be corrupted, located on a server that is not working or read-only."
If I process in blocks of less than 150 the same file opens without problems. It would be good though to keep all 250 files together. Any ideas of how to avoid the issue?
Thanks,
Rod

Auric__ November 6th 16 03:31 PM

File cannot be accessed
 
"Rod" wrote:

I have a macro that sequentially opens data files (csv data in .rnd
files), copies data to the main workbook, then closes the file. This
normally works well but when the number of files reaches some arbitrary
number, around 200, I get the message: Filename "cannot be accessed.The
file may be corrupted, located on a server that is not working or
read-only."
If I process in blocks of less than 150 the same file opens without
problems. It would be good though to keep all 250 files together. Any
ideas of how to avoid the issue?


Post the code that's giving you trouble.

--
I'm not sure if you're serious, or trolling the troll.

piatzseo November 7th 16 04:08 AM

Quote:

Originally Posted by (Post 1626708)
Hi I have a macro that sequentially opens data files (csv data in .rnd files), copies data to the main workbook, then closes the file. This normally works well but when the number of files reaches some arbitrary number, around 200, I get the message: Filename "cannot be accessed.The file may be corrupted, located on a server that is not working or read-only." If I process in blocks of less than 150 the same file opens without problems. It would be good though to keep all 250 files together. Any ideas of how to avoid the issue? Thanks, Rod

B*i đăng sao nhiều link thế. đừng spam nha bạn

Robert Baer November 8th 16 04:01 AM

File cannot be accessed
 
wrote:
Hi
I have a macro that sequentially opens data files (csv data in .rnd files), copies data to the main workbook, then closes the file. This normally works well but when the number of files reaches some arbitrary number, around 200, I get the message: Filename "cannot be accessed.The file may be corrupted, located on a server that is not working or read-only."
If I process in blocks of less than 150 the same file opens without problems. It would be good though to keep all 250 files together. Any ideas of how to avoid the issue?
Thanks,
Rod

I think so.
The problem is made by having to many file handles, and the only way
that has a chance of preventing that is to close the files as soon as
possible: (open | read/copy | close) repeat for next file.


cachmangmuasam November 8th 16 03:53 PM

kêu Gấu Gam Mới chỗ phá vợ Nguyen đ*p về chị cả chồng đi Huyen Nguyen Tâm M*p Bùi nay đấy. gọi Đang Thu xong Jessica Gọi em Bui ăn gặp :v. trưa
Bảo Hiểm Daiichi life

[email protected] November 8th 16 04:38 PM

File cannot be accessed
 
Many thanks for this - I think you are right but cannot see a work around as I do shut the files as soon as I have copied the data I need.
I only ever have two files open at one time.
Regards,
Rod


[email protected] November 8th 16 04:41 PM

File cannot be accessed
 
On Sunday, November 6, 2016 at 3:32:00 PM UTC, Auric__ wrote:
"Rod" wrote:

I have a macro that sequentially opens data files (csv data in .rnd
files), copies data to the main workbook, then closes the file. This
normally works well but when the number of files reaches some arbitrary
number, around 200, I get the message: Filename "cannot be accessed.The
file may be corrupted, located on a server that is not working or
read-only."
If I process in blocks of less than 150 the same file opens without
problems. It would be good though to keep all 250 files together. Any
ideas of how to avoid the issue?


Post the code that's giving you trouble.

--
I'm not sure if you're serious, or trolling the troll.


Thanks for your reply. I am serious but I dont see the benefit of posting a large amount of code most of which is irrelevant. The bits that open and close the files are standard and work most of the time. However I suspect that a buffer is getting filled, or something. I just wondered if anyone had seen anything similar before.

GS[_6_] November 8th 16 05:58 PM

File cannot be accessed
 
Many thanks for this - I think you are right but cannot see a work
around as I do shut the files as soon as I have copied the data I
need. I only ever have two files open at one time. Regards,
Rod


If you use ADODB to pull your data you won't have to open any files.
Here's where to start...

http://www.appspro.com/conference/Da...rogramming.zip

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

[email protected] November 8th 16 07:01 PM

File cannot be accessed
 


If you use ADODB to pull your data you won't have to open any files.
Here's where to start...

http://www.appspro.com/conference/Da...rogramming.zip

--
Garry


Great idea! I'll look into it.
Many thanks,
Rod

[email protected][_2_] November 11th 16 11:02 AM

File cannot be accessed
 
Do you have proper error handling?
What could happen is that a file was opened but not closed as your code ended
prematurely due to an error.

RBS


On Tuesday, 8 November 2016 16:41:48 UTC, wrote:
On Sunday, November 6, 2016 at 3:32:00 PM UTC, Auric__ wrote:
"Rod" wrote:

I have a macro that sequentially opens data files (csv data in .rnd
files), copies data to the main workbook, then closes the file. This
normally works well but when the number of files reaches some arbitrary
number, around 200, I get the message: Filename "cannot be accessed.The
file may be corrupted, located on a server that is not working or
read-only."
If I process in blocks of less than 150 the same file opens without
problems. It would be good though to keep all 250 files together. Any
ideas of how to avoid the issue?


Post the code that's giving you trouble.

--
I'm not sure if you're serious, or trolling the troll.


Thanks for your reply. I am serious but I dont see the benefit of posting a large amount of code most of which is irrelevant. The bits that open and close the files are standard and work most of the time. However I suspect that a buffer is getting filled, or something. I just wondered if anyone had seen anything similar before.




All times are GMT +1. The time now is 12:06 AM.

Powered by vBulletin® Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
ExcelBanter.com