moving a file

[Dwight3238]

I'm using VBA to open a new document from a template that i pick from a list of templates in a directory. i open only one document from it and then i need to move this template to an archive folder. That way it won't appear in the list of templates the next time i use this project.

I have tried the FileCopy and Kill and Name features and always get errors. mostly regarding access or permissions. but that shouldn't be an issue because i have confirmed the paths and the template was never opened so it couldn't be locked.

Any assistance with this would be greatly appreaciated.

 

[michaelbr55]

Hi Dwight3238,
If you use a template to create a document the template is in use ie it can't be moved. You'll have to come up with some other way to mark the template as achived. How does the user choose the template to use, do you have a list/combo box on a form or what?

Mike

 

[Dwight3238]

Thanks Mike

the user selects from a combo box on a form. but while replying i thought that i could move the file prior to opening the document then just open from the archive directory.

Thanks again.

 

[Bowers74]

Try the following procedure, it will do what you want, but you will need to modify the file and path varibles. It even has an Error Handler:

Sub MoveThenOpenFile()
Dim file As String, fPath As String, newfPath As String
file = "temp.xls" ' Change this
fPath = "C:\" & file ' Change this
newfPath = "E:\" ' Change this
Dim filesys
Set filesys = CreateObject("Scripting.FileSystemObject")
On Error GoTo ErrHandler
filesys.MoveFile fPath, newfPath
ErrHandler:
If Err.Number <> 0 Then
    Select Case Err.Number
        Case Is = 58
            MsgBox newfPath & file & &quot; already exists!&quot;, _
                vbExclamation, &quot;Error &quot; & Err.Number & &quot; - File Exists&quot;
        Case Is = 53
            MsgBox fPath & &quot; does not exist!&quot;, _
                vbExclamation, &quot;Error &quot; & Err.Number & &quot; - File not Found&quot;
        Case Else
            MsgBox &quot;An error has occured!&quot;, vbExclamation, Err.Number & &quot; &quot; & Err.Description
    Exit Sub
    End Select
End If
Workbooks.Open newfPath & file
End Sub

I hope this helps!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Bowers74]

Dwight,

Check out a new FAQ I created (inspired by this thread). It contains a better procedure to move a file.

faq707-4116

Enjoy!

Peace!

Mike

Never say Never!!!
Nothing is impossible!!!