Refreshing External Database Query In Excel using VBA

[LucasLondon]

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

-------------------------


[VBA]
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; CSR=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
[/VBA]

[p45cal]

Not tested, but I think it's something like:

ActiveSheet.QueryTables("Query from registrations Data").Refresh BackgroundQuery:=False

and you might be able to omit the BackgroundQuery:=False bit.

[LucasLondon]

Hi,

Thanks for the code p45cal. I can confirm that it works even after removing the BackgroundQuery:=False line. However I need to manually enter my username and password in order to run the query/connect to the external source.

Does anyone know how I can specfiy the user ID and Password in the code? For example in the orginal query the key parameters were recorded in the following line:

SERVER=OBUDW2D;UID=JAMMA;PWD=TESTING;DBQ=OBUDW2D

Thanks,

Lucas