PDA

View Full Version : Refreshing External Database Query In Excel using VBA



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

p45cal
08-25-2010, 05:41 AM
Not tested, but I think it's something like:

ActiveSheet.QueryTables("Query from registrations Data").Refresh BackgroundQuery:=Falseand you might be able to omit the BackgroundQuery:=False bit.

LucasLondon
08-27-2010, 03:52 AM
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