Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remover ODBC Dialog Box
I have a macro that creates a Pivot Table using ODBC connection. Everyting I
run it, the dialog box pops up and I must choose the MACHINE DATA SOURCE tab and then select the correct ODBC connection. How do I remove this from displaying each time this macro is run? Here is the code: Sub Create_Pivot_Table() ' ' ' Range("A6").Select Workbooks("Book1").Connections.Add "ARSYSTEM SITE_PATIENT_REGISTRATION5", "", _ Array(Array( _ "ODBC;DSN=Dim;Description=Dim;UID=usr;APP=2007 Microsoft Office system;WSID=D820_XP_09;DATABASE=Medical;AutoTransl ate=No;Trusted_Conn" _ ), Array("ection=Yes;UseProcForPrepare=0;QuotedId=No; AnsiNPW=No")), Array( _ "EXECUTE [dbo].[SITE_SP_PATIENT_MATRIX] 'MAIN','2007-04-01','2007-04-14'"), 2 ActiveWorkbook.PivotCaches.Create(SourceType:=xlEx ternal, SourceData:= _ ActiveWorkbook.Connections("ARSYSTEM SITE_PATIENT_REGISTRATION5"), Version:= _ xlPivotTableVersion12).CreatePivotTable TableDestination:="Sheet1!R6C1", _ TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion12 Cells(6, 1).Select With ActiveSheet.PivotTables("PivotTable1").PivotFields ("DATE_ADDED") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Account_and_Name") .Orientation = xlRowField .Position = 2 End With With ActiveSheet.PivotTables("PivotTable1").PivotFields ("ACTION_TYPE") .Orientation = xlColumnField .Position = 1 End With With ActiveSheet.PivotTables("PivotTable1").PivotFields ("USERID") .Orientation = xlColumnField .Position = 2 End With ActiveSheet.PivotTables("PivotTable1").AddDataFiel d ActiveSheet.PivotTables( _ "PivotTable1").PivotFields("TRX_COUNT"), "Sum of TRX_COUNT", xlSum End Sub Thanks, MEG |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Duplicate Remover | Excel Discussion (Misc queries) | |||
Remover number from one sheet to another | Excel Discussion (Misc queries) | |||
unique item remover | Excel Discussion (Misc queries) | |||
Duplicate remover | Excel Programming | |||
Duplicate remover | Excel Programming |