Home

Excel macro runs 1/12th as fast when run from Access

%3CLINGO-SUB%20id%3D%22lingo-sub-995449%22%20slang%3D%22en-US%22%3EExcel%20macro%20runs%201%2F12th%20as%20fast%20when%20run%20from%20Access%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-995449%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20macro%20that%20I've%20written%20to%20%22reformat%22%20some%20data%20in%20Excel%20(not%20font%20types%20and%20colors%20and%20such%2C%20but%20how%20the%20data%20is%20laid%20out%20in%20the%20spreadsheet).%20I%20found%20a%20macro%20that%20records%20how%20long%20it%20takes%20a%20macro%20to%20run%2C%20and%20that%20macro%20consistently%20runs%20for%20about%2030%20seconds.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20problem%20is%20that%20the%20reason%20I'm%20reformatting%20the%20data%20is%20to%20import%20into%20Access%2C%20and%20I%20don't%20want%20my%20users%20to%20have%20to%20have%20intimate%20knowledge%20of%20Access%20or%20Excel%20to%20reformat%20the%20data%20and%20import%20it%20into%20the%20Access%20database.%20Thus%2C%20I've%20created%20a%20form%20with%20a%20button%20that%20allows%20the%20user%20to%20select%20a%20file%20from%20a%20dialog%20box%2C%20and%20then%20the%20macro%20runs%20on%20that%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20for%20some%20reason%2C%20when%20done%20this%20way%2C%20the%20macro%20takes%20upwards%20of%206%20minutes.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20don't%20know%20if%20it%20would%20be%20helpful%20to%20post%20the%20code%20of%20the%20macro%20here%2C%20or%20if%20there's%20some%20generic%20advice%20that%20will%20help.%20I%20just%20can't%20imagine%20why%20this%20increase%20in%20run-time%20is%20happening.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-995449%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3E2016%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1000512%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20macro%20runs%201%2F12th%20as%20fast%20when%20run%20from%20Access%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1000512%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F406098%22%20target%3D%22_blank%22%3E%40jamesson_kaupanger%3C%2FA%3E%26nbsp%3BWell%20Excel%20Automation%20is%20not%20that%20fast...thats%20a%20fact...try%20hiding%20the%20Excel%20while%20performing%20the%20manipulation...this%20should%20help%20in%20performance..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1003039%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20macro%20runs%201%2F12th%20as%20fast%20when%20run%20from%20Access%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1003039%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F401564%22%20target%3D%22_blank%22%3E%40tsgiannis%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETurning%20ScreenUpdate%20off%20doesn't%20seem%20to%20help%2C%20at%20least%20not%20much.%3C%2FP%3E%3C%2FLINGO-BODY%3E
jamesson_kaupanger
Contributor

I have a macro that I've written to "reformat" some data in Excel (not font types and colors and such, but how the data is laid out in the spreadsheet). I found a macro that records how long it takes a macro to run, and that macro consistently runs for about 30 seconds.

 

The problem is that the reason I'm reformatting the data is to import into Access, and I don't want my users to have to have intimate knowledge of Access or Excel to reformat the data and import it into the Access database. Thus, I've created a form with a button that allows the user to select a file from a dialog box, and then the macro runs on that file.

 

And for some reason, when done this way, the macro takes upwards of 6 minutes.

 

I don't know if it would be helpful to post the code of the macro here, or if there's some generic advice that will help. I just can't imagine why this increase in run-time is happening.

2 Replies

@jamesson_kaupanger Well Excel Automation is not that fast...thats a fact...try hiding the Excel while performing the manipulation...this should help in performance..

 

@tsgiannis 

 

Turning ScreenUpdate off doesn't seem to help, at least not much.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies