Page 1 of 4 123 ... LastLast
Results 1 to 15 of 52
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Carrollton, Georgia, USA
    Posts
    123
    Thanks
    3
    Thanked 1 Time in 1 Post

    Suggestions.... (97 SR2)

    Hi guys,

    We are using Access to open Windows Explorer, cut, copy, rename and delete files, using the 'Send Keys' function. This system is working well, but not very efficiently. The process is automated, so basically, we start the process to move, rename, etc. allot of files, and let it run. Of course the process is accurate; we couldn't have it making any mistakes.

    I was wondering if anyone had a suggestion to accomplish the same task without leaving Access and having to use Windows Explorer.

    Your suggestions and comments are appreciated!
    Thanks!
    KST

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Suggestions.... (97 SR2)

    It isn't necessary to use Windows explorer at all for this process because it can be done entirely from VBA. Tell us what you're trying to accomplish so the answers will be most useful to you. For instance, is the user selecting a folder for this process, or how does the application know which folder to start with? Are these Access files or Access-created files of some sort? What exactly do you mean by "move, rename, etc.", since those processes aren't mutually exclusive in any way?
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Carrollton, Georgia, USA
    Posts
    123
    Thanks
    3
    Thanked 1 Time in 1 Post

    Re: Suggestions.... (97 SR2)

    Excuse me, I'm new at this. Let me see if I can describe the process better.

    We look at images using some photo software. While viewing the images, we create a record for each image. The record is created automatically, creating a file name field, color field, orientation field, and what number the file would be in a directory, as well as a directory location field. Once those items are created, we have Access open Windows Explorer, and use the "send keys" option to put in the directory location in the address bar, and then cursor to the image file. It then copies it, and puts it in another location for processing by other photo software. We are doing this for 300 to 800 files at a time. Once we create the original records, we let the process run over night to put the files in the correct location.

    So, the user is viewing images, and creating a record for each image w/ the characteristics of each image. Once that's complete, the Access/Explorer operation is automatic. We do this twice during the whole process. Once for running proofs, and then once the order is placed, we use the information there to do it again to print the customer's order.

    Hope this is better information. Thanks for your reply.
    KST

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Suggestions.... (97 SR2)

    You can copy a file from one location to another by using the FileCopy like this:

    <pre> If Dir(strDestpath) = "" Then
    'if it doesn't already exist, copy it
    FileCopy strSourcePath, strDestpath ' Copy source to destination.
    End If</pre>


    Using the Dir function first insures that there is not already a file by that name in the destination folder. It isn't clear to me which file location you're storing in your table, the current location or the desired location, so I can't give you a more complete routine. There are ways of testing to make sure the destination folder actually exists before you try to copy to it, and you can use Kill to delete the file in the old location after you have copied it to the new. There is also a Move method for files, but I don't recall whether it was available in Access 97.
    Charlotte

  5. #5
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Carrollton, Georgia, USA
    Posts
    123
    Thanks
    3
    Thanked 1 Time in 1 Post

    Re: Suggestions.... (97 SR2)

    Charlotte,

    I have been creating an origination location as well as a destination location. I'm excited to hear you think I can do all of this in Access. I do need to tell you that I am not good at writing code. I have been using Access for about 8 years, but just lately have started trying to write code. If you could explain you code alittle more, that would be great.

    I have been sending Windows Explorer to the original file location, copying the file, and then sending Windows Explorer to the destination location, and then pasting the file. In the second part of the process, when the customer orders, I do the same thing except I rename the file to give the print a serial number, and then I paste it in the destination location.

    Thanks again for your help. I am currently trying your previous code to get it to work for me, but my lack of code ability is getting in the way.
    KST

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Suggestions.... (97 SR2)

    Excuse me for butting in here Charlotte, but I have done this kind of thing quite often in the past.

    What Charlotte says is right on the button.

    Her example defines how you go about copying a file to the destination directory but checking if it already exists:
    'if it doesn't already exist, copy it
    If Dir(strDestpath) = "" Then
    ' Copy source to destination.
    FileCopy strSourcePath, strDestpath
    End If

    The Filecopy command is the key to what you want to do. The strSourcePath and strDestpath can be set to a path and filename, eg: strPath & strSourceFilename.
    So the filecopy command could be:

    FileCopy strSourcePath & strSourceFilename, strDestpath & strDestFilename

    As you can now see, you can nominate the Source and Destination paths as well as the Source and Destination filenames. Your Destination filename could be strDestFilename and this could be defined as strDestFilenamePrefix & strDestFilenameSuffix. The variable strDestFilenamePrefix or strDestFilenameSuffix could be used to setup the serial number that you were describing.

    As Charlotte mentioned you can also use the Kill command to delete a file.

    HTH
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  7. #7
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Carrollton, Georgia, USA
    Posts
    123
    Thanks
    3
    Thanked 1 Time in 1 Post

    Re: Suggestions.... (97 SR2)

    OK, guys, be patient with me.

    I am trying to get this to work in a test db. I'm telling you, I am CODE STUPID! LOL!

    OK, here is what I done.

    I created a table called "File Information", with two fields, SourcePath, and DestPath. I then created a form called "File Information", and put a command button there. I put some correct information in the fields for a file and set the click command for the button to go to the code below:


    Function FileCopyMove()

    If Dir(strDestpath) = "" Then
    'if it doesn't already exist, copy it
    FileCopy strSourcePath, strDestpath
    'Copy source to destination.
    End If

    End Function

    I'm getting an error, and I know I don't have something right, but, I can't see what. Error is "Compile Error" Variable Not Defined, and it highlites (strDestpath) part of the code. I feel like I need to add something to the code, but don't know what.

    Thanks again for your help and patients. I'm learning slowly!
    KST

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Suggestions.... (97 SR2)

    You can't just refer to fields in a table that way. In my example, strSourcePath and strDestPath are variables that have been passed into the routine. If you want to get the values from a table, you have to open a recordset on the appropriate records in the table and then loop through the records in the recordset passing the values from those fields to the FileCopy command. Is the table you're using cleared after you move the files or is some kind of flag set to that you can distinguish those that have been moved from the ones left to be moved?
    Charlotte

  9. #9
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Carrollton, Georgia, USA
    Posts
    123
    Thanks
    3
    Thanked 1 Time in 1 Post

    Re: Suggestions.... (97 SR2)

    Charlotte,

    I have a form based on a table, and in the form, I have some fields that are calculated based on the information in the table. And 'Yes' (i think) to the question, I can distiquish between files that have been moved, and not moved. I have a field in the table that gets a Now() date function when they get moved.

    So, I would need the code to work from the form, if that's possible. I am used to creating tables, then creating forms based on those tables, and doing my work on the form, and running what little code I can write from those forms.

    Do I need to change to make this work?
    Thanks for your patience.
    KST

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Suggestions.... (97 SR2)

    Working from a form is fine, but you still need to use a recordset because a form has only one current record at a time (even if the form is continuous) and you apparently want to loop through the records and transfer multiple files. Are those calculated fields something you need to use in the file transfer?
    Charlotte

  11. #11
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Carrollton, Georgia, USA
    Posts
    123
    Thanks
    3
    Thanked 1 Time in 1 Post

    Re: Suggestions.... (97 SR2)

    Yes, and what I'm doing is using a "SetValue" command to put the calculated information into a field that is then copied and put into a macro.

    I have customer information in the database. If I clear that data, is it OK to post the database for evaluation? I might be alittle emabarrassed for you guys to see my work, but.... If it helps me learn, and improves my database, I'm OK with that. Can you advise me on that?

    You and other's may be able to better help me.

    Meanwhile, I'll look at the information and see what I can change to protect the client information that's in my database.

    Thanks!
    KST

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Suggestions.... (97 SR2)

    If you can post a database with the macro, the table and the form with only a few dummy records in the table, that probably would be easier than trying to explain in a long thread of questions and answers. Make sure you zip the mdb file and that the total size is 100Kb or less zipped. The database doesn't actually have to work, only to contain the objects you're asking questions about.
    Charlotte

  13. #13
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Carrollton, Georgia, USA
    Posts
    123
    Thanks
    3
    Thanked 1 Time in 1 Post

    Re: Suggestions.... (97 SR2)

    OK, here is the 1st process I've been talking about. It's the "Proofing" section of the beast. I call it 'PhotoCode'.

    Basically, this is working fine. I just want to improve it. One (of many ways, Im sure) way to improve it would be to not use Windows Explorer to move the files around.

    Thanks!
    KST
    Attached Files Attached Files

  14. #14
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Suggestions.... (97 SR2)

    How does this basically work? Does it take all the files from N:1Raw and copy them to c:1ProofsToPrintColorVertical?
    After looking at your database I am going to offer a suggestion on how to do it.
    Behind the button Send Files I would put the following code behind the DoubleClick event. You may have to change some of the code to reflect your own variables.

    What this routine does is to copy all files from N:1Raw to c:1ProofsToPrintColorVertical.

    Dim strMatch as String
    strMatch = Dir("N:1Raw*.*")
    Do While strMatch <> ""
    FileCopy "N:1Raw" & strMatch , "c:1ProofsToPrintColorVertical" & strMatch
    Dir()
    Loop

    I have not put code in here to test if the destination filename already exists, but this is very straightforward if you require it

    HTH
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  15. #15
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Suggestions.... (97 SR2)

    It's been so long since I looked at anything built purely with macros that's it hard for me to even figure out what you're doing here. It appears that the Proofing button on your main menu opens the form CD Move Session Info which then calls the macro CD-Move1 from the Send button, right? Is this what you want help with? What are all the CD Move Timer forms for? You seem to be opening and closing them for some reason, but I'm at a loss to know what that is.

    Why don't you list the steps that apply in a post here so anyone who wants to help won't have to wade through every form and macro in the database to try and figure out where to start? One thing that might also help you is to convert all your macros to VBA code. You can do that by selecting a macro and doing a File-->Save As/Export. One of the options is Save as Visual Basic Module and that will convert the macro to a VBA module. It won't make it any more efficient, but it will be a start on moving you out of macros, which can't be error trapped and which run into recursion problems when calling themselves, and into VBA where you have a great deal more control over what goes on in your application.

    I have to say, it would be much easier to understand (and maintain) this database if you used some kind of naming convention (i.e., name all forms something like frmMyForm and all subforms something like fsbMySubform, etc.) and got rid of all those spaces in the object names, including field names. Object names with spaces (and with characters like "-" and "/" and "," in them) require special handling and are likely to trip you up in queries and in code. The way it is presently designed, it is impossible to tell for sure whether an object being referenced is a form, subform, table or macro except by the way it's being called.
    Charlotte

Page 1 of 4 123 ... LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •