LucasLondon
08-25-2010, 03:49 AM
I've been able to set up a macro below to extract data from an external (orcale databse) into excel (the active sheet) using the macro recorder and this works fine.
However instead of creating a new database query in a new sheet everytime, I would simply like to refresh the existing query that sits on an existing sheet (e.g sheet called rawdata1) but not sure how to adopt the code to achieve this.
Anyone got any ideas? In terms of where I would like to go with this, long-term I would like to run a single macro to refresh all of the external database queries in the workbook.
Hope someone can help.
Thanks,
Lucas
-------------------------
Sub TEST_QUERY()
'
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DRIVER={Oracle in OraDb11g_home1};SERVER=OBUDW2D;UID=JAMMA;PWD=TESTING;DBQ=OBUDW2D;DBA=W;APA= T;EXC=F;XSM=Default;FEN=T;QTO=T;FRC=10;FDL=" _
), Array( _
"10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=Me;C SR=F;FWC=F;FBS=60000;TLO=O;" _
)), Destination:=Range("A1"))
.CommandText = Array("select * from ACTIVITY_REPORTS")
.Name = "Query from registrations Data"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub
However instead of creating a new database query in a new sheet everytime, I would simply like to refresh the existing query that sits on an existing sheet (e.g sheet called rawdata1) but not sure how to adopt the code to achieve this.
Anyone got any ideas? In terms of where I would like to go with this, long-term I would like to run a single macro to refresh all of the external database queries in the workbook.
Hope someone can help.
Thanks,
Lucas
-------------------------
Sub TEST_QUERY()
'
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DRIVER={Oracle in OraDb11g_home1};SERVER=OBUDW2D;UID=JAMMA;PWD=TESTING;DBQ=OBUDW2D;DBA=W;APA= T;EXC=F;XSM=Default;FEN=T;QTO=T;FRC=10;FDL=" _
), Array( _
"10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=Me;C SR=F;FWC=F;FBS=60000;TLO=O;" _
)), Destination:=Range("A1"))
.CommandText = Array("select * from ACTIVITY_REPORTS")
.Name = "Query from registrations Data"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub