vba - Get the data from excel files in sub directories -


i new vba , programming in general. first post on board. i've been working on while modifying code i've found on internet , have code want, modify speed process.

the code have pulls data excel files deposit in folder on desktop "receiving temp" , places data in workbook "receiving data extractor". getting data 1000 files month stored in sub-directories named p.o. associated (varying names). right have go through each of these sub directories , move excel files "receiving temp" before macro work. modify code same excel files contained within sub directories within folder allowing me copy sub-folders "receiving temp" folder , run macro rather opening each sub directory , grabbing excel file , moving manually. again, sub-directories have varying names.

i appreciate can offer.

sub readdatafromallworkbooksinfolder()     dim foldername string, wbname string, r long     dim cvalue variant, bvalue variant, avalue variant     dim dvalue variant, evalue variant, fvalue variant     dim wblist() string, wbcount integer, integer      foldername = thisworkbook.path & "\receiving temp\"      ' create list of workbooks in foldername     wbcount = 0     wbname = dir(foldername & "\" & "*.xls")     while wbname <> ""         wbcount = wbcount + 1         redim preserve wblist(1 wbcount)         wblist(wbcount) = wbname         wbname = dir     wend     if wbcount = 0 exit sub     ' values each workbook     r = 1      = 1 wbcount         r = r + 1         cvalue = getinfofromclosedfile(foldername, wblist(i), "quality rep.", "c9")         bvalue = getinfofromclosedfile(foldername, wblist(i), "quality rep.", "o61")         avalue = getinfofromclosedfile(foldername, wblist(i), "quality rep.", "ae11")         dvalue = getinfofromclosedfile(foldername, wblist(i), "quality rep.", "v9")         evalue = getinfofromclosedfile(foldername, wblist(i), "quality rep.", "af3")         fvalue = getinfofromclosedfile(foldername, wblist(i), "non compliance", "a1")            sheets("sheet1").cells(r, 1).value = cvalue          sheets("sheet1").cells(r, 2).value = bvalue          sheets("sheet1").cells(r, 3).value = avalue          sheets("sheet1").cells(r, 4).value = dvalue          sheets("sheet1").cells(r, 6).value = evalue          sheets("sheet1").cells(r, 5).value = fvalue      next end sub  private function getinfofromclosedfile(byval wbpath string, _ wbname string, wsname string, cellref string) variant     dim arg string      getinfofromclosedfile = ""      if right(wbpath, 1) <> "\" wbpath = wbpath & "\"      if dir(wbpath & "\" & wbname) = "" exit function      arg = "'" & wbpath & "[" & wbname & "]" & _           wsname & "'!" & range(cellref).address(true, true, xlr1c1)      on error resume next     getinfofromclosedfile = executeexcel4macro(arg) end function

the creation of array doing has inside processfiles function taken here. once array made, rest of original code remains is. had make changes getinfofromclosedfile function when copy, copy complete code given below , not change anything.

option explicit  dim wblist() string dim wbcount long  sub readdatafromallworkbooksinfolder()     dim foldername string     dim cvalue variant, bvalue variant, avalue variant     dim dvalue variant, evalue variant, fvalue variant     dim long, r long      foldername = thisworkbook.path & "\receiving temp"      processfiles foldername, "*.xls"      if wbcount = 0 exit sub      r = 1      = 1 ubound(wblist)          '~~> wblist(i) give         '   c:\receiving temp\aaa.xls         '   c:\receiving temp\folder1\aaa.xls         debug.print wblist(i)          r = r + 1         cvalue = getinfofromclosedfile(wblist(i), "quality rep.", "c9")         bvalue = getinfofromclosedfile(wblist(i), "quality rep.", "o61")         avalue = getinfofromclosedfile(wblist(i), "quality rep.", "ae11")         dvalue = getinfofromclosedfile(wblist(i), "quality rep.", "v9")         evalue = getinfofromclosedfile(wblist(i), "quality rep.", "af3")         fvalue = getinfofromclosedfile(wblist(i), "non compliance", "a1")          sheets("sheet1").cells(r, 1).value = cvalue         sheets("sheet1").cells(r, 2).value = bvalue         sheets("sheet1").cells(r, 3).value = avalue         sheets("sheet1").cells(r, 4).value = dvalue         sheets("sheet1").cells(r, 6).value = evalue         sheets("sheet1").cells(r, 5).value = fvalue      next end sub  '~~> function taken '~~> http://www.vbaexpress.com/kb/getarticle.php?kb_id=245 sub processfiles(strfolder string, strfilepattern string)     dim strfilename string, strfolders() string     dim long, ifoldercount long      '~~> collect child folders     strfilename = dir$(strfolder & "\", vbdirectory)     until strfilename = ""         if (getattr(strfolder & "\" & strfilename) , vbdirectory) = vbdirectory             if left$(strfilename, 1) <> "."                 redim preserve strfolders(ifoldercount)                 strfolders(ifoldercount) = strfolder & "\" & strfilename                 ifoldercount = ifoldercount + 1             end if         end if         strfilename = dir$()     loop      '~~> process files in current folder     strfilename = dir$(strfolder & "\" & strfilepattern)     until strfilename = ""         wbcount = wbcount + 1         redim preserve wblist(1 wbcount)         wblist(wbcount) = strfolder & "\" & strfilename         strfilename = dir$()     loop      '~~> through child folders     = 0 ifoldercount - 1         processfiles strfolders(i), strfilepattern     next end sub  private function getinfofromclosedfile(byval wbfile string, _ wsname string, cellref string) variant     dim arg string, wbpath string, wbname string      getinfofromclosedfile = ""      wbname = functiongetfilename(wbfile)     wbpath = replace(wbfile, "\" & wbname, "")      arg = "'" & wbpath & "\[" & wbname & "]" & _           wsname & "'!" & range(cellref).address(true, true, xlr1c1)      on error resume next     getinfofromclosedfile = executeexcel4macro(arg) end function  '~~> function file name full path '~~> taken http://www.ozgrid.com/vba/getexcelfilenamefrompath.htm function functiongetfilename(fullpath string)     dim strfind string     dim long      until left(strfind, 1) = "\"         = + 1         strfind = right(fullpath, i)         if = len(fullpath) exit     loop     functiongetfilename = right(strfind, len(strfind) - 1) end function

Comments

Post a Comment

Popular posts from this blog

java - Play! framework 2.0: How to display multiple image? -

i need display gallery of photos. here template: @(photos: list[photo]) @title = { <bold>gallery</bold> } @main(title,"photo"){ <ul class="thumbnails"> @for(photo <- photos) { <li class="span3"> <a href="#" class="thumbnail"> <img src="@photo.path" alt=""> </a> </li> } </ul> } and here controller method: public static result getphotos() { return ok(views.html.photo.gallery.render(photo.get())); } and here photo bean : @entity public class photo extends model { @id public long id; @required public string label; public string path; public photo(string path, string label) { this.path = path; this.label = label; } private static finder<long, photo> find = new finder<long, photo>( long.class, photo.class); public static list<phot...
Read more

gmail - Is there any documentation for read-only access to the Google Contacts API? -

i want read-only access user's google contacts. there stackoverflow member wanted this, , received following answer : yes, there is, use https://www.googleapis.com/auth/contacts.readonly scope , "view contacts". this exchange reference read-only access google contacts can find using google. simply using access token obtained attempt read usual google contacts api (i.e. https://www.google.com/m8/feeds ) fails insufficient permissions. similarly, using google contacts api on new target fails 404 errors (for example https://www.googleapis.com/auth/contacts.readonly/default/full ). is there documentation anywhere on how use api, or permissible way access google contacts api full read-write access?
Read more

php - Controller/JToolBar not working in Joomla 2.5 -

relevant code: jtoolbarhelper::custom('savecategories', 'save', '', 'save', false, false); ... <input type="hidden" name="controller" value="easyblogcontroller"> according can dig docs , own previous questions, should call savecategories() function in easyblogcontroller . i've tried setting value easyblog , easyblog.php (file name) current easyblogcontroller (class name). clicking on save button refreshes page. not redirect, echo or var_dump test code put in savecategories() function. var_dump ing jrequest::getvar('controller') , 'task' return correct values. creating controller object , using $controller->execute('task'); works. the values going depend on location of controller trying call, , have couple options here. typically there controller in base folder of component (likely components/com_easyblog) in file called controller.php , class name inside ...
Read more