Run Excel Macro as Automated Task on Server

Microsoft Excel 2007
October 27, 2009 at 08:38:12
Specs: Windows Server 2003 R2
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.


See More: Run Excel Macro as Automated Task on Server

Report •


#1
October 28, 2009 at 05:45:49
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


Report •

#2
October 28, 2009 at 07:08:39
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.


Report •

#3
October 28, 2009 at 09:26:58
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


Report •

Related Solutions


Ask Question