Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Ellpee is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    May 2013
    Posts
    80

    FileCopy Question

    Trying to do something that seems simple: use VBA to copy a file and save it under a different filename.



    SourceFile = "M:\TRADES\bldtrd_MFTRADE.ini"
    DestinationFile = "M:\TRADES\BLDTRD" & me.ID_TRADE & ".ini"
    FileCopy SourceFile, DestinationFile

    Getting an error on second line, however, "object doesn't exist" or words to that effect. This seems pretty straightforward, what am I missing? (me.ID_TRADE, BTW, is a serial number, currently five digits.)

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Shot in the dark without more info, but try:

    DestinationFile = "M:\TRADES\BLDTRD" & [ID_TRADE] & ".ini"

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    A clue as to what event (and/or where it is) might help, as Me might not be applicable. Was going to suggest that the function call might be where the issue lies (because the FileSystemObject method is CopyFile but you have FileCopy) but you've indicated the 2nd line is the one that generates the error. Hope you're sure about that because unless you declared DestinationFile as some type of object, the error doesn't make sense to me, based on what I'm reading. Did you determine that by stepping through the code?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Ellpee is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    May 2013
    Posts
    80
    Tried this on my home PC using local computer files, worked fine, so I'm wondering is it because the source and destination files in this case are on the client's network, not local? Seem to remember something awhile back where extra code was needed to handle network filepaths.

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Quote Originally Posted by Ellpee View Post
    Tried this on my home PC using local computer files, worked fine, so I'm wondering is it because the source and destination files in this case are on the client's network, not local? Seem to remember something awhile back where extra code was needed to handle network filepaths.
    That would only happen I should think, if everyone's mapped drives are not the same?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    extra code was needed to handle network filepaths.
    Might need to use UNC path?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    give this a shot
    Code:
    Function GetUNC(strMappedDrive As String) As String
     
        Dim objFso As FileSystemObject
        On Error GoTo GetUNC_Error
     
        Set objFso = New FileSystemObject
        Dim strDrive As String
        Dim strShare As String
     
        'Separated the mapped letter from
        'any following sub-folders
        strDrive = objFso.GetDriveName(strMappedDrive)
     
        'find the UNC share name from the mapped letter
        strShare = objFso.Drives(strDrive).ShareName
     
        'The Replace function allows for sub-folders
        'of the mapped drive
        GetUNC = Replace(strMappedDrive, strDrive, strShare)
     
        Set objFso = Nothing    'Destroy the object
     
        On Error GoTo 0
        Exit Function
     
    GetUNC_Error:
     
        MsgBox "Error " & err.Number & " (" & err.Description & ") in procedure GetUNC of Module modAlwaysIncluded"
     
    End Function
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  8. #8
    Ellpee is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    May 2013
    Posts
    80
    To continue... Just had user run another test, still gets an error on the line that puts the destination filepath in a variable. Apparently no problem with the source filepath. I just now logged on and ran the following in the immediate window, and it worked just fine:

    FileCopy "M:\TRADES\bldtrd_MFTRADE.ini", "M:\TRADES\BLDTRD99999.ini"

    Crazier and crazier. How can it work in immediate mode but not as part of a procedure? Maybe I should just skip the two variables and put the actual strings in the FileCopy line? Only difference would seem to be pulling the trade ID from the form, and there's nothing tricky about that bit as far as I can see. (Original post repeated below for reference.)

    SourceFile = "M:\TRADES\bldtrd_MFTRADE.ini"
    DestinationFile = "M:\TRADES\BLDTRD" & me.ID_TRADE & ".ini"
    FileCopy SourceFile, DestinationFile

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Did you step through this code to see if it is always executing as you expect?
    Does any part of your code insert the last backslash, because I'm not seeing evidence of it within the code?
    Is the file to be copied open - that should raise an error if so.

    EDIT - If none of that helps, I suggest you debug.print what the output variable contains and copy/paste that into a post. It's one thing for you to type it correctly into the immediate window, but maybe another thing if you output it there.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    Ellpee is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    May 2013
    Posts
    80
    Still grappling with this.
    --Took out just the relevant few lines of code and put it in a new module, so I don't have to launch it from the form.
    --Debugs fine, runs fine, throws no errors, but the output file never gets created.
    --Tried it with Filecopy, tried it with FSO.Copyfile, same.
    --Tried it with the UNC filepath, same.
    --Put in the necessary code to change the drive and then the filepath, verified that it does that, still no luck.
    --As posted earlier, ran it in the immediate window, worked perfectly.

    I am absolutely baffled why it won't work in a simple procedure, and since I get no error messages, no clue what else there might be to try. Anyone?

  11. #11
    Ellpee is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    May 2013
    Posts
    80
    Oh, and yes, I stepped thru it, checking each variable etc. along the way, all good.

  12. #12
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Well, most code you've posted is 3 or 4 lines at a time, so not much to go on. You've gone from errors (mentioned in 2 posts) to no errors now? Aside from posting your entire procedure or a zipped db copy, all I can think of is to copy the output path and paste it into File Explorer and see what happens, on the very slim chance that you're not looking in the right place. That might seem like in insult to your intelligence but it's not meant to be and I probably wouldn't suggest it if you were still getting errors.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  13. #13
    Ellpee is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    May 2013
    Posts
    80
    Okay, well, I'm waving the white flag on this one. I started out explaining a simple thing I was trying to do, and have since posted many different things I tried without success. Lots of well-intended responses were heavy on "post more code" and "upload your database." My code, or the bit that is driving me nuts, is only a few lines trying to do a simple thing, so uploading much beyond that seems pointless. Just think of it as a tiny VBA procedure with only one purpose. So, back to square one. All I'm trying to get VBA to do is take an existing file on a network drive, make a copy, and save the copy under a different filename in exactly the same folder on exactly the same drive. It would appear the routes I tried -- FileCopy and FSO.CopyFile -- were not the way to go, so anybody who can suggest a different path that will get this done, let's hear it. Appreciate all the responses, but this is really in the category of "2 + 2 should yield 4, why doesn't it?" (Oh, but pulling it into a separate module did at least get rid of the error messages. Now it runs fine, no errors, but also no output file. Go figure.)

  14. #14
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I've got nothing else to offer in terms of advice or anything else beyond what you don't want to consider so I wish you good luck with your issue.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  15. #15
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 10-05-2018, 10:56 AM
  2. Faster way to copy a file than FileCopy()
    By kdbailey in forum Access
    Replies: 10
    Last Post: 10-29-2015, 07:20 AM
  3. Replies: 1
    Last Post: 03-18-2014, 05:16 PM
  4. FileCopy
    By thescottsman92 in forum Access
    Replies: 1
    Last Post: 09-02-2013, 04:35 PM
  5. FileCopy Funtion
    By dccjr in forum Programming
    Replies: 2
    Last Post: 04-18-2013, 09:04 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