Results 1 to 10 of 10
  1. #1
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    150

    Copying Files from One Folder to Another

    I have routine that I'd like to copy all the file and paste to anther folder. Below is what I have so far. For testing purpose, I've left all the debug.print statement in. For this particular folder, I know there are two files in the folder I'm testing, one is a .pdf, and the other is a .txt. This routine seems to only grab the first file in the folder, which is the pdf. As it loops, it does not seem to recognize the .txt file as strFileName = "". I'd appreciate any help you can provide.
    Code:
            strFileName = Dir$(strCurrentPdfFolder & "*")        Do While strFileName <> ""
                Debug.Print "Current pdf folder: " & strCurrentPdfFolder
                strCurrentFolderAndFile = strCurrentPdfFolder & strFileName
                Debug.Print "Current Folder and File: " & strCurrentFolderAndFile
                strFutureFolderAndFile = strFuturePdfFolder & strFileName
                Debug.Print "Future folder and file name: " & strFutureFolderAndFile
                        Set fso = VBA.CreateObject("Scripting.FileSystemObject")
                        If Dir(strFutureFolderAndFile) <> "" Then ' Checking if future file name exists
                            Debug.Print strFutureFolderAndFile
                            Kill strFutureFolderAndFile 'If existing file name exists
                            Call fso.CopyFile(strCurrentFolderAndFile, strFutureFolderAndFile, 0)
                        Else 'No file name exists
                            Call fso.CopyFile(strCurrentFolderAndFile, strFutureFolderAndFile, 0)
                        End If
                strFileName = Dir$
            Loop


  2. #2
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 10 Access 2016
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    Have you tried
    Shell "xcopy FilePath DestinationPath"
    there are parameters to handle the logic for you,

    also xcopy is deprecated, you can likely use it but robocopy would give you even more logic to breakdown the file copy specifics.

  3. #3
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    150
    Quote Originally Posted by Perceptus View Post
    Have you tried
    Shell "xcopy FilePath DestinationPath"

    or of the similar?
    I don't have issue with the copying part. It's working. It's the looping through the files in the directory that I have trouble with. I'm not sure if the Dir () function or looping through the directory is working properly.

  4. #4
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 10 Access 2016
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    Xcopy and robocopy have the ability to check for new files and folders in locations and copy based on that.

    If you could post your full function, the one you did seems a bit malformated on the first line.

    Also I dont believe the DIR function returns a set of data to iterate through, if you wanted to do

    Code:
    x$ = dir(filepath)
    dim strsplit() as string: strsplit = split(x$,vbcrlf)
    dim intcounter as integer
    for incounter = lbound(strsplit) to ubound(strsplit)
       debug.print strsplit(intcounter) ' this is the first file matched
    next intCounter
    this wont work, as DIR only returns 1 file.

    you need to iterate through all the files in the file system object when it is looking at a specific folder.

    Similar to https://stackoverflow.com/questions/...lder-using-vba

    Sub LoopThroughFiles()
    Dim MyObj AsObject, MySource AsObject, file AsVariant
    Set MySource = MyObj.GetFolder("c:\testfolder")
    ForEach file In MySource.Files
    If InStr(file.name,"test")>0Then
    MsgBox
    "found"
    ExitSub
    EndIf
    Next file
    EndSub

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Ron le bruin has a number of functions you might find useful including one to copy all files in a folder
    http://www.rondebruin.nl/win/s3/win026.htm

    Much easier than looping through one file at a time
    Although the site is mainly for Excel this section also applies to Access.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    150
    Quote Originally Posted by Perceptus View Post
    Xcopy and robocopy have the ability to check for new files and folders in locations and copy based on that.

    If you could post your full function, the one you did seems a bit malformated on the first line.

    Also I dont believe the DIR function returns a set of data to iterate through, if you wanted to do

    Code:
    x$ = dir(filepath)
    dim strsplit() as string: strsplit = split(x$,vbcrlf)
    dim intcounter as integer
    for incounter = lbound(strsplit) to ubound(strsplit)
       debug.print strsplit(intcounter) ' this is the first file matched
    next intCounter
    this wont work, as DIR only returns 1 file.

    you need to iterate through all the files in the file system object when it is looking at a specific folder.

    Similar to https://stackoverflow.com/questions/...lder-using-vba

    Sub LoopThroughFiles()
    Dim MyObj AsObject, MySource AsObject, file AsVariant
    Set MySource = MyObj.GetFolder("c:\testfolder")
    ForEach file In MySource.Files
    If InStr(file.name,"test")>0Then
    MsgBox
    "found"
    ExitSub
    EndIf
    Next file
    EndSub
    Code:
    Do While strFileName <> ""
    of the first line above is actually the second line. It somehow got wrapped to the first line when I copied and pasted.

    I'm not the level of a coder as most, but if I understand it correctly, it would appear that strsplit() is an arrary? When I step through the codes, lbound(strsplit) and ubound(strsplit) both return 0. Therefore, even though I know there are two files in the folder (filepath), it's going through the For loop only once.

  7. #7
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    150
    Quote Originally Posted by ridders52 View Post
    Ron le bruin has a number of functions you might find useful including one to copy all files in a folder
    http://www.rondebruin.nl/win/s3/win026.htm

    Much easier than looping through one file at a time
    Although the site is mainly for Excel this section also applies to Access.
    You are absolutely right, that link you provided has much simpler codes. They seem to work beautifully. Thank you so much.

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You're welcome.
    Should be much faster than looping through a recordset as well
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  9. #9
    apr pillai's Avatar
    apr pillai is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2010
    Location
    Alappuzha, India
    Posts
    209
    Here is an example for Dir() command and it's usage to read all the file names one by one:
    Code:
    Public Function TestDir(ByVal strFolder As String) As String
    'Function Usage e.g.: TestDir "D:\Documents\"
    Dim j As Integer, strFile As String
        j = 1
        strFile = Dir(strFolder, vbHidden)
    Do While Len(strFile) > 0
          Debug.Print j & ":" & strFile
          j = j + 1
          strFile = Dir()
    Loop
    End Function
    Call the function from Immediate Window with a Folder Path (e.g. "D:\Documents") to try it out.

    1. In the above example first time the Dir(strFolder) Command is executed with a Folder Path as parameter. Returns the first filename encountered in the folder.

    2. Second step onwards the Dir() Command is run without any parameter and gets the next file in the folder. When this step runs in a loop every time it gets the next file. The loop ends when no more file in the folder.

  10. #10
    apr pillai's Avatar
    apr pillai is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2010
    Location
    Alappuzha, India
    Posts
    209
    Here is an example for Dir() command and it's usage to read all the file names one by one:
    Code:
    Public Function TestDir(ByVal strFolder As String) As String
    'Function Usage e.g.: TestDir "D:\Documents\"
    Dim j As Integer, strFile As String
        j = 1
        strFile = Dir(strFolder, vbHidden)
    Do While Len(strFile) > 0
          Debug.Print j & ":" & strFile
          j = j + 1
          strFile = Dir()
    Loop
    End Function
    Call the function from Immediate Window with a Folder Path (e.g. "D:\Documents") to try it out.

    1. In the above example first time the Dir(strFolder) Command is executed with a Folder Path as parameter. Returns the first filename encountered in the folder.

    2. Second step onwards the Dir() Command is run without any parameter and gets the next file in the folder. When this step runs in a loop every time it gets the next file. The loop ends when no more file in the folder.

    You may use this command with an empty string to get the first file from Current Folder: Dir(""), and for subsequent runs as explained above.

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

Similar Threads

  1. Copying files from outside sources
    By Lou_Reed in forum Access
    Replies: 31
    Last Post: 12-05-2016, 08:06 AM
  2. Replies: 10
    Last Post: 09-09-2015, 03:25 AM
  3. Replies: 1
    Last Post: 05-15-2015, 10:58 AM
  4. Replies: 3
    Last Post: 09-02-2014, 01:06 AM
  5. Copying multiple files?
    By daveofgv in forum Programming
    Replies: 3
    Last Post: 04-14-2011, 03:18 PM

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