vba - How to aggregate/compile multiple Excel spreasheets (.csv) into separate sheets in one workbook? -


i not programmer - need/want write command in excel aggregate multiple .csv files separate sheets in 1 workbook... runs once, , copy/paste contents of 1 .csv file errors out error: 

runtime error '438':  object not support property or method.

i've narrowed down line: 

'paste thisworkbook.worksheets(sheets.count).range("a1").paste

but, being new of this, unsure do. far have been grabbing seemed applicable code snippets around web.

private sub commandbutton1_click()  dim strfile string, strpath string dim wkb workbook  'change path own file location: strpath = "c:[file path here]"  'this returns empty string "" if file cannot found , error  if folder incorrect strfile = dir(strpath & "*.csv")  while strfile <> ""  'open csv file , assign variable can   reference later set wkb = workbooks.open(strpath & strfile)   'add new worksheet @ end of macro workbook paste thisworkbook.sheets.add after:=sheets(sheets.count)   'get range , copy wkb.sheets(1).usedrange.copy debug.print (sheets.count)  'paste thisworkbook.worksheets(sheets.count).range("a1").paste   'close csv file wkb.close   'find next file - dir without parameters next file in folder matches first dir call strfile = dir loop    end sub 

 sub dougsloop()       dim wbk workbook      dim filename string      dim path string      dim wso workbook      dim starttime double      dim secondselapsed double      dim arr variant      dim rowc long      dim colc long       application.screenupdating = false      application.displayalerts = false      application.calculation = xlcalculationmanual       starttime = timer       path = "path folder of files" & "\"      filename = dir(path & "*.csv??")      set wso = thisworkbook      wso.sheets(1).select       while len(filename) > 0          doevents          set wbk = workbooks.open(path & filename, true, true)          arr = wbk.sheets(1).usedrange          rowc = wbk.sheets(1).usedrange.rows.count          colc = wbk.sheets(1).usedrange.columns.count          wso.activesheet.range(wso.activesheet.cells(1, 1), wso.activesheet.cells(rowc, colc)).value2 = arr          wbk.close false          filename = dir          wso.sheets.add after:=worksheets(worksheets.count)      loop       application.screenupdating = true      application.displayalerts = true      application.calculation = xlcalculationautomatic       secondselapsed = round(timer - starttime, 2)      msgbox "this code ran in " & secondselapsed & " seconds", vbinformation   end sub

Comments

Post a Comment

Popular posts from this blog

How to understand 2 main() functions after using uftrace to profile the C++ program? -

i trying uftrace profile following simple c++ program: #include <iostream> class { public: a() {std::cout << "a created" << std::endl;} ~a() {std::cout << "a destroyed" << std::endl;} }; int main() { a; return 0; } the profile result this: # uftrace a.out created destroyed # duration tid function 2.026 [ 4828] | __cxa_atexit(); [ 4828] | main() { [ 4828] | __static_initialization_and_destruction_0() { 89.397 [ 4828] | std::ios_base::init::init(); 0.768 [ 4828] | __cxa_atexit(); 93.029 [ 4828] | } /* __static_initialization_and_destruction_0 */ 94.425 [ 4828] | } /* main */ [ 4828] | main() { [ 4828] | a::a() { 11.104 [ 4828] | std::operator <<(); 10.825 [ 4828] | std::basic_ostream::operator <<(); 24.514 [ 4828] | } /* a::a */ [ 4828] | a::~a() { 0.978 [ 4828] | ...
Read more

c# - Update a combobox from a presenter (MVP) -

i using mvp in project, new in mvp. have 2 comboboxes. when select option in combobox, combobox should filled new data. this action in presenter. view 'view1' in presenter, , introduced combobox1 , combobox2 properties in 'view1', because need 'datasource', 'displaymember', 'valuemember' , 'refresh()' in method below. but, when using pattern, enough send property like public string combobox2 { { return combobox1.selectedvalue.tosstring(); } } into presenter not whole combobox. how can solve problem? public void onselectedindexchangedcombobox1() { if (view1.combobox1.selectedindex == -1) { return; } datatable dt = tools.getdatatable("a path"); var query = (from o in dt.asenumerable() o.field<string>("afield") == farmerview.combobox1.selectedvalue.tostring() orderby o.field<string>("anotherfield") select...
Read more

How to put a lock and transaction on table using spring 4 or above using jdbcTemplate and annotations like @Transactional? -

i trying put lock on table while writing on table , if happened in between roll-back . trying convert below code lock table test_g1 read; lock table test_g write; -- begin; start transaction; insert test_g1 values(143); insert test_g values(145); select * test_g1; select * test_g; rollback; select * test_g; unlock tables; how convert above code @transactional spring jdbctemplate code? @transactional(rollbackfor={dataaccessexception.class}) public void test(){ jdbctemplate.execute("insert test1 (id, nam) values (4, 'a')"); throw new dataaccessexception("error") { }; } here trying throw error, insert statement should rollback it's not happening . thanks edit-1 attaching code , doing in jdbcdaoimpl.java , mentioned problem comment above test() . app.java package com.cgiri.javabrains.spring4; import org.springframework.beans.factory.annotation.autowired; import org.springframework.context.applic...
Read more