Runtime Error When Copying Files in Excel

I have a macro that copies 16 .xls files to a temp folder. There is one xlsm file in the main folder as well. The macro used to work but it copied the xlsm macro file. I put in the kill line to get rid of the file. Now I get "runtime error 70. Permission Denied. Here is where the code is breaking. The error occurs on the fso.CopyFile line.
***************************************************************
mywb = ActiveWorkbook.Name
strPath = ActiveWorkbook.Path
FileExt = "*.xls"

       
fso.CopyFile Source:=strPath & "\" & FileExt, Destination:=strPath & "\october\"
   
Kill strPath & "\quarterly\" & mywb
****************************************************************
 When I look in the October folder, all the files including the xlsm file have been copied to the folder but the error pops up and the routine will go no further. Experts, I need your help in resolving this issue. Many Thanks in Advance!
waynerayAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gowflowCommented:
can you post the whole code ? as it is not clear with these 2 lines we need to reproduce your problem to see where it is hanging.

By any chance in your fso copy operation do you happen to copy also the actual file that contain this macro ? in other words are you copying also the active workbook that is opened ? maybe your error comes from wanting to kill the file that is opened reason why you get access denied ? just a wild guess but more testing need to be done based on your code.

gowflow
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
waynerayAuthor Commented:
It is a pretty long sub routine. You will notice I select the file extension type to copy in my fso.filecopy portion of the code. However, it does copy the xlsm file that contains the macro. I commented out the kill line but it never makes it to that line. The error thows on the fso line. If I copy the files to the October folder and comment out the fso and kill lines, the rest of the code runs fine. Here is the code.

Sub fMonthlyTotals()

Dim cmonth As String
Dim newWb As Workbook
Dim months As Variant
Dim dstRangeName As String
Dim dstRange As Range
Dim dstCell As Range
Dim m  As Integer
Dim MyFolder As String
Dim MyFile As String
Dim strPath, mywb, empName As String
Dim cwb As Workbook
Dim x, n, j, k As Integer
Dim bCounter, lngRowCount As Long
Dim fso As Object
Set fso = VBA.CreateObject("Scripting.FileSystemObject")
Dim FileExt As String

With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
End With
lngRowCount = ActiveSheet.UsedRange.Rows.Count
mywb = ActiveWorkbook.Name
strPath = ActiveWorkbook.Path
FileExt = "*.xls"

       
fso.CopyFile Source:=strPath & "\" & FileExt, Destination:=strPath & "\october\"
   
Kill strPath & "\quarterly\" & mywb


Sheets("Monthly Rpt").Activate
ActiveSheet.PageSetup.LeftHeader = ""
ActiveSheet.PageSetup.CenterHeader = ""
ActiveSheet.PageSetup.RightHeader = ""

m = InputBox("Please input the number for the month of the report. (i.e. Jan = 1, Feb = 2, etc.")
months = Array("???", "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")
 

cmonth = "&B Monthly Chemical Report" & Chr(10) & months(m) & " Chemical Usage &B"

Cells(2, 1).Activate


strPath = ActiveWorkbook.Path & "\october" 'Path For temp files used to create the report
MyFile = Dir(strPath & "\*.xls")
bCounter = 0
Do While MyFile <> ""
    MyFile = Dir(strPath & "\*.xls")
    Workbooks.Open Filename:=strPath & "\" & MyFile
   
    Workbooks(mywb).Activate
    Cells(225, 1).Value = MyFile
    empName = Strip(Cells(225, 1), ".")
    Workbooks(MyFile).Activate
    Sheets(months(m)).Activate
   
        For j = 2 To lngRowCount
            If ActiveSheet.Cells(j, 6).Value <> 0 Then ' Look for quantities used during month.
                Range(Cells(j, 1), Cells(j, 6)).Select
                Selection.Copy
               
                Workbooks(mywb).Activate
                Sheets("Monthly Rpt").Activate ' Go to Report
                For k = 2 To 120
                    If Cells(k, 1).Value = "" Then ' Look for blank row on report to paste
                       
                        Range(Cells(k, 2), Cells(k, 5)).Select
                        ActiveSheet.Paste
                        Cells(k, 4).Value = Cells(k, 7).Value
                        Cells(k, 7).Value = 0
                        Cells(k, 1).Value = empName
                        Workbooks(MyFile).Activate
                        Exit For
                    End If
                   
                Next k
            End If
        Next j
    Workbooks(MyFile).Save
    Workbooks(MyFile).Close
    Kill strPath & "/" & MyFile
End Sub
0
gowflowCommented:
Welll

You have
Do While MyFile <> ""
...


missing Loop !!!!

you cut the sub somewhere ?

then what is this ?
Kill strPath & "/" & MyFile

it should be anyway
Kill strPath & "\" & MyFile

you get your error at
fso.CopyFile Source:=strPath & "\" & FileExt, Destination:=strPath & "\october\"

does it turn yellow and you get the error ? can you post a snalpshot ?
gowflow
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

gowflowCommented:
the instruction
fso.CopyFile Source:=strPath & "\" & FileExt, Destination:=strPath & "\october\"

works fine I just created a file and put it in a folder and put your macro in that file and added couple of other files

then ran step your macro till it came at the fso instruction then it hit an error Path not found which is normal as I did not create October directory. So I then created October in that folder and ran it again and it copied all 4 files that were there together with the macro file and then came to the next instruction which is
Kill strPath & "\quarterly\" & mywb

so obviously I did not run it as do not have anything in a folder called quarterly

so MY question to you is maybe you are getting this error as you are running the macro from within the quarterly folder where in fact you should run it from within your main directory.

OR

Maybe somewhere in your previous code you have the file that is in the quarterly folder already opened and not closed properly reason why you get this error also.

check these out.
gowflow
0
NorieAnalyst Assistant Commented:
Isn't the code trying to copy/kill the workbook the code is in as well as all the other workbooks in the same folder?
0
gowflowCommented:
seems like it as I pointed out in ID: 39579076. But willing to go all the way.
waiting for user's answer to my questions.
gowflow
0
waynerayAuthor Commented:
Thanks for all your help. I must confess the code did work when I first wrote it. It would copy the files to the proper folder and delete the xlsm file that copied. I am at a loss as to what I did to break it. I am running the macro in the xlsm workbook in the main folder. I apologize for the "quarterly" confusion. I changed the folder name I was copying to to October in desperation of fixing the problem. I did not change the next line which is the kill line. Kill strPath & "\quarterly\" & mywb should be Kill strPath & "\october\" & mywb. The purpose of this statement is to delete the Product Inventory.xlsm file that was copied to the folder.

The files do copy to October. You will notice in the code I posted and in the second attached jpg the kill statement is commented out and the error is still happening. In the fso.copy statement I tell it to only copy .xls files but it does copy the xlsm file. I did not change the code but it is brok and I do not understand why.

In short the code copies the files to a folder and then copies data into a monthly usage worksheet in the main file. All the code below the fso.copy statement still works properly. When I comment out the fso.copy statement and make sure the files are in the October folder, everything works.
Slide1.JPG
Slide2.JPG
0
gowflowCommented:
try replacing this

fso.CopyFile Source:=strPath & "\" & FileExt, Destination:=strPath & "\october\"

Open in new window


by this
fso.CopyFile Source:=strPath & "\" & FileExt, Destination:=strPath & "\october\",True

Open in new window


gowflow
0
gowflowCommented:
I read your comment again and noticed


Kill strPath & "\quarterly\" & mywb should be Kill strPath & "\october\" & mywb. The purpose of this statement is to delete the Product Inventory.xlsm file that was copied to the folder.

if your statement after fso should be this then it is:
Kill strPath & "\october\" & mywb

in other words it is trying to kill this very actual file that is running which is impossible and will generate this error access denied.

If I read your comment again what you want to do is this instruction
Kill strPath & "\october\Product Inventory.xlsm"

and  not
Kill strPath & "\october\" & mywb

Am I correct ???
gowflow
0
waynerayAuthor Commented:
I fixed the problem buy adding "On Error Resume Next" right before the fso statement. All files are copied and the copied xlsm file is deleted after copying. Thanks for your help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.