Problem using Name As to move files after reading CSV Data into a WorkSheet

**mcdirt** · 03-04-2013, 11:04 AM

Hi,

Just learning VBA and have pieced together this subroutine from various sources that did similar things but not quite what I wanted. Any help appreciated. The sub reads all *.csv files in a Folder and places the contents into a single Worksheet, one below the other. That bit works great.

Then I want to move each file that has been read to a new Directory so it still exists but will not be imported again when the macro is run next.

I cannot get the Name As section of the code to work. The Macro always stops with Run Time Error 75 - Path/File access error. I have tried simple Path & File names (as can be seen commented out in the code) and it still does not work. I tried using FileCopy and that works no problem but that doesn't move the files.

I'm using Excel 2010 on Win7 and when I copy files myself in Explorer, I have to "provide administrator permission" to move & delete files etc.
The csv files are exported from another program that is run "as Administrator".

Here is the code, many thanks for any help ...

Please Login or Register  to view this content.

rgds mcdirt

**AlphaFrog** · 03-04-2013, 11:32 AM

For a self-proclaimed noob, your code is well written. Good job.

Try putting a \ at the end of the new path.

NewPath = "C:\Users\Brian\"

**TMS** · 03-04-2013, 11:34 AM

Maybe:

Please Login or Register  to view this content.

Regards, TMS

**mcdirt** · 03-04-2013, 05:58 PM

Thanks for replies ...

AlphaFrog - I like clean code

sorry, the final \ in NewPath was there but got lost when fiddling just before copying the code here, routine still gets the error with correct NewPath.
TMShucks - with your code, I get the same error but now on the Kill statement.

Would prefer to get Name As working if I can as it seems safer - if I use Copy & Kill then I'll need to add error checking code to make sure the Kill only proceeds if the Copy works.

rgds mcdirt

**AlphaFrog** · 03-04-2013, 08:43 PM

Are you logged on Windows with Administrator privileges? If no, can you test it logged on as an Administrator?

**mcdirt** · 03-04-2013, 10:16 PM

Double checked User Accounts and I show as Administrator.

rgds mcdirt

**mcdirt** · 03-06-2013, 09:34 AM

Was something to do with Permissions where the source files were.
I moved them manually then tried it and it worked perfectly so I'll just write a batch file to move the files every hour.
Thanks for the help.