Solved

MS Access copy and rename file

Posted on 2008-11-03
6
2,523 Views
Last Modified: 2013-11-27
Hi

How can I copy and rename an existing file using Access vba?

Many thanks

Ken
0
Comment
Question by:kenabbott
  • 3
  • 3
6 Comments
 
LVL 3

Accepted Solution

by:
Yossarian-22 earned 500 total points
ID: 22867754
Use this:
FileCopy "c:\test\copythis.txt", "c:\new\target.txt"
0
 

Author Comment

by:kenabbott
ID: 22867927
That works a treat except if I try it on a Word document that I have created from a template via vba, I get runtime error 53
0
 
LVL 3

Expert Comment

by:Yossarian-22
ID: 22868015
Runtime error 53 means "file not found"

Try debugging and accessing the file from the variable you store the string of the file location in.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:kenabbott
ID: 22868381
I've checked this numerous times - the file names etc are correct.  It is something to do with initial creating the files in VBA.  I am using the code below.

If I create a file with this called abc.doc via the code below then FileCopy will not work (even after a reboot).  However if I create a file from scratch called abc.doc and save it in the same folder then its works!

Any ideas?




'creat object and populate bookmarks

        Set objWord = CreateObject("Word.Application")

        objWord.Documents.Add strTemplate

        objWord.Visible = False

    

        With objWord.ActiveDocument.Bookmarks

            .Item("Address").Range.Text = strAddress

            .Item("Date").Range.Text = Format(Date, "dd/mm/yy")

        End With

        

    'save and open

        

        objWord.ActiveDocument.SaveAs strFinalPath

Open in new window

0
 

Author Comment

by:kenabbott
ID: 22868556
UPDATE

I ahve now sorted this the problem is with the code - it is missing objWord.quit so it was leaving an instance of Word open and this seems to be causing the problem.  I also found an unrelated problem that was causing this still to fail after reboot

many thanks
0
 
LVL 3

Expert Comment

by:Yossarian-22
ID: 22868629
FileCopy cannot access files in use. Close the active document and set the object to Nothing before you attempt the FileCopy.
From within Word you may use the method mentioned here: http://word.mvps.org/FAQs/MacrosVBA/CopyOpenFile.htm


Otherwise try this method:


Dim objFso As Object

Set objFso = CreateObject("Scripting.FileSystemObject")
 

objFso.CopyFile source, destination, True
 

Set objFso = Nothing

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now