Results 1 to 12 of 12
  1. #1
    tpcervelo is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    28

    Copy file to folder

    At the end of my procedure to upload an imported Excel file I want to copy/move the file to a different folder.
    I have tried the following but get a "Path/File Access Error" reported from MS Access. I have debug on and know that the 'move to' folder string is being loaded correctly. Both path names appear to be valid and correct.
    Any help is appreciated.



    strPathFile is "C:\Access2007\Shop Material Import\excelsheet.xlsx"

    strMovePath = Replace(strPathFile, "Shop Material Import", "Shop Material Uploaded")
    FileCopy strPathFile, strMovePath

  2. #2
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    did you create the folder "C:\Access2007\Shop Material uploaded"?
    or do you have privilege to create file in that folder?

    And, why don't just use

    strPathFile is "C:\Access2007\Shop Material Uploaded\excelsheet.xlsx"
    instead of
    strMovePath = Replace(strPathFile, "Shop Material Import", "Shop Material Uploaded")

    it's more directly.

  3. #3
    tpcervelo is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    28

    Copy File to Folder

    The target folder was already created.
    I cannot use a static excel file name as each import is different...the olny thing I want to do is move the file to a completed folder.

  4. #4
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    Quote Originally Posted by tpcervelo View Post
    The target folder was already created.
    I cannot use a static excel file name as each import is different...the olny thing I want to do is move the file to a completed folder.
    when you import from excel, the file has to be opened of course. are you trying a copy before the file is actually closed? if so, that is one issue.

    also, have you created the FileSystemObject that has the ability to move the files?

  5. #5
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    As Adam suggested with the FileSystemObject, to copy a file to a new folder, I use code as such:

    OldName = "C:\OldFolder\SomeExample.xls"
    NewName = "C:\NewFolder\SomeExample.xls"
    retval = 0
    Dim objFSO As Object
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    retval = objFSO.CopyFile(OldName, NewName, True)
    Set objFSO = Nothing
    Last edited by pkstormy; 08-28-2010 at 10:37 PM.

  6. #6
    tpcervelo is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    28

    Copy File to Folder

    I tried the code that pkstormy listed and get an error message of "Invalid Procedure Call or Argument". I checked that my from-to paths are valid and the receiveing folder is created.

  7. #7
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    Ok. Let me look into it. It worked fine for me.

  8. #8
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    tpcervelo,

    I just tried it again and it worked fine. Try creating a folder just called: OldFolder and another called NewFolder and put the excel file in the OldFolder. It might be something with the folder name you supplied. Make sure no spaces are in the name. Spaces are killers.

    (Note: if you must have spaces in the name, try using the Shop~1 to correct it. Sometimes this works, often not though. The UNC name should also work as well.)

  9. #9
    tpcervelo is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    28

    Copy File to Folder

    Thanks for your help pkstormy... the code does work but only if I hard code the path names which I cannot do because the import file will be different every time this is run. It has something to do with the string Replace function where I replace the incoming path with the New folder even though debug trace shows the path name to be correct.
    This works...
    OldName = "C:\Access2007\SFReady\50364_FRAME_002_8-6-10_A.xlsx"
    NewName = "C:\Access2007\SFComplete\50364_FRAME_002_8-6- 10_A.xlsx"
    retval = 0
    Dim objFSO As Object
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    retval = objFSO.CopyFile(OldName, NewName, True)
    Set objFSO = Nothing
    This doesn't...
    OldName = strPathFile
    NewName = Replace(OldName, "SFReady", "SFComplete")
    Debug shows both paths to be the same as if hardcoded but fails with the error.
    Anyway, thanks for your help...I will mark this as solved...

  10. #10
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    You may want to look at this example which lets you click a browse button to quickly select a folder. Ideally, I might put the files into a specific folder to try and automate things as much as possible. The link below also shows how you could import multiple excel files from a specified folder into the mdb.

    https://www.accessforums.net/sample-...ally-7536.html

  11. #11
    tpcervelo is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    28

    Copy File to Folder

    Ok...I will check it out...anything to make it more efficient.
    I did find the problem that was causing the error...newbie mistake.
    The variables oldfolder and newfolder were private and the routine was in another sub. I made them public and the move worked perfectly.
    thanks again.

  12. #12
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    Quote Originally Posted by tpcervelo View Post
    Ok...I will check it out...anything to make it more efficient.
    I did find the problem that was causing the error...newbie mistake.
    The variables oldfolder and newfolder were private and the routine was in another sub. I made them public and the move worked perfectly.
    thanks again.
    I might throw something like this as a function in a module, passing to the function the oldname and newname. That way it can be called from anywhere.

    example (in a module I call 'Moving a File'):

    Function MoveAFile(OldName as variant, NewName as variant)
    if isnull(OldName) or isnull(NewName) then
    msgbox "Old or New Folder/File name not supplied to function."
    exit function
    end if
    retval = 0
    Dim objFSO As Object
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    retval = objFSO.CopyFile(OldName, NewName, True)
    Set objFSO = Nothing
    msgbox "File has been moved."
    End Function

    and then call it like this:

    Call MoveAFile("c:\OldFolderName\OldFileName.xls","c:\N ewFolderName\NewFileName.xls")

    if I wanted to get fancy, I'd use the browse button (from the link supplied in the previous post) and make it so there are actually 2 folder name text type boxes on the form (along with 2 browse buttons and also possibly 2 text boxes for the filename, one for the old file name and one for the new file name.)

    Or I'd use the Common Dialog box (see code repository) and use the browse button to grab the actual old Folder 'and' File name itself and use a 2nd browse button to just insert what the new folder name is into a text box field on the form (creating a couple of string variables and populate these string variables with the values from the 2 textbox fields on the form and then pass these to the function.)

    Or if it was always the same old folder/new folder locations, just have 2 text boxes on the form, one for the old file name and one for the new file name and not even use a browse button.

    There are a few different ways to do it.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 05-02-2010, 05:09 PM
  2. Create Hash - Copy File to string
    By andrew_ww in forum Programming
    Replies: 0
    Last Post: 03-20-2010, 02:14 AM
  3. copy data from text file into table in access
    By miziri in forum Programming
    Replies: 3
    Last Post: 08-12-2009, 03:02 PM
  4. Displaying images from another folder
    By w3leon in forum Access
    Replies: 0
    Last Post: 01-30-2009, 06:18 PM
  5. Replies: 0
    Last Post: 09-18-2006, 08:37 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums