Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS Access copy and rename file

Posted on 2008-11-03
6
Medium Priority
?
3,810 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 3

Accepted Solution

by:
Yossarian-22 earned 2000 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

688 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