I'm looking for some help running an excel macro as an automated task. The macro is supposed to create a data connection, import some data from a particular database, apply some formatting and formulas, and then save a copy of the file to a network drive. I would like to have this excel file opened automatically every night by the Windows 2003 Server and have the events run on open and excel close, and this work perfectly fine as long as I am logged into the server and actively connected via remote desktop. Where I run into problems is when the task runs overnight as scheduled the macro errors and excel gets stuck open, preventing my other excel scheduled tasks from running. I know that the issue isn't the data connections because I have a few other macros that simply refesh connections and they work fine. It is only my macros that create the connections that seem to error. Any help would be appreciated. Thanks.

Hi, I am assuming that Excel hangs because the macro returns an error and is waiting for user input. Without seeing the code and knowing which line causes the error, it is not possible to make any suggestions. Also please state what error message is showing. Regards

Thats exactly what is happening. Its just seems strange the the error only occurs when I'm not connected to the server via remote desktop (even if the account is still logged in the error occurs).The error I am recieving is "Run-time error '1004": Applications-defined or object defined error. The code that it seems to be failing on is: Sub Run() Call Get_Data Call Fill_Empty Call Add_Columns Call Rename_Columns Call Formulas Call Format Call Format_Final End Sub Sub Get_Data() Sheet1.Cells.ClearContents Sheet1.Cells.ClearFormats With Sheet1.ListObjects.Add(SourceType:=0, Source:=Array(Array( _ "ODBC;DSN=IncomeChek;DATABASE=rrvc_test_production;SERVER=10.1.1.111;PORT=5432;UID=test;;SSLmode=disable;ReadOnly=0;Protocol=7." _ ), Array( _ "4;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;ShowSystemTables=0;ConnSettings=;Fetch=100;Socket=4096;UnknownSizes=0;MaxVarcha" _ ), Array( _ "rSize=255;MaxLongVarcharSize=8190;Debug=0;CommLog=0;Optimizer=0;Ksqo=1;UseDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarch" _ ), Array( _ "ar=0;BoolsAsChar=1;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;;LFConversion=1;UpdatableCursors=1;DisallowPremature=0;T" _ ), Array( _ "rueIsMinus1=0;BI=0;ByteaAsLongVarBinary=0;UseServerSidePrepare=0;LowerCaseIdentifier=0;XaOpt=1" _ )), Destination:=Range("$A$1")).QueryTable .CommandText = Array( _ "SELECT customer.name , customer.businessnumber , customer.rapid_rate , customer.basic_rate ," & Chr(13) & "" & Chr(10) & "COUNT(CASE WHEN orderitems.order_date BETWEEN DATE_TRUNC('MONTH', CURRENT_TIMESTAMP - INTERVAL '1 MONTH') " _ , _ "AND" & Chr(13) & "" & Chr(10) & "(CURRENT_TIMESTAMP - INTERVAL '1 MONTH') THEN orderitems.customer_id ELSE NULL END) ," & Chr(13) & "" & Chr(10) & "COUNT(CASE WHEN orderitems.order_date BETWEEN DATE_TRUNC('MONTH', CURRENT_TIMESTAMP) AND" & Chr(13) & "" & Chr(10) & "(CURRENT_TIMESTAMP" _ , _ ") THEN orderitems.customer_id ELSE NULL END) ," & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "AVG(CASE WHEN orderitems.order_date BETWEEN DATE_TRUNC('MONTH', CURRENT_TIMESTAMP - INTERVAL '1 MONTH') AND" & Chr(13) & "" & Chr(10) & "(CURRENT_TIMESTAMP - INTERVAL '1 MONTH'" _ , _ ") THEN orderitems.sellprice ELSE NULL END) ," & Chr(13) & "" & Chr(10) & "AVG(CASE WHEN orderitems.order_date BETWEEN DATE_TRUNC('MONTH', CURRENT_TIMESTAMP) AND" & Chr(13) & "" & Chr(10) & "(CURRENT_TIMESTAMP) THEN orderitems.sellprice ELSE NULL END) ," & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "" _ , _ "" & Chr(13) & "" & Chr(10) & "SUM(CASE WHEN orderitems.order_date BETWEEN DATE_TRUNC('MONTH', CURRENT_TIMESTAMP - INTERVAL '1 MONTH') AND" & Chr(13) & "" & Chr(10) & "(CURRENT_TIMESTAMP - INTERVAL '1 MONTH') THEN orderitems.sellprice ELSE NULL END) ," & Chr(13) & "" & Chr(10) & "SUM(" _ , _ "CASE WHEN orderitems.order_date BETWEEN DATE_TRUNC('MONTH', CURRENT_TIMESTAMP) AND" & Chr(13) & "" & Chr(10) & "(CURRENT_TIMESTAMP) THEN orderitems.sellprice ELSE NULL END) " & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "FROM " & Chr(13) & "" & Chr(10) & "orderitems, " & Chr(13) & "" & Chr(10) & "customer" & Chr(13) & "" & Chr(10) & "WHERE" & Chr(13) & "" & Chr(10) & "(orderitems" _ , _ ".status = 'Success' OR orderitems.status = 'Rejected by IRS') AND" & Chr(13) & "" & Chr(10) & "customer.id = orderitems.customer_id" & Chr(13) & "" & Chr(10) & "GROUP BY" & Chr(13) & "" & Chr(10) & "customer.name," & Chr(13) & "" & Chr(10) & "customer.businessnumber," & Chr(13) & "" & Chr(10) & "customer.rapid_rate," & Chr(13) & "" & Chr(10) & "customer.basic_rate" & Chr(13) & "" & Chr(10) & "" _ , _ "HAVING" & Chr(13) & "" & Chr(10) & "COUNT(CASE WHEN orderitems.order_date BETWEEN DATE_TRUNC('MONTH', CURRENT_TIMESTAMP - INTERVAL '1 MONTH') AND" & Chr(13) & "" & Chr(10) & "(CURRENT_TIMESTAMP - INTERVAL '1 MONTH') THEN orderitems.customer_id ELSE NULL EN" _ , _ "D) > 0 OR" & Chr(13) & "" & Chr(10) & "COUNT(CASE WHEN orderitems.order_date BETWEEN DATE_TRUNC('MONTH', CURRENT_TIMESTAMP) AND" & Chr(13) & "" & Chr(10) & "(CURRENT_TIMESTAMP) THEN orderitems.customer_id ELSE NULL END) > 0" & Chr(13) & "" & Chr(10) & "ORDER BY" & Chr(13) & "" & Chr(10) & "customer.name " _ ) .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = "Table_Query_from_IncomeChek_DB_1" .Refresh BackgroundQuery:=False End With End Sub I have code similar to this in three sheet and call them in the on_open private sub. It seems strange because I have several other excel files that work similarly to this that run without any problems.

Hi, I have no experience of ODBC access, but as the macro fails at the Get_Data sub, and as this starts with setting up the access to the server with the database, this sounds like a permissions issue. I can't see any obvious code errors, and of course this sub runs OK when you are logged on, so the code itself is likely OK. Hopefully others will comment. Sorry - not much help! Regards