vba - Running a macro for mutiple files on excel -


ok, suck @ , thats why i'm asking help.

i 700 new files every month , have clean them before putting together. have macro that, work has been done manually, file file. want find way run macro every file @ once , save ton of time every month. know there's way that, don't know how.

sub ibo()      rows("1:6").select     selection.delete shift:=xlup     rows("16:18").select     selection.delete shift:=xlup     activewindow.smallscroll down:=6     rows("31:38").select     selection.delete shift:=xlup     activewindow.smallscroll down:=12     rows("46:46").select     selection.delete shift:=xlup     rows("46:47").select     range("r46").activate     selection.delete shift:=xlup     activewindow.scrollcolumn = 9     activewindow.scrollcolumn = 8     activewindow.scrollcolumn = 7     activewindow.scrollcolumn = 5     activewindow.scrollcolumn = 4     activewindow.scrollcolumn = 2     activewindow.scrollcolumn = 1     activewindow.smallscroll down:=15     rows("62:62").select     selection.delete shift:=xlup     activewindow.smallscroll down:=-24     rows("34:34").select     selection.insert shift:=xldown     activewindow.smallscroll down:=-9     rows("19:19").select     selection.insert shift:=xldown     activewindow.smallscroll down:=-12     rows("4:4").select     selection.insert shift:=xldown     range("b17:c17").select     activewindow.smallscroll down:=6     range("b17:p32").select     selection.copy     range("r1").select     activesheet.paste     activewindow.smallscroll down:=15     range("b33:t48").select     application.cutcopymode = false     selection.copy     range("ag1").select     activesheet.paste     range("a1:a3").select     activewindow.smallscroll down:=33     range("b49:m49").select     activewindow.smallscroll down:=6     range("b49:s64").select     application.cutcopymode = false     selection.copy     range("az1").select     activesheet.paste     range("bq1").select end sub

i want know how add macro on run on files @ once

i appreciate recursivedir function provided ammara digital solutions. pairs folder picker.

public function recursivedir(colfiles collection, _                              strfolder string, _                              strfilespec string, _                              bincludesubfolders boolean)      dim strtemp string     dim colfolders new collection     dim vfoldername variant      'add files in strfolder matching strfilespec colfiles     strfolder = trailingslash(strfolder)     strtemp = dir(strfolder & strfilespec)     while strtemp <> vbnullstring         colfiles.add strfolder & strtemp         strtemp = dir     loop      if bincludesubfolders         'fill colfolders list of subdirectories of strfolder         strtemp = dir(strfolder, vbdirectory)         while strtemp <> vbnullstring             if (strtemp <> ".") , (strtemp <> "..")                 if (getattr(strfolder & strtemp) , vbdirectory) <> 0                     colfolders.add strtemp                 end if             end if             strtemp = dir         loop          'call recursivedir each subfolder in colfolders         each vfoldername in colfolders             call recursivedir(colfiles, strfolder & vfoldername, strfilespec, true)         next vfoldername     end if  end function  public function trailingslash(strfolder string) string     if len(strfolder) > 0         if right(strfolder, 1) = "\"             trailingslash = strfolder         else             trailingslash = strfolder & "\"         end if     end if end function  sub mymacro()      dim strpath string     dim colfiles new collection     dim varfile variant     dim wbkmybook workbook  '* folder picker. left click folder once '* , choose select set strpath equal folder.      application.filedialog(msofiledialogfolderpicker)         .title = "select folder"      '* set title of folder picker window.         .allowmultiselect = false       '* not allow multiple folders selected.         .initialfilename = "documents"  '* set initial location windows "my documents" folder.         if .show = true             strpath = .selecteditems(1) '* set strpath equal selected folder.         else             exit sub                    '* exit sub if click cancel on folder picker window.         end if     end  '* here recursivedir called. creates collection '* of files (colfiles) in path (strpath) '* match filter ("*.xlsx"). last argument (true) '* instructs recursivedir search subfolders.      recursivedir colfiles, strpath, "*.xlsx", true      each varfile in colfiles         set wbkmybook = workbooks.open(varfile)  '* perform work on each file. '* variable (varfile) references current file '* on recursivedir looping.          debug.print varfile         wbkmybook.sheets(1).cells(1, 1) = "hello."         wbkmybook.close savechanges:=true      next varfile  end sub

Comments

Post a Comment

Popular posts from this blog

php - SPIP: From Tag directly to an article -

i work spip , plugin called tagsphere works fine tags in spip. have tag groupe put 1 article tag. when click on tag in tagsphere send me before menu can see articles linked tag. want send me directly article without see tag menu before. here code model of thagsphere: <div id="tagsphere-#env{id_article}"> <ul> <boucle_mot(mots){id_groupe ?}> <li>[<a href="#url_mot">(#titre)</a>]</li> </boucle_mot> </ul> </div> anybody idea? if want skip tag page, have link directly article. <b_article> block prevents display tag if no article linked. <div id="tagsphere-#env{id_article}"> <ul> <boucle_mot(mots){id_groupe ?}> <b_article> <li> <a<boucle_article(articles){...
Read more

jquery - isAjaxRequest always return false -

i got error message 404,'model has not been saved' following code my action controller public function actionextend($id) { $model=$this->loadmodel($id); if(yii::app()->request->isajaxrequest) { $model->todate = date('y-m-d h:i:s a',(strtotime('next month',strtotime(date('y-01-d'))))); if($model->save()) { yii::app()->end(); } else { throw new chttpexception(404,'model has not been saved'); } } } the jquery call $("#buttonextend<?php echo $this->post_row;?>").click(function(e){ $.ajax({ type: "post", url: "<?php echo yii::app()->createurl('post/extend', array('id' => $data['id'])); ?>", }); }); why not ajax call ? thank help i found response, missed } , function ...
Read more

ruby on rails - In a controller spec, how to find a specific tag in the generated view? -

i know integration tests preferred need ran in controller test, i'm testing gem injecting html code in view, xhr can't run in feature spec (if can, please explain me how :) ) so rspec controller tests can assert selector present (with capybara) : response.body.should have_selector('#foobar') has_selector? call all method capybara find selector. what want last child of body , assert id in particular. afaik it's not possible have_selector. what : all('body:first-child').first.id.should == '#foobar' however, capybara dsl, defined (more or less): def all(*args) page.all(*args) end and page empty unless use visit it's integrations specs. how can use capybara all method inside rspec controller test ? i can't test right after googling seems trick def page capybara::node::simple.new(response.body) end source
Read more