Consulting

Results 1 to 12 of 12

Thread: VB looking at excel file to move files

  1. #1

    VB looking at excel file to move files

    I need help with some VB code, complete amateur.I am looking for some VB code that can lookup a xls/csv file to move filesFile would have the following columns : Source Folder : Source File Name : Destination Folder : Destination file Name : Date Stamp Required : Delete Previous File : PasswordExplanation1. Source Folder : There file is located2. Source File Name : File name eg myfile.xls, myfile.pdf etc3. Destination Folder : Where the file is going to4. Destination file Name : File name eg myfile.xls, myfile.pdf etc5. (If possible) and password is populated, Either add password to destination file or zip and add password to zip file6. Date Stamp Required : Yes/No : Yes, the date should be placed at the end of the file name. eg myfile140713.xls7. Delete Previous File : Yes/No : if Yes go back to Source folder and delete fileIf file does not exist in the source folder, move on to the next.If step 6 is No, overwrite the file in the destination folderIf file name is *.* move all files within directory but also follow step 6 & 7I have been searching for hours for any code that could help do this with no luck Any help would be greatly appreciated.

  2. #2
    VBAX Mentor
    Joined
    Jul 2012
    Posts
    398
    Location
    attach please a sample file with comments

  3. #3

    Post VB looking at excel file to move files

    Thanks for the help, i am trying to attach an xls file with explanations as requested, but it will not let me, will keep tryingNo joy uploading file, the button does not work for me, might not have access to upload files?
    Last edited by jamesttait; 08-15-2013 at 05:51 AM. Reason: add additional info

  4. #4
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,903
    Location
    I had trouble attaching a file the other day myself. If you can not do it, try breaking your needs into a few single lines in outline format. Feel free to upload the file to a site like box.net if needed.

    I know that we can help you. Mostly, we just need to make sure that we fit the solution to your needs.

  5. #5

    As requested

    I need to have 5 posts to use links this is post 3

  6. #6
    I need to have 5 posts to use links this is post 4

  7. #7
    I need to have 5 posts to use links this is post 5

  8. #8
    Here is the link now, joined box and added the file.https://app.box.com/s/rbclowj3dvc1ksfaab39

  9. #9
    VBAX Mentor
    Joined
    Jul 2012
    Posts
    398
    Location
    try this code (no zip and no password)
    Sub a()
    LR = Cells(Rows.Count, "A").End(xlUp).Row
    For j = 2 To LR
      spath = Cells(j, 1)
      sfname = Cells(j, 2)
      dfname = Cells(j, 4)
      dpath = Cells(j, 3)
      If Range("H" & j) <> "" Then dfname = Range("H" & j)
      If Range("J" & j) = "Yes" Then
        Name spath & sfname As dpath & dfname
        'MsgBox "Name " & spath & sfname & " As " & dpath & dfname
      Else
        'MsgBox "FileCopy " & spath & sfname & "," & dpath & dfname
        FileCopy spath & sfname, dpath & dfname
      End If
    Next
    End Sub

  10. #10
    That's Fantastic and looks great and will help no end with a manual run, can this be converted to run as vbs looking at the excel file that contains the info and runnning this script.I want to use windows scheduler to run this task several times a day.One more question, if it does not find the file name, does it move on to the next?

  11. #11
    Just tested and brill, just one thing, if the file name does not exist, i get an error, need it to bypass that file and move on, as the files will appear at different times of the day.

  12. #12
    VBAX Mentor
    Joined
    Jul 2012
    Posts
    398
    Location
    try
    Sub a()
    on error resume next
    LR = Cells(Rows.Count, "A").End(xlUp).Row
    ......
    
    

Tags for this Thread

Posting Permissions

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