Forum Discussion
Permission Denied error when moving a file
Hi all, I'm using Excel 365 MSO (Version 2401 Build 16.0.17231.20236) 64-bit. I have a spreadsheet (let's call it 'Main') that contains a bunch of VBA to do the following. It first imports da...
Hi Jan,
Thanks for responding.
fso is the Scripting.FileSystemObject, so it's pretty simple.
fso.MoveFile(strFilespec, strNewFilespec)
The source file has already been closed by the time the MoveFile() method is called. In fact, the procedure that opened and closed the source file has already gone out of scope.
Thanks for responding.
fso is the Scripting.FileSystemObject, so it's pretty simple.
fso.MoveFile(strFilespec, strNewFilespec)
The source file has already been closed by the time the MoveFile() method is called. In fact, the procedure that opened and closed the source file has already gone out of scope.
gseach Of course, I know the fso library. But what other stuff is done before and after the statement might be important. Anyway, is there anything that might refer to the file that fails to move? Like a data connection?
- Hi Jan,
This is the fourth of four files that are processed one after the other. All four files are processed in the same way, by the same code (a procedure called EnumeratePackslips). The procedure that contains the MoveFile() method (called ExtractData2Upload) is called as the last call by EnumeratePackslips() and only touches the Access backend - it does not touch any spreadsheet, and it's not called until all the source spreadsheets have been closed.
My guess is that Excel still thinks the last source spreadsheet is still open. That's the only thing I can think of to return a Permission Denied error. It doesn't appear to be a timing issue either; I tested that by using DoEvents and by calling the Sleep API for longer and longer periods.
Is there something in the way I'm calling and closing the source spreadsheets?- You are using Powerquery to pull in the data to Excel, that means .NET is involved. perhaps a garbage collection issue? Is the workbook closed which contains the queries, at the point when you are trying to move the file?
Thanks for all your help, JKPieterse.
I found some code on the web that did what I wanted without incurring any errors later on.
'Open the packfile and import its data Set wb = Workbooks.Open(fil.Path) Set sht = wb.Sheets(1) sht.Copy After:=ThisWorkbook.Sheets("Settings") ThisWorkbook.Sheets(3).Name = "DataImport" wb.Close False