Scheduling an excel data update and email best practices

%3CLINGO-SUB%20id%3D%22lingo-sub-1795779%22%20slang%3D%22en-US%22%3EScheduling%20an%20excel%20data%20update%20and%20email%20best%20practices%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1795779%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20have%20set%20up%20multiple%20spreadsheets%20over%20the%20years%20that%20pull%20data%20in%20from%20either%20a%20web%20page%20or%20SQL%20database.%26nbsp%3B%20I%20pull%20the%20data%20into%20a%20table%20and%20add%20some%20calculations%20to%20the%20right%20of%20the%20data.%26nbsp%3B%20In%20some%20workbooks%20data%20from%20one%20table%20is%20copied%20and%20pasted%20into%20a%20new%20sheet%20as%20a%20record%20of%20the%20prior%20weeks%20data%20points.%26nbsp%3B%20Then%20I%20either%20save%20the%20file%20and%20close%2C%20or%20email%20a%20view%20of%20the%20data%20to%20myself%20and%2For%20some%20co-workers.%26nbsp%3B%20I'm%20being%20general%2C%20but%20in%20short%20these%20always%20work%20for%20a%20while%20then%20break.%26nbsp%3B%20I've%20started%20keeping%20an%20update%20log%20in%20the%20files%20and%20it%20seems%20if%20the%20notebook%20is%20opened%2Fupdated%20daily%20then%20it%20breaks%20about%20once%20a%20month.%26nbsp%3B%20By%20break%20I%20mean%20the%20file%20stops%20behaving%20correctly.%26nbsp%3B%20Cell%20formats%20are%20lost%20or%20some%20amount%20of%20data%20is%20note%20represented%20or%20updating%20correctly.%26nbsp%3B%20The%20file%20doesn't%20say%20it's%20corrupt.%26nbsp%3B%20Usually%20I%20am%20able%20to%20restore%20normal%20behavior%20by%20restoring%20a%20prior%20revision.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20regular%20enough%20create%20trouble%20but%20not%20a%20deterrent%20for%20creating%20these%20tools.%26nbsp%3B%20It's%20made%20me%20wonder%20if%20I'm%20doing%20something%20that%20is%20known%20bad%20practice.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20using%20Task%20Scheduler%20to%20open%20the%20given%20excel%20workbook%20around%205am.%26nbsp%3B%20And%20then%20the%20Auto_Open%20macro%20does%20the%20rest%20(updates%2C%20data%20copying%2C%20emails).%20File%20size%20isn't%20that%20large%20(1.1MB).%26nbsp%3B%20If%20my%20PC%20is%20off%20at%205am%20then%20Task%20Scheduler%20is%20set%20to%20run%20the%20next%20opportunity%20it%20gets%20and%20usually%20will%20about%205-10%20minutes%20after%20I%20start%20up.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELooking%20for%20any%20general%20guidance%20to%20make%20my%20excel%20workbooks%20more%20reliable.%3C%2FP%3E%3CP%3EAm%20I%20asking%20one%20workbook%20to%20do%20too%20many%20things%3F%3C%2FP%3E%3CP%3EIs%20it%20expected%20that%20a%20workbook%20fails%20after%20too%20much%20use%3F%26nbsp%3B%20i.e.%20do%20issues%20just%20pile%20up%20in%20the%20background%3F%3C%2FP%3E%3CP%3EIs%20using%20Task%20Scheduler%20somehow%20related%20to%20the%20issues%20I%20see%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1795779%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Occasional Visitor

Hi, I have set up multiple spreadsheets over the years that pull data in from either a web page or SQL database.  I pull the data into a table and add some calculations to the right of the data.  In some workbooks data from one table is copied and pasted into a new sheet as a record of the prior weeks data points.  Then I either save the file and close, or email a view of the data to myself and/or some co-workers.  I'm being general, but in short these always work for a while then break.  I've started keeping an update log in the files and it seems if the notebook is opened/updated daily then it breaks about once a month.  By break I mean the file stops behaving correctly.  Cell formats are lost or some amount of data is note represented or updating correctly.  The file doesn't say it's corrupt.  Usually I am able to restore normal behavior by restoring a prior revision. 

 

This is regular enough create trouble but not a deterrent for creating these tools.  It's made me wonder if I'm doing something that is known bad practice.

 

I'm using Task Scheduler to open the given excel workbook around 5am.  And then the Auto_Open macro does the rest (updates, data copying, emails). File size isn't that large (1.1MB).  If my PC is off at 5am then Task Scheduler is set to run the next opportunity it gets and usually will about 5-10 minutes after I start up.

 

Looking for any general guidance to make my excel workbooks more reliable.

Am I asking one workbook to do too many things?

Is it expected that a workbook fails after too much use?  i.e. do issues just pile up in the background?

Is using Task Scheduler somehow related to the issues I see?

 

 

0 Replies