vba - Speed up Excel Macro as Opening -


so whole scope of excel file is copy , paste other 27 external files current excel file 1 one. show mean, following code examples , stimulated capture picture.enter image description here

macros(line # including space line):

1. importing sub

in file, have 27 subs this. it's longer example. real macro has 179 lines total. in example, has 51 lines.

the thing changed row numbers word row in vba code in line 6. 

    sub import_nj()      dim row integer, pathfileopen string, namefileopen string,      typefileopen string, fullfilename string, tabcopy string, modelfilename string      let row = worksheets("control_table").cells("2", "d").value     let pathfileopen = worksheets("control_table").cells(row, "a").text     let namefileopen = worksheets("control_table").cells(row, "b").text     let typefileopen = worksheets("control_table").cells(row, "c").text     let fullfilename = pathfileopen & "\" & namefileopen & typefileopen     let tabcopy = worksheets("control_table").cells(row, "j").text     let modelfilename = worksheets("control_table").cells("10", "b").text          application.asktoupdatelinks = false         application.displayalerts = false         application.calculation = xlcalculationmanual         workbooks.open filename:=fullfilename, updatelinks:=0      'copy income statement         workbooks(namefileopen).worksheets("total_reports").cells("9", "c").resize(5, 120).copy         'revenues         workbooks(modelfilename).worksheets(tabcopy).cells("4", "aw").resize(5, 120).pastespecial xlpastevalues         workbooks(namefileopen).worksheets("total_reports").cells("18", "c").resize(4, 120).copy        'prod costs         workbooks(modelfilename).worksheets(tabcopy).cells("11", "aw").resize(4, 120).pastespecial xlpastevalues         workbooks(namefileopen).worksheets("total_reports").cells("25", "c").resize(26, 120).copy       'employee related thru maintenance         workbooks(modelfilename).worksheets(tabcopy).cells("17", "aw").resize(26, 120).pastespecial xlpastevalues         workbooks(namefileopen).worksheets("total_reports").cells("53", "c").resize(3, 120).copy       'd&a         workbooks(modelfilename).worksheets(tabcopy).cells("46", "aw").resize(3, 120).pastespecial xlpastevalues            application.cutcopymode = false         workbooks(namefileopen).close         application.displayalerts = true      end sub
  1. batch import sub

although shows 7 callings, have 27 calling in file

    sub batch_import()     application          call import_nj            call import_md          call import_pa            call import_okc          call import_ca             call import_hi          call import_in          end          application.calculation = xlcalculationautomatic         activeworkbook.save         application.displayalerts = true          msgbox _         ("batch loading completed.")      end sub

what tried:

  1. turn off automatic calculation in each sub, see in first example macro. , others application many could.

  2. i didn't shut down screen updating since manager wants see it.

  3. i activate automatic calculating @ end of patch sub.

i guess reseason slowing down whole process have more 27 subs in module.also, there bunch of formulas filled in worksheets.

are there ways speed macro regarding opening file , running it? let me know if need elabrate more on question. in advance , read through question. : )

you pasting values; transfer values directly instead , remove clipboard consideration. either source or target referenced within ... end with.

this,

    workbooks(namefileopen).worksheets("total_reports").cells("9", "c").resize(5, 120).copy         'revenues     workbooks(modelfilename).worksheets(tabcopy).cells("4", "aw").resize(5, 120).pastespecial xlpastevalues     workbooks(namefileopen).worksheets("total_reports").cells("18", "c").resize(4, 120).copy        'prod costs     workbooks(modelfilename).worksheets(tabcopy).cells("11", "aw").resize(4, 120).pastespecial xlpastevalues     workbooks(namefileopen).worksheets("total_reports").cells("25", "c").resize(26, 120).copy       'employee related thru maintenance     workbooks(modelfilename).worksheets(tabcopy).cells("17", "aw").resize(26, 120).pastespecial xlpastevalues     workbooks(namefileopen).worksheets("total_reports").cells("53", "c").resize(3, 120).copy       'd&a     workbooks(modelfilename).worksheets(tabcopy).cells("46", "aw").resize(3, 120).pastespecial xlpastevalues

becomes,

with workbooks(namefileopen).worksheets("total_reports")     workbooks(modelfilename).worksheets(tabcopy).cells("4", "aw").resize(5, 120) = _         .cells("9", "c").resize(5, 120).value2         'revenues     workbooks(modelfilename).worksheets(tabcopy).cells("11", "aw").resize(4, 120) = _         .cells("18", "c").resize(4, 120).value2        'prod costs     workbooks(modelfilename).worksheets(tabcopy).cells("17", "aw").resize(26, 120) = _         .cells("25", "c").resize(26, 120).value2       'employee related thru maintenance     workbooks(modelfilename).worksheets(tabcopy).cells("46", "aw") = _         .cells("53", "c").resize(3, 120).value2        'd&a end

as mentioned in comments, if external files @ or on 1mb filesize area, save them .xlsb (excel binary) reduce load time.


Comments

Post a Comment

Popular posts from this blog

android - InAppBilling registering BroadcastReceiver in AndroidManifest -

i'm implementing inapp billing in android app. works well, however, i'm trying decouple broadcast receiver activity manifest. suggestion in android's trivialdrive sample: // important: dynamically register broadcast messages updated purchases. // register receiver here instead of <receiver> in manifest // because call getpurchases() @ startup, therefore can ignore // broadcasts sent while app isn't running. // note: registering listener in activity bad idea, done here // because sample. regardless, receiver must registered after // iabhelper setup, before first call getpurchases(). currently there's class extends broadcastreceiver : public class iabbroadcastreceiver extends broadcastreceiver { /** * intent action receiver should filter for. */ public static final string action = "com.android.vending.billing.purchases_updated"; private final iabbroadcastlistener mlistener; public iabbroadcastreceiver(iabbroadcastlistener listener) { m...
Read more

python Tkinter Capturing keyboard events save as one single string -

is possible store keyboard events 1 string? code below stores 1 char , prints it. card reader or bar code reader, contains collection of character/ string not 1 character @ time. goal save char pressed text variable. from tkinter import * root = tk() def key(event): text= event.char text+= event.char print ("pressed", text) def callback(event): frame.focus_set() print ("clicked at", event.x, event.y) frame = frame(root, width=100, height=100) frame.bind("<key>", key) frame.bind("<button-1>", callback) frame.pack() root.mainloop() currently, creating text variable , everytime key() function gets called, , text stores last character typed. you can define text module level variable , , use module level text inside key function - text = '' def key(event): global text text+= event.char print("pressed", text)
Read more

sql server - Why does Linq-to-SQL add unnecessary COUNT()? -

here tables: create table customer ( cid int primary key, name varchar(32) ) create table ord ( oid int primary key, cid int foreign key references customer, address varchar(20) ) here's linq-to-sql statement: var aaa = c in db.customer select new { c, o = c.ord.tolist() }; and here's inexplicable query generated linq-to-sql: select [t0].[cid] [cid], [t0].[name] [name], [t1].[oid] [oid], [t1].[cid] [cid2], [t1].[address] [address], (select count(*) [dbo].[ord] [t2] [t2].[cid] = [t0].[cid]) [value] [dbo].[customer] [t0] left outer join [dbo].[ord] [t1] on [t1].[cid] = [t0].[cid] order [t0].[cid], [t1].[oid] i want understand how rid of count(*) part. it's entirely uncalled for! linq needs count(*) in order determine number of entries returned o = c.ord.tolist() .
Read more