excel - Performance issues when automatically putting a large amount of links in spreadsheet -


i using code snippet place links workbook own:

sub legg_inn_lekkjer()   dim long    call deaktiver    = 0 740     loktider.range("c4").offset(i, 0) = "='[simulering arbeidsplan ovn 3 28h.xls]lokklegging'!r" & cstr(11 + i) & "c6"     loktider.range("d4").offset(i, 0) = "='[simulering arbeidsplan ovn 3 28h.xls]lokklegging'!r" & cstr(11 + i) & "c5"     loktider.range("e4").offset(i, 0) = "='[simulering arbeidsplan ovn 3 28h.xls]lokklegging'!r" & cstr(11 + i) & "c16"     loktider.range("f4").offset(i, 0) = "='[simulering arbeidsplan ovn 3 28h.xls]lokklegging'!r" & cstr(11 + i) & "c15"     loktider.range("g4").offset(i, 0) = "='[simulering arbeidsplan ovn 4 30h.xls]lokklegging'!r" & cstr(11 + i) & "c6"     loktider.range("h4").offset(i, 0) = "='[simulering arbeidsplan ovn 4 30h.xls]lokklegging'!r" & cstr(11 + i) & "c5"     loktider.range("i4").offset(i, 0) = "='[simulering arbeidsplan ovn 4 30h.xls]lokklegging'!r" & cstr(11 + i) & "c16"     loktider.range("j4").offset(i, 0) = "='[simulering arbeidsplan ovn 4 30h.xls]lokklegging'!r" & cstr(11 + i) & "c15"   next    call reaktiver end sub  sub deaktiver()   application.enableevents = false   application.screenupdating = false   application.displaystatusbar = false   application.calculation = xlcalculationmanual   ' activesheet.displaypagebreaks = true 'note sheet-level setting end sub  sub reaktiver()   application.enableevents = true   application.screenupdating = true   application.displaystatusbar = true   application.calculation = xlcalculationautomatic   ' activesheet.displaypagebreaks = true 'note sheet-level setting end sub

it works correctly, takes quite while run. won't problem, won't need update link-addresses often, still wondered if there more efficient way this, using kind of relative reference input addresses each column @ once instance? quite lot of googling indicates "no", figured wouldn't hurt ask.

i can imagine quite slow, loop entering cell values individual in 10 columns 741 iterations, around 7.4k cell entries done individually.

you either create cell values in array , paste array workbook, or more enter expected formulae in top row , fill down appropriate.

as example replace:

  = 0 740     loktider.range("c4").offset(i, 0) = "='[simulering arbeidsplan ovn 3 28h.xls]lokklegging'!r" & cstr(11 + i) & "c6"     loktider.range("d4").offset(i, 0) = "='[simulering arbeidsplan ovn 3 28h.xls]lokklegging'!r" & cstr(11 + i) & "c5"     loktider.range("e4").offset(i, 0) = "='[simulering arbeidsplan ovn 3 28h.xls]lokklegging'!r" & cstr(11 + i) & "c16"     loktider.range("f4").offset(i, 0) = "='[simulering arbeidsplan ovn 3 28h.xls]lokklegging'!r" & cstr(11 + i) & "c15"     loktider.range("g4").offset(i, 0) = "='[simulering arbeidsplan ovn 4 30h.xls]lokklegging'!r" & cstr(11 + i) & "c6"     loktider.range("h4").offset(i, 0) = "='[simulering arbeidsplan ovn 4 30h.xls]lokklegging'!r" & cstr(11 + i) & "c5"     loktider.range("i4").offset(i, 0) = "='[simulering arbeidsplan ovn 4 30h.xls]lokklegging'!r" & cstr(11 + i) & "c16"     loktider.range("j4").offset(i, 0) = "='[simulering arbeidsplan ovn 4 30h.xls]lokklegging'!r" & cstr(11 + i) & "c15"   next

with:

edit -- r1c1 reference system seems use absolute values, standard cell reference added instead

with loktider     .range("c4") = "='[simulering arbeidsplan ovn 3 28h.xls]lokklegging'!f11"     .range("d4") = "='[simulering arbeidsplan ovn 3 28h.xls]lokklegging'!e11"     .range("e4") = "='[simulering arbeidsplan ovn 3 28h.xls]lokklegging'!p11"     .range("f4") = "='[simulering arbeidsplan ovn 3 28h.xls]lokklegging'!o11"     .range("g4") = "='[simulering arbeidsplan ovn 4 30h.xls]lokklegging'!f11"     .range("h4") = "='[simulering arbeidsplan ovn 4 30h.xls]lokklegging'!e11"     .range("i4") = "='[simulering arbeidsplan ovn 4 30h.xls]lokklegging'!p11"     .range("j4") = "='[simulering arbeidsplan ovn 4 30h.xls]lokklegging'!o11" range(.cells(4,3),.cells(744,10)).filldown end

this on few assumptions, loktider worksheet variable you've created, , you'll using specific range hardcoded loop counter.


Comments

Post a Comment

Popular posts from this blog

php - failed to open stream: HTTP request failed! HTTP/1.0 400 Bad Request -

this code i'm using find latitude , longitude of place: function coords($address){ echo $url="http://maps.googleapis.com/maps/api/geocode/json?address=".$address; $json = file_get_contents(urlencode($url)); $data = json_decode($json, true); print_r($data['results'][0]['geometry']['location']['lat']); print_r($data['results'][0]['geometry']['location']['lng']); } however returns warning: failed open stream: http request failed! http/1.0 400 bad request if use above code in simple procedure works fine not in function. note: have tried curl_init()... method produced same result? instead of encoding whole url encode address part. following code work fine $add='jamshoro phase 2'; $url="http://maps.googleapis.com/maps/api/geocode/json?address=".urlencode($add); $json = file_get_contents($url); $data = json_decode($json, true); print_r($data['r
Read more

java - How to filter a backspace keyboard input -

i have code gives message when key number pressed: txtvalueofclause.addeventfilter(keyevent.key_typed, new eventhandler<keyevent>() { @override public void handle(keyevent t) { char ar[] = t.getcharacter().tochararray(); char ch = ar[t.getcharacter().tochararray().length - 1]; if (!(ch >= '0' && ch <= '9')) { system.out.println("the char entered not number"); t.consume(); } } }); now if press on wrong button , press backspace remove it, error message. how can add backspace input if statement? i have found answer: txtvalueofclause.addeventfilter(keyevent.key_typed, new eventhandler<keyevent>() { @override public void handle(keyevent t) { char ar[] = t.getcharacter().tochararray(); char ch = ar[t.getcharacter().tochar
Read more

java - Show Soft Keyboard when EditText Appears -

i have code in android app, alert dialog , keyboard, , when dialog shown want keyboard appear. not , not sure why? here code: final alertdialog.builder alert = new alertdialog.builder(this); alert.settitle("title"); alert.setmessage("message"); final edittext input = new edittext(this); input.setinputtype(inputtype.type_class_number); alert.setview(input); input.setonfocuschangelistener(new view.onfocuschangelistener() { @override public void onfocuschange(view v, boolean hasfocus) { if (hasfocus) { log.i(tag, "in focus"); inputmethodmanager inputmethodmanager = (inputmethodmanager) getsystemservice(context.input_method_service); inputmethodmanager.showsoftinput(input, inputmethodmanager.show_implicit); } } }); but keyboard not appear though edit text in focus, , tell appear why, doesn't come up? thanks in advance.
Read more