PDA

View Full Version : VB looking at excel file to move files



jamesttait
08-14-2013, 05:24 AM
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.

patel
08-15-2013, 12:21 AM
attach please a sample file with comments

jamesttait
08-15-2013, 05:49 AM
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?

Kenneth Hobs
08-15-2013, 06:27 AM
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.

jamesttait
08-15-2013, 06:42 AM
I need to have 5 posts to use links this is post 3

jamesttait
08-15-2013, 06:43 AM
I need to have 5 posts to use links this is post 4

jamesttait
08-15-2013, 06:45 AM
I need to have 5 posts to use links this is post 5

jamesttait
08-15-2013, 06:46 AM
Here is the link now, joined box and added the file.https://app.box.com/s/rbclowj3dvc1ksfaab39

patel
08-15-2013, 10:59 PM
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

jamesttait
08-16-2013, 01:02 AM
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?

jamesttait
08-16-2013, 01:34 AM
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.

patel
08-16-2013, 07:49 AM
try

Sub a()
on error resume next
LR = Cells(Rows.Count, "A").End(xlUp).Row
......