Excel VBA - Find file with Dir based on keyword or ask user to path it -


i'm new vba, i'm pulling few macros together. need find file based on given path (stored in c2) , keyword (stored in d2) , print complete path e2 , if file not found, prompt dialog user can find (in case spell mistake made example).

what have far:

 public sub pather()      'find path file1 based on keyword1     dim file1 variant, keyword1 string, path1 string     keyword1 = sheet5.range("d2").text     path1 = sheet5.range("c2").text    file1 = dir(mainpath & path1)    while (file1 <> "")       'insert keyword below        if sheet5.range("e2") = ""       'print file1 path e2         sheet5.range("e2") = path1 & file1       ' display error message test reason       ' (change dialog script user can find file1 )       else:          msgbox "file not found."          '*** add filedialog here ***          exit sub       end if      file1 = dir   wend      end sub

works if leave else out... when include else, if file exists, return both file path e2 , display alert. doing wrong?

also, want display alert , run following script:

dim fd filedialog dim filename string set fd = application.filedialog(msofiledialogopen) 'the number of button chosen dim filechosen integer filechosen = fd.show fd.title = "choose workbook" fd.initialfilename = "c:\test" fd.initialview = msofiledialogviewlist 'show excel workbooks , macro workbooks fd.filters.clear fd.filters.add "excel workbooks", "*.xlsx" fd.filters.add "excel macros", "*.xlsm" fd.filterindex = 1 fd.buttonname = "choose file" if filechosen <> -1 'didn't choose (clicked on cancel) msgbox "no file choosen. file wont saved .pdf" else 'get file, , open (name property 'includes path, need) filename = fd.selecteditems(1) workbooks.open (filename) end if

is possible embed 2nd script 1st?

thanks soooo much

what input path1 in cell c2? if it's folder while loop goes on while there files go through. that's why multiple outputs (i.e. return of both possible outcomes).

also purpose of keyword1? supposed wildcard, extension, or file name?

edit:

for listing files match wildcard use:

   public sub pather()      'find path file1 based on keyword1     dim file1 variant, keyword1 string, path1 string     keyword1 = sheet5.range("d2").text     path1 = sheet5.range("c2").text    file1 = dir(mainpath & path1)    dim integer    = 1    while (file1 <> "")       'insert keyword below     debug.print file1       if cells(i, 5) = ""          if instr(file1, keyword1) > 0             'print file1 path e2               cells(i, 5) = path1 & file1             ' display error message test reason             ' (change dialog script user can find file1 )             = + 1             'exit sub         end if       else          debug.print "file not found"          msgbox "file not found."          '*** add filedialog here ***          exit sub       end if      file1 = dir     wend      end sub

if want list first matches wildcard, remove ' symbol from: 'exit sub (so exits after first match found).


Comments

Post a Comment

Popular posts from this blog

searchKeyword not working in AngularJS filter -

i writing app using angularjs on front end. want search through table word/field; 1 search box everything. according article here: http://hello-angularjs.appspot.com/searchtable can use filter: searchkeyword this. this code, , not working expected. <label>search: <input ng-model="searchkeyword"></label> <table ng-repeat="post in posts | orderby: sort | filter: searchkeyword"> <tr> <td> {{index(post)}} </td> <td> {{post.title}} </td> <td> {{post.author}} </td> <td> {{post.content}} </td> <td> {{post.date | date: "d/m/yyyy"}} </td> </tr> </table> what should do? thank you try this: controller $scope.posts = [ {title:'title one', author:'author one', content: 'content one'}, {title:'title two', author:'author two', content: 'cont...
Read more

sequelize.js - Sequelize: sort by enum cases -

i have model ( article ) field (the name type ) of type enum('source', 'translated') , return 1 article ordered type field. source articles should returned before translated articles. like: article.findone(order: [ { type: [ 'source', 'translated' ] } ]); unfortunately there appears no built-in solution sorting enums. instead have sort by case , couldn't find documentation on how sequelize. what conventional approach here? sorting case hard in sequelize, sort field (the second answer in linked post) should possible: order: sequelize.fn('field', sequelize.col('type'), 'source', 'translated') it's supported mysql far know though
Read more

user interface - how to replace an ongoing process of image capture from another process call over the same ImageLabel in python's GUI TKinter -

i trying use following code create interactive gui changes color-space(hsv, rbg, grayscale etc) on event of button click. display opencv video in tkinter using multiprocessing being new python having problems multiprocessing , attempts on making gui change it's color space on button clicks hangs entire system. on it's implementation highly appreciated. thankyou. below code: import cv2 pil import image,imagetk import tkinter tk import numpy np multiprocessing import process , queue def quit_it(root,process): root.destroy() process.terminate() def black_andwhite(root,process): process.terminate p=process(target=capture_image, args=(5,queue, )) p.start() root.after(0, func=lambda: update_all(root, image_label, queue)) def update_image(image_label, queue): frame = queue.get() = image.fromarray(frame) b = imagetk.photoimage(image=a) image_label.configure(image=b) image_label._image_cache = b root.update() def update_all(root...
Read more