A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Programming
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

File cannot be accessed



 
 
Thread Tools Display Modes
  #1  
Old November 5th 16, 02:35 PM posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5
Default 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
Ads
  #2  
Old November 6th 16, 04:31 PM posted to microsoft.public.excel.programming
Auric__
external usenet poster
 
Posts: 452
Default 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.
  #3  
Old November 7th 16, 05:08 AM
piatzseo piatzseo is offline
Junior Member
 
First recorded activity by ExcelBanter: Nov 2016
Posts: 3
Default

Quote:
Originally Posted by View Post
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
  #5  
Old November 8th 16, 04:53 PM
cachmangmuasam cachmangmuasam is offline
Junior Member
 
First recorded activity by ExcelBanter: Nov 2016
Posts: 10
Default

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
  #6  
Old November 8th 16, 05:38 PM posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5
Default 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

  #7  
Old November 8th 16, 05:41 PM posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5
Default 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.
  #8  
Old November 8th 16, 06:58 PM posted to microsoft.public.excel.programming
GS[_6_]
external usenet poster
 
Posts: 628
Default 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
  #9  
Old November 8th 16, 08:01 PM posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5
Default 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
  #10  
Old November 11th 16, 12:02 PM posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 38
Default 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.


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
file could not be accessed - reference xlextras.xla Steve Excel Discussion (Misc queries) 2 November 3rd 08 05:17 PM
file.xls cannot be accessed...Read only... OA Excel Discussion (Misc queries) 7 February 26th 07 08:53 PM
File cannot be accessed KMc Excel Discussion (Misc queries) 2 July 20th 06 03:56 PM
file cannot be accessed, could be read only somnath shetty Excel Discussion (Misc queries) 2 April 10th 06 03:30 PM
"file" cannot be accessed FJB Excel Discussion (Misc queries) 4 May 18th 05 03:45 AM


All times are GMT +1. The time now is 12:33 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.