I wrote the code shown below to copy certain files to a list of directories.
The list is contained in the spreadsheet to which the code is attached. I
have access to all the files and directories used, but the program is telling
me "permission denied" on the copyfile statement. I am new to VB, so maybe
I have the syntax wrong or something. Any help would be appreciated.

The code:

Sub copyauxfiles()

Dim rnum
Dim destpath As String
Dim source As String
Dim basebook As Workbook
Dim basesheet As Worksheet
Dim rowsub As Integer
Dim nrows As Long
Dim ncol As Integer
Dim arraysub As Integer
Dim fs As Object

Set fs = CreateObject("Scripting.filesystemobject")
source = "k:\budget\master files\form D.xls"
Set basebook = ThisWorkbook
Set basesheet = basebook.Worksheets("PARAMETER LIST")

nrows = basesheet.Range("a1").End(xlDown).Row

For rowsub = 1 To (nrows - 1)
destpath = "J:\budget\" & basesheet.Cells(rowsub, 1).Value & "\blank forms"
fs.copyfile source, destpath, True
Next rowsub
End Sub

First, have you verified that the file path is OK when you get to the
copyfile line? Put a breakpoint there and check. (Since you are new to VB,
refer to help regarding the breakpoint if you need to, and also how to use
the locals window and immediate pane to test your variables' values).

If you are sure the file path is correct and you have the proper access
rights to the folder, then the likely cause is that the destination file name
already exists and the file is open when you are trying to overwrite it. If
it is open for use by another Windows would not let you replace it with a new
file and so would deny the permission to write over it. This would be true
even if you are the user who has the file open.
--
- K Dales


"SandyR" wrote:

I wrote the code shown below to copy certain files to a list of directories.
The list is contained in the spreadsheet to which the code is attached. I
have access to all the files and directories used, but the program is telling
me "permission denied" on the copyfile statement. I am new to VB, so maybe
I have the syntax wrong or something. Any help would be appreciated.

The code:

Sub copyauxfiles()

Dim rnum
Dim destpath As String
Dim source As String
Dim basebook As Workbook
Dim basesheet As Worksheet
Dim rowsub As Integer
Dim nrows As Long
Dim ncol As Integer
Dim arraysub As Integer
Dim fs As Object

Set fs = CreateObject("Scripting.filesystemobject")
source = "k:\budget\master files\form D.xls"
Set basebook = ThisWorkbook
Set basesheet = basebook.Worksheets("PARAMETER LIST")

nrows = basesheet.Range("a1").End(xlDown).Row

For rowsub = 1 To (nrows - 1)
destpath = "J:\budget\" & basesheet.Cells(rowsub, 1).Value & "\blank forms"
fs.copyfile source, destpath, True
Next rowsub
End Sub

Thanks - the problem actually was that I needed to have the file name, not
just the folder name in the destination path. When I added this it worked
perfectly. Thanks for your help.

"K Dales" wrote:

First, have you verified that the file path is OK when you get to the
copyfile line? Put a breakpoint there and check. (Since you are new to VB,
refer to help regarding the breakpoint if you need to, and also how to use
the locals window and immediate pane to test your variables' values).

If you are sure the file path is correct and you have the proper access
rights to the folder, then the likely cause is that the destination file name
already exists and the file is open when you are trying to overwrite it. If
it is open for use by another Windows would not let you replace it with a new
file and so would deny the permission to write over it. This would be true
even if you are the user who has the file open.
--
- K Dales


"SandyR" wrote:

I wrote the code shown below to copy certain files to a list of directories.
The list is contained in the spreadsheet to which the code is attached. I
have access to all the files and directories used, but the program is telling
me "permission denied" on the copyfile statement. I am new to VB, so maybe
I have the syntax wrong or something. Any help would be appreciated.

The code:

Sub copyauxfiles()

Dim rnum
Dim destpath As String
Dim source As String
Dim basebook As Workbook
Dim basesheet As Worksheet
Dim rowsub As Integer
Dim nrows As Long
Dim ncol As Integer
Dim arraysub As Integer
Dim fs As Object

Set fs = CreateObject("Scripting.filesystemobject")
source = "k:\budget\master files\form D.xls"
Set basebook = ThisWorkbook
Set basesheet = basebook.Worksheets("PARAMETER LIST")

nrows = basesheet.Range("a1").End(xlDown).Row

For rowsub = 1 To (nrows - 1)
destpath = "J:\budget\" & basesheet.Cells(rowsub, 1).Value & "\blank forms"
fs.copyfile source, destpath, True
Next rowsub
End Sub