Problems with FileCopy and Wb(name).close

[RonMcK]

Hi, All !

I'm having some challenges with FileCopy in the following code fragment where I attempt to copy a template file to use as the current working instance. When I hit the FileCopy line, the program reports an error 53:
'// Close files if they are open; ignore errors on close if not open
On Error Resume Next
Workbooks(TargetFull).Close SaveChanges:=False
Workbooks(TemplateFull).Close SaveChanges:=False

'// Turn error checking back on
On Error GoTo 0
'// Copy the template as new target file
ChDir FilePath
FileCopy TemplateFull, TargetFull '// includes filepaths ' either line causes the error #53, File not found
' FileCopy TemplateFile, TargetFile '// just bare filenames

A bit later in my program, I've built the name of a worker file, before I do anything, I attempt to close it; this time VBA reports an runtime error #9, Subscript out of range. Why doesn't the 'on error resume next' cause the code to drop through to the following line instead of stopping at an error box with msg?
Do While WorkerNum <= NumWorkers
SourceFile = Workers(WorkerNum) & " " & DateText & ".xls"
On Error Resume Next
Workbooks(FilePath & PathSep & SourceFile).Close SaveChanges:=False ' error 9 at this point - rjm

'// Skip to next worker if file not found
On Error GoTo GetNextWorker
Workbooks.Open Filename:=SourceFile
Workbooks(SourceFile).Activate
On Error GoTo 0
Suggestions? Any and all help will be appreciated.

Thanks,

[mdmackillop]

Why are you copying a template? Should you not use
Workbooks.Add Template:= "C:\Templates\Test.xlt"

[mdmackillop]

Re Part 2, I can't replicate your problem. It "works" if I step through it

[RonMcK]

Why are you copying a template? Should you not use
Workbooks.Add Template:= "C:\Templates\Test.xlt"

Doh! (self-administered 1 each dope slap) So, I tried:
FilePath = "C:\Documents and Settings\Ron\My Documents\My Work"
TemplateShort = "Complied-Template"
TemplateFile = TemplateShort & " " & Format(Super, "0") & ".xlt"
TemplateFull = FilePath & PathSep & TemplateFile
Workbooks.Add Template:=TemplateFull
Hmm? Now, I'm getting run-time error '1004': 'Complied-Template 1.xlt' could not be found. Check the spelling of the file name, and verify that the file location is correct.'

Yup, that's the file name. File location is:
C:\Documents and Settings\Ron\My Documents\My Work
TemplateFull='C:\Documents and Settings\Ron\My Documents\My Work\Complied-Template 1.xlt'
(copied from Immediate window).

New Query:
The template name is not the final name for the file. Is there a 'rename' function? I'm not finding one. But looking in Object Browser I see that Name class has a member called Value. Can I so something like Workbooks("name").Name.Value="new-name" to change it?

I'm still puzzled.

Thanks,

[mdmackillop]

Sub Macro3()
Dim WB As Workbook
Set WB = Workbooks.Add(Template:= _
"C:\Users\Malcolm\AppData\Roaming\Microsoft\Templates\Test.xlt")
WB.SaveAs "C:\AAA\" & InputBox("New file name") & ".xls"

End Sub

[RonMcK]

Malcolm,

Thank you very much. I'll put that all to work when I get back in a couple of hours.

Cheers!

[mdmackillop]

Have a look at this thread (http://www.vbaexpress.com/forum/showthread.php?t=20995) as well.

[RonMcK]

Malcolm, et al,

I'm having some challenges that are baffling me. You help, again, will be appreciated. From any and every one, not just Malc.

My program processes a template files (.xlt), first creating today's working copy as an .xls, and then iterating through the list of workers (the tabs in the working file), looking for a daily file for each worker. If no file exists with the day's date for a worker, the code should skip that worker and look for the next one in the list (loaded into an array for ease of processing).

The template has 6 workers (Worker1, Worker2, ... Worker6 for our purposes). I created daily files for Workers 1 and 2. When I run my code (below) the program fails to skip Worker3; it reports run-time error #9 - subscript out of range at the point noted in the code fragment, below.

Do I need iterate through the open workbooks, getting their names, determining that SourceFile is open, and grabbing its index number, all before I attempt t close to close SourceFile? And, attempting to close SourceFile if and only if there is an instance of SourceFile open?
WorkerNum = 1
'// Loop through the workers; stopping when WorkerNum exceeds NumWorkers
Do While WorkerNum <= NumWorkers
'// Build name of Worker's Tracker file
SourceFile = Workers(WorkerNum) & " " & DateText & ".xls"
Debug.Print SourceFile
'// Make sure the SourceFile is not open

' Workbooks(FilePath & PathSep & SourceFile).Close SaveChanges:=False
Debug.Print FilePath & PathSep & SourceFile
On Error Resume Next
Workbooks(SourceFile).Close SaveChanges:=False
'// Now, open the SourceFile for the worker
'// Skip to next worker if file not found
On Error GoTo GetNextWorker
Workbooks.Open Filename:=SourceFile
On Error GoTo 0
'// Make SourceFile the ActiveWorkBook
Workbooks(SourceFile).Activate

'// Find index of 'SrcSheet' in Worker's workbook
mySheetNum = 0
NumSheets = ActiveWorkbook.Sheets.Count
For SheetNum = 1 To NumSheets
Debug.Print Worksheets(SheetNum).Name, NumSheets, SrcSheet
'// stop iterating when SrcSheet is found
If Worksheets(SheetNum).Name = SrcSheet Then
mySheetNum = SheetNum
GoTo CopyRangeofCells
End If
Next
CopyRangeofCells:
'// quit process for file if no Compiled ws.
If mySheetNum = 0 Then GoTo GetNextWorker

'// Copy designated range from source wb to ws in target wb
ActiveWorkbook.Worksheets(mySheetNum).Range("B5:K57").Copy _
Workbooks(TargetFile).Worksheets(Workers(WorkerNum)).Range("B5")

GetNextWorker:
'// Done with worker file so close it; there s/b no changes, we only read.
On Error Resume Next
Workbooks(SourceFile).Close SaveChanges:=False ' ERROR #9 Subscript out of range
'// Restart error trapping
On Error GoTo 0
'// Set up to get next worker
WorkerNum = WorkerNum + 1
Loop

Many thanks in advance for any and all help.

[mdmackillop]

You can check the existence of a file using the Dir command

If Len(Dir(SourceFile))>0 then
Workbooks.Open Filename:=SourceFile
'etc.

[RonMcK]

Malcolm,

Thank you very much.

[RonMcK]

Malcolm, et al,

I'm getting a run-time error #9 (subscript out of range) two places (marked in red) of the following code.
Debug.Print ">" & mySheetNum & "<", Workers(WorkerNum), TargetFull
Debug.Print Workbooks(TargetFull).Name
Debug.Print ActiveWorkbook.Name
'// Copy designated range from source wb to ws in target wb
ActiveWorkbook.Worksheets(mySheetNum).Range("B5:K57").Copy _
Workbooks(TargetFull).Worksheets(Workers(WorkerNum)).Range("B5")
Originally, the error occurred at the 2nd instance; it moved up to the 1st after I added the debug code. Is it looking for an index for the target workbook and/or worksheet, instead of the names?

Thanks,

Your perplexed learner,

[RonMcK]

Answer apparently is Yes. Substituting index numbers for names cleared up the problem.