vba - Copy/Rename Batch of Files, based on data in a worksheet -


i have list in column hyperlinks of file-names pdf files on shared network drive gets filtered , sorted often. want run macro on selected filtered range excluding hidden rows in table. macro copy files new location, , rename them based on data in worksheet.

column contains hyperlinks file names including extension (e.g. cell "a3" contains "15-p980_vendor_15169_.pdf") column b , e contains text based on formula extract text file name. underscores separators. (column c , d hidden , not used). so, cell "b3" contains "15-p980", , cell "e3" contains "vendor" file name that's in column a.

enter image description here

trying rename file in each row what's in cell(, 5) + "_" + cell(, 2) in selected range rows.

i'm selecting range =$a$3:$e$6.

i getting object required error. i'm having trouble writing for each part. obtaining file path, sourcepath =. figure have obtain hyperlink address that's in column a, extract file path that, not sure how code that. appreciated.

sub copyfile() thisworkbook.activesheet.unprotect     on error goto errhndl     dim xtitleid string     dim sourcepath string, destpath string     dim sourcefile string, destfile string, sourceextension string     dim rng range, cell range, row range       destpath = "c:\users\\desktop\test\dst"     sourcefile = ""     destfile = ""      xtitleid = "copy/rename files"     set rng = thisworkbook.activesheet.application.selection     set rng = thisworkbook.activesheet.application.inputbox("range", xtitleid, rng.rows, type:=8)     set addr = rng.cells(, 1)      each row in rng.rows       sourcepath = addr.hyperlinks(1).address        sourceextension = split(row.cells(, 1), ".")(1)       sourcefile = sourcepath + row.cells(, 1)       destfile = destpath + row.cells(, 5) + "_" + row.cells(, 2) + "." +  sourceextension       file.copy sourcefile, destfile, false     next row       msgbox "operation successful.", vbokonly + vbinformation, "done"     exit sub  errhndl:     msgbox "error happened while working on: " + vbcrlf + _         sourcefile + vbcrlf + vbcrlf + "error " + _         str(err.number) + ": " + err.description, vbcritical + vbokonly, "error"  end sub

i'm not 100% sure of you're trying accomplish in order extract filename , path separately, instead of going through range objects, took approach of looping through hyperlink collection...

for each linky in rng.hyperlinks      sourcepath = left(linky.address, len(linky.address) - len(linky.texttodisplay))      sourceextension = ".pdf" 'split(row.cells(, 1), ".")(1)     sourcefile = linky.address     destfile = destpath + cells(linky.parent.row, 5) + "_" + cells(linky.parent.row, 2) + sourceextension     fso.copyfile sourcefile, destfile, false next linky

you'll have careful possible file duplication, raise error. also, noticed need add \ @ end of destpath.


Comments

Post a Comment

Popular posts from this blog

routing - AngularJS State management ->load multiple states in one page -

Image
i developing site in angularjs ui-router. have situation on button click need change , load 3 state page , 3 looks image .please me how can load multiple pages. in 1 page you need configure router config this: $stateprovider .state("state1", { url: "/url1", controller: "firstcontroller", views: { view1: { templateurl: "templates/template1.html" }, view2: { templateurl: "templates/template2.html" }, view3: { templateurl: "templates/template3.html" } } }).state("state2", { url: "/url2", controller: "secondcontroller", views: { view1: { templateurl: "templates/template4.html" }, view2: { templateurl: "templates/template5.html" }, view3: { templateurl: "templates/template6.html" } }).state("state3", { ...
Read more

python - GRASS parser() error -

i'm trying use grass on python 2.7, i'm having problems @ setting script on idle, i'm getting error @ parser() function: here script: import os import sys gisbase = os.environ['gisbase'] = 'c:\program files (x86)\grass gis 7.0.1rc1' gisrc = 'c:\grassdata' gisdbase = 'c:\grassdata' location = 'newlocation' mapset = 'tc' ld_library_path = 'c:\program files (x86)\grass gis 7.0.1rc1\lib' path = 'c:\program files (x86)\grass gis 7.0.1rc1\etc';'c:\program files (x86)\grass gis 7.0.1rc1\etc\python';'c:\program files (x86)\grass gis 7.0.1rc1\lib';'c:\program files (x86)\grass gis 7.0.1rc1\bin';'c:\python27';'c:\program files (x86)\grass gis 7.0.1rc1\python27';'c:\program files (x86)\grass gis 7.0.1rc1\msys' pythonlib = 'c:\python27' pythonpath = 'c:\program files (x86)\grass gis 7.0.1rc1\etc\python' sys.path.append(os.path.join(os.environ['...
Read more

Swift game error message -

i following ios game tutorial. cannot figure out wrong here. error on these 2 lines. let newangle = percent * 180 - 180 cannon.zrotation = cgfloat(newangle) * cgfloat(m_pi); 180.0 //error "use of unresolved identifier 'percent'" the tutorial uses: cannon.zrotation = cgfloat(newangle) * cgfloat(m_pi)/ 180.0 but changed error suggested import spritekit class gamescene: skscene { var cannon: skspritenode! var touchlocation:cgpoint = cgpointzero override func didmovetoview(view: skview) { /* setup scene here */ cannon = self.childnodewithname("cannon") as! skspritenode } override func touchesbegan(touches: set<uitouch>, withevent event: uievent?) { /* called when touch begins */ touchlocation = touches.first!.locationinnode(self) } override func touchesmoved(touches: set<uitouch>, withevent event: uievent?) { touchlocation = touches.first!.locationinn...
Read more