Hello to all!
Hope everyone is well
Ihave a question here which has been baffling me for quite some while.
I have an excel spreadsheet which runs multiple extracts using the import external data function in excel.
This gets the data from access, but then, I think it goes from access and tries to get data from an oracle database because a screen pops up and asks me for an oracle password.
When the password is entered the data is then returned to excel.
The only problem with this is if i have to run 20 different extracts, i have to enter the oracle password 20 times
Here is the below code, which i got from recording a macro in excel.
It is probably something simple but i am quite new to this.
I have tried putting UID and PWD in the connection string but it doesnt seem to be working. I have probably done something wrong :-P
If anyone could help me i would be extremely thankful!!!
Code:
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DBQ=\\intranet\FIF\allshare_7\Omni\users_standby.mdb;DefaultDir=\\intranet\FIF\allshare_7\Omni;Driver={Driver " _
), Array( _
"do Microsoft Access (*.mdb)};DriverId=25; FIL=MS Access;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Thread;" _
), Array("s=3;UserCommitSync=Yes;")), Destination:=Range("A10"))
.CommandText = Array( _
"SELECT Extract_Query.customer_id, Extract_Query.FirstName, Extract_Query.Surname, & Chr(13) & "" & Chr(10) & "FROM `users_standby.mdb`.Extract_Query Extract_Query" & Chr(13) & "" & Chr(10) & _
"WHERE (Extract_Query.Customer_id='" & CusId & "') AND (Extract_Query.Surname = " & surname & ")")
.Name = "Extract_Query"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
I am hoping it is something simple that happens all the time....
Any help would be appreciated
thank you very much :-)