Renaming a local file and passing field contents to name

[jimbo62]

I have a problem an would like to see if anyone can help me get this to work. I have spent a great deal of time already trying. Here is the problem. I need to copy a template file to the proper directory and rename it so I can append the worksheets to thae name selected. Here is my code. Could anyone point me in the right direction?

Thanks,

Jim

Function SaveFileCMRCBuild()
'I was trying to use the name VBA command
Name NUTRO_CMRC_Template.XLS As "CMRC_" & Forms!ConvertCMRC!sMonth & Forms!ConvertCMRC!sDay & Forms!ConvertCMRC!sYear & "_To_" & Forms!ConvertCMRC!eMonth & Forms!ConvertCMRC!eDay & Forms!ConvertCMRC!eYear & ".XLS"
'
'it gives me a object required error
'
'
MsgBox "Is your Date Stamp Set for the Nutro Credit Format file?....!", , "CMRC File Conversion"
'
'This is where I send the spreadsheet to the file named 'from the same form
'
DoCmd.TransferSpreadsheet acExport, 8, "CMRC RC Total", "c:\NutroCreditBuilds\CMRC_" & Forms!ConvertCMRC!sMonth & Forms!ConvertCMRC!sDay & Forms!ConvertCMRC!sYear & "_To_" & Forms!ConvertCMRC!eMonth & Forms!ConvertCMRC!eDay & Forms!ConvertCMRC!eYear & ".XLS", True, ""
'.....
'..There are many more after this line


DoCmd.OpenForm "progress"
End Function

Jimbo

 

[Remou]

How about:

strOldName="NUTRO_CMRC_Template.XLS"
strNewName="CMRC_" & Forms!ConvertCMRC!sMonth & Forms!ConvertCMRC!sDay & Forms!ConvertCMRC!sYear & "_To_" & Forms!ConvertCMRC!eMonth & Forms!ConvertCMRC!eDay & Forms!ConvertCMRC!eYear & ".XLS"

Name strOldName As strNewName

You can the re-use strNewName, rather than that chunk of stuff. As an aside, why not a date field? It makes life easier in that you can use Format.