VBA Set ODBC Connection String For Query
My workbook has 9 queries, all needing the same string. I would like to have a msgbox pop up, let them enter the directory name, and have that change all the odbc conneciton strings automatically. Can anyone point me in the right direction?
The message box would be a nice touch, but is not necessary, I can change it in the VBA editor if needed.
Currently, they connect using a Visual FoxPro ODBC driver. We setup the connection when we made the queries. Now that they are in on the worksheets, I want to be able to edit them with VBA instead of using the script editor (which is very slow).
View Complete Thread with Replies
Related Forum Messages:
ODBC Connection To An Oracle Database
I am currently querying data from an Oracle database through MS Access and then passing it to Excel for the user to work. Not all users have MS Access so I would like to automate the data pull from Excel
The DNS is: a150
The Tables are:
Sample SQL string would be:
Running Access Query In Excel And Connection
I am not sure how to setup the initial connection to the datbase from excel and was wondering what would be the best way to go about things. Is it possible to run the query stored in the database and pass it a value or would I be better off just coding it into a sqlstring within the macro.
Here is the query that is in the database...when run it asks for the "Status?" variable.
SELECT ordernumber, mobilenumber
WHERE status = [status?];
So yes, really simple query but yeah just want to know how to pass it that variable from excel or can I just set it up in the actual macro to maybe read the status from a cell?
Pivot Refresh After ODBC Query Complete Refresh
I have a query with ODBC connection to a SQL database. This query's parameter is linked to a cell. The resultant data is the source for a pivot table. I want to refresh the pivot table, when the query is run. I've tried using the cell that triggers the query....but the problem is that the query takes about 10 seconds to run. By the time the query returns new data....the pivot has already refreshed. I need it to refresh AFTER the query is complete.
I tried adding a cell that sums up the data from the query...thinking when THAT changes (due to updated data), to trigger the pivot refresh. Problem is that I don't know the trigger for when the sum cell changes (ie....formula change, not typed in.)
VBA Coding To Detect Internet Connection
Afternoon All - i have an excel workbook that runs on 20 remote machines that i do not have direct access to - i am placing upgrades on the net but looking at finding a way for excel to detect the update what i need it to do first is check if the machine is connected to the internet.
Query An Access Query With VBA Function
I'm trying to query a query in Access 2003, from Excel 2003.
The query in Access looks like:
AccessQuery: [SELECT VBAFunction(field1) FROM Table]
The query in Excel looks like:
ExcelQuery: [SELECT * FROM AccessQuery]
I use the following VBA code in Excel to excecute the query:
With ThisWorkbook.Worksheets(cDataSheetName).QueryTables.Add(Connection:=strConnection, _
.RowNumbers = True
iResultRowCount = .ResultRange.Rows.Count
When I execute this code I get the error message 'SQL Syntax Error' (Error 1004). When I remove the VBA function from the query in Access, it all works fine.
To Set Up A Web Query That Gets The Player Roster
I'm trying to set up a web query that gets the Player Roster from Indians.com and one column is giving me trouble.
On the web site, this column is the player's height, listed as, for example, 6-2.
When the web query import is complete, this column displays a date instead of the height.... so 6-2 comes in as 2-June.
How do I massage this column to display the height and not a date?
Numeric String Manipulation Query
I have a sizable spreadsheet, which has one column where the cells are mostly numeric strings of up to 6 numbers - there are variations, which I will mention.
What I need to do, is to treat the numbers as individual figures, and add them, then divide by the number of figures. e.g. cell value '123456' I need to process as (1+2+3+4+5+6)/6.
The variations are that '0' has to be added as 10, and any letters need to be added as 12. '-' and '/' which are the only non-alphanumeric values should be ignored.
Query Parameters: Editing The Middle Of A String
My question is in regards to query and setting up parameters. I have the following query (I am using an example for security purposes)
What I am trying to do is have a parameter for the user to change the AA21. For example the user could type AA33 and it would change the code to:
I am familiar with creating parameters and have been able to add things to the end of the string but not in the middle. I have been trying a very long time to figure this out.
Use String To Set As Commandbutton
I have a large number of commandbuttons on a userform that I wish to apply captions to via a for loop. Each button is numbered appropriately for this purpose. e.g.
Button_1, Button_2 etc etc
I want the loop to compile a string that represents the button name e.g.
String = "Button_" & Loop_Count
and then use my defined commandbutton:
Dim My_Button as commandbutton
to name the buttons. So the loop would be something like:
for Loop_Count = 1 to 100
String = "Userform_Name.Button_" & Loop_Count
set My_Button = String
My_Button.caption = "Test_" & Loop_Count
Unfortunately trying to set 'My_Button' using a string like this doesn't work.
How To Get Set Of Chars From A String To A Variable ..
This one has two sets of quotes, with the information from the noted post I can retrieve the string "S5a09b-90", now that I have a double set of brackets "( )" how do I get the value for the second set of brackets, "CAL_15"?
In the quoted string, CALIB/SENS,S, is constant. The first set of brackets are constant (string length is not), the second set of brackets are constant (string length is not).
Set A Workbook Variable As A String
I have a string variable contacting a file.
I want to set a workbook variable to this file, WITHOUT actually opening it!
DIM wb as Workbook
DIM filenm as String
filenm = "C: empdata.xls"
set workbook = filenm
So I know this won't work, but is there anyway around it? (bearing in mind I don't want to open the file!
Get Set Of Chars From A String To A Variable
I believe I should use the INSTR function, but I simply dont understand its use very well. I have a string in a cell, "MEAS/SPHERE,F(QUA_1),5", the characters I need start at the "(" and end at the closing ")", ref: (QUA_1). The start and end will always be the same, the length of characters and the spelling of characters be anything. Thus, QUA_1 can be CAL_1, CAL_2, SOMEOTHERTEXT_1 or 187bgo_199, etc, always enclosed in "( )". I wish to place the characters and the closing "( )" into a variable as a string.
Search String For Defined Set Of Characters
I have about 700 cells I want to interrogate. Within each cell the following text appears “Estimate – BOLB/02/1234 – Some more text here”. I want to search all the cells and delete the cell contents but leave the “BOLB/02/1234”.
The problem I have is that the string is different in each cell but the format is the same. It always starts with BOLB followed by /, followed by 2 numbers, followed by / followed by 4 numbers i.e. BOLB/**/****.
Connect To Access Using ODBC
how to do this with Java but not VBA, I have the capability of understanding it I just need to know where to look because I can't seem to find any solid information on the net from my searches. Does anyone have any websites, other threads or anything they could direct me to for this kind of thing.
I was contemplating putting this in the Access thread but I want to connect to Access through Excel so I think it fits both.
My plan is to use a macro to send specific cells to five seperate tables in an Access database using VBA and SQL.
ODBC Lookup To Populate Variable
I have no problem setting ODBC connections in vba, that return the results to a selected Range destination, but short of then setting txtVariable = Range("A1") is there any way to directly tell the ODBC connection to set the result of the query to txtVariable?
Connecting To A Database Without The Local Machines Odbc
I'm working on a spreadsheet that will connect to a firebird DB (Programmed using VBA and ADO intially).. The spreadsheet itself will reside on a server in a shared folder..
When a user opens the file up, I'd like for the spreadsheet to use the servers ODBC connection, and not the client machine.. This way we don't have to install the Firebird ODBC driver on each client workstation..
Index/match/vlookup And ODBC SQL
I want to create a formula that will use a database table as a lookup for index/match and vlookup type functions.
I have my ODBC connection connecting to a mysql database and am successfully able to run sql queries through excel.
Is there a way to get index/match/vlookup formulas to use the database table as the lookup range?
VBA Listbox Column Query
I have a database of customers in sheet1 and can search colA by using .find command and find part of text string entered in textbox1, results are filtered and shown in listbox1. my problem is the listbox is only showing 9 columns and i need 12.
i think this is because i am using the additem command.
can anyone suggest a way around this or alternative coding method??
Dim strFind As String 'what to find
Dim rFilter As Range 'range to search
Set rFilter = Sheet1.Range("a2", Range("f65536").End(xlUp))
Set rng = Sheet1.Range("a2", Range("a65536").End(xlUp))
strFind = Me.TextBox1.Value
If Not .AutoFilterMode Then .Range("A2").AutoFilter
rFilter.AutoFilter Field:=1, Criteria1:=strFind & "*"......................
VBA To Query A Access Database
i am trying to use VBA to query a access database that i have i want to be able to pull the information that matchs either textbox4 or 5 i am using the code below but i am getting global errors.
Private Sub CommandButton1_Click()
Dim custname As Variant
Dim Custnum As Variant
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
custname = Range("TextBox5").Text
Custnum = Range("TextBox4").Text
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"ODBC;DSN=Insolvency;Description=Insolvency;APP=Microsoft Office XP;DATABASE=Insolvency;Trusted_Connection=YES"), Destination:=Range("Listbox1"))
.CommandText = Array("SELECT * FROM POST WHERE Customer_Account_Name=" & custname & " AND Customer_Account_Number = '" & Custnum & "' ORDER BY Customer_Account_Number")
.Name = "Insolvency Post Query"..........................
Process Append Query Through VBA
I currently have a great VBA option to append records to the appropriate Access table. One problem I'm running into though is getting by the unique record number. Currently a user submits a record to a DataStorage tab which is then appended to the database. However, we would like to keep some of the old records (maybe a weeks worth: approx 100 records) on that tab in case any problems arise in the append transition. But we don't want duplicate records in the table.
Is there a way to modify the below code to kick out the duplicates and only bring in new records to the table? I will assign the table the requirement to only bring in unique new records but the code errors in Excel.
Dim cn As ADODB.Connection
Dim cm As ADODB.Command
dbPath = "C:Documents and SettingsUSERDesktop"
dbName = "MyAppendTest.mdb"
Set cn = New ADODB.Connection
Set cm = New ADODB.Command
.CommandTimeout = 0
.Provider = "Microsoft.Jet.OLEDB.4.0;"
.ConnectionString = "Data Source=" & dbPath & dbName
.CommandText = "MyApp"
.CommandType = adCmdStoredProc
.ActiveConnection = cn
MsgBox ("Append Update is Complete")
Filter New Database Query In VBA
I am trying to create a macro that will import data from an external database; however, I want to be able to filter the data using input from the end user. The code will work fine if I do not use a variable (i.e. I type "31" to replace the second instance of "Reply" below).
Dim Reply As Integer
Reply = InputBox("Week Number", " Find Week")
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=DataWarehouse;Description=DataWarehouse;UID=DataWarehouse;PWD=;APP=Microsoft® Query;WSID=WILLIAM;DATABASE=tmd" _
, Destination:=Sheets("RawData"). Range("A1"))
.CommandText = Array( _
"SELECT vOEEDownTime. …, " & Chr(13) & "" & Chr(10) & "FROM tmd.dbo.vO" _
, "EEDownTime vOEEDownTime" & Chr(13) & "" & Chr(10) & "WHERE (vOEEDownTime.downtimecode Is Not Null) And (vOEEDownTime.weekbucket = Reply)")
.Name = "Query from DataWarehouse"
Vba To Run Query Designer
I want to run my existing Query I wrote using Essbase Query Designer, in Excel using VBA. But all it does is open the Query Designer wizard for me, but doesn't wait for my response and proceeds to the next step. What should I do?
Currently I have:
qmenu = EssMenuVQueryDesigner()
If qmenu = 0 Then
MsgBox ("query successful!")
MsgBox ("Try again!")
And I get "Try again" message box. make this process simple is appreciated! Basically I want to open already saved Query and run that before retrieving data.
ODBC :: Importing External Data From An Oracle 9i Database
I'm having some issues importing external data from an Oracle 9i database.
Here is what is happening:
I am initially able to import data from the table I want to into Excel.
I do this through "Data -> Import External Data -> New Database Query" where I have my .ORA data source. I'm able to log in using my user and password and import the table. My problems come after I import this first set of data.
If I try to "Edit Query…" I get an error box that says "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
If I try to "Refresh Data" I get two error boxes. The first is just: "[Microsoft][ODBC driver for Oracle][Oracle]" and the next says: "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed". It then prompts me for the User name and password of the DB I'm connecting and after I enter the information, I get the same two error messages.
Also, if I attempt to establish another connection following the "Data -> Import External Data…" steps I run into a different problem. Upon entering the information for the User Name and password prompt I get two error messages. One says "!" and the next box says "Couldn't read this file". Once I click "OK" in those boxes, the Microsoft Query window automatically opens with nothing in it.
I can't seem to figure out why I can initially pull down data and then cannot succeed afterwards. I'm unable to connect to the DB I have through Excel until I completely closed all Microsoft Excel instances.
I also use this data source in Microsoft Access without any problems.
Vba Calling An Access Query With Parameter
i have code accessing an access database running a "query" which has been defined in the database itself. i asm using ADO - no issues with this. works very well.
my problem arises when i change the query to take a parameter. this works ok when i execute in access but not with the code i am using for VBA / ADO. i have tried several techniques but none seem to work.
Dim rsReport As New ADODB.Recordset
Dim cnReport As New ADODB.Connection
Dim cmdReport As New ADODB.Command
Dim prmReport As New ADODB.Parameter
cmdReport.CommandType = adCmdStoredProc
cmdReport.CommandText = "testQuery-withParam"
. Name = "FullName"
.Value = "joe bloggs"
.Type = adChar
.Size = 8
.Direction = adParamOutput
VBA - Refresh Pivot Table & Query
I've used this code prior and it worked fine in Excel 2007, now I'm having issues with it. Originally every Pivottable & Query pointed to a different CSV file. Now I have two pivottables built off a Excel worksheet that is built from a Query. This allows me to do some complex lookups that I couldn't do with just the Pivottable and MS Query alone.
Here's the code that's failing out: Any problems with it in Excel 2007?
Private Sub Update_All_Data_Click()
Dim pvt As PivotTable
Dim ws As Worksheet
Dim qt As QueryTable
mytitle = "This will refresh all data for validation, are you sure?"
Msg = "The Refresh process takes about 5 minutes, are you sure you want to continue?"
Response = MsgBox(Msg, vbExclamation + vbYesNo, mytitle)
Select Case Response
Case Is = vbYes
' Do Nothing, continue with program
Case Is = vbNo
For Each ws In ThisWorkbook.Worksheets
For Each qt In ws.QueryTables
qt.BackgroundQuery = True
For Each ws In ActiveWorkbook.Worksheets
For Each pvt In ws.PivotTables
mytitle = "Confirmation of data refresh"
Msg = "The data has been refreshed"
Response = MsgBox(Msg, vbExclamation, mytitle)
Disable Save Query Definition Option Vba
I want to be able to disable the save query definition option for a worksheet using VBA. Right now I have to right click a cell and then go to Data Range Properties and then uncheck the save query definition box. If there is a way to automate this that would be great.
Vba Query: Attempt To Find A Missing Integer
I am trying to write a code enabling some calculations. As part of this code I want to find an integer in an excel range. I set the variable (integer) as Rvalue and wrote the following:
which works fine as long as the find is successful. The issue I have however is when the variable cannot be found. I tried using the "iserror" function in vba but it appears that excel/vba does not recognise this issue as an error; I could use an excel formula (i.e. if(iserror(vlookup...) but I would prefer to find out how to handle the issue in vba.
Use Vba To Query Data From A Password Protect Access Database
I have an excel file that imports data from an access database. Now the user wants the database password protected I edited my code to include the line "user ID=admin;password=risk;" but I'm getting an error message stating: "Cannot start your application. The workgroup information file is missing or opened exclusively by another user."
I'm bothered because I did not create a workgroup information file. I just want a password on the database I did not create users and groups.
Note this code works fine without the password protection and the added line.
Dim rsData As ADODB.Recordset
Dim szConnect As String
Dim szSQL As String
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= C: est.mdb;" & _
szSQL = "SELECT * from data"
Set rsData = New ADODB.Recordset
rsData.Open szSQL, szConnect, adOpenForwardOnly, adLockReadOnly, adCmdText
i have two workbooks that use a identical worksheet which is called products (this sheet contains rows of over 19000 products ). The problem is i cannot merge the two workbooks together because they are just too big in size.
Also these workbooks have similar userforms and macros that rely on this product databases (worksheets).
My problem is when i add a new product to of one product databases i have to also add it to the second product database again. Is there a way where i can update both worksheets together without opening both workbooks.
I have three userforms that rely on this worksheet from both workbooks:
frmNewProduct - To add a new product to the worksheet.
frmProductUpdate - To edit, replace or delete products in the worksheet.
frmSearch - To search for products inside the worksheet.
I have also got other define names, worksheets and formulas that rely on these product database worksheets.
Create SQL Query In VBA And Display Result As Table In Worksheet
I need the VBA script in excel that will do a SQL Query, for this case I need to select a value where there are 2-3 tags and between certain period then display the result as a table in a worksheet.
Example I have a database with 3 types of tags "Tag A", "Tag B", and "Tag C", and each tag have a value with different timestamp. the database looks like this:
No. Timestamp Tags Value
1 1-Jan-2010 Tag A 18
2 1-Jan-2010 Tag C 20
3 2-Jan-2010 Tag A 20
4 3-Jan-2010 Tag B 17
5 3-Jan-2010 Tag C 19
6 4-Jan-2010 Tag B 18
7 4-Jan-2010 Tag A 20
8 5-Jan-2010 Tag A 22
9 5-Jan-2010 Tag B 18
10 5-JAn-2010 Tag C 20
DB Connection Lockout Other DB Connections
I have a series of external database connections that I run in Excel, connecting to a Server based DB and running some SELECT statements. (ingres). We may have up to dozen of client machines accessing the ingres DB at any time. All is good at this point but when excel goes to INSERT some calculated data back to the main database, it appears that my Excel spreadsheet connection is locking the database when it connects, keeping other people from using their client connections.
first question: Does anyone know if the connection excel uses to run sql statements via external database connection will indeed prevent other users from connecting to the main database?
second question: if so, is there a setting or something that can make excel more "passive"?
SAP Connection - Which References
I'm trying to connect to SAP R/3, and I think I'm not sure which references (Tools --> References) I'm supposed have checked.
All I want to do for now is the following:
Dim sapConn As Object
Set sapConn = CreateObject("SAP. Functions")
Unfortunately, I am getting a "Run-time error '-2147024770 (8007007e)': Automation error. The specified module could not be found."
I have read somewhere random on the internet that I should add "SAP:Remote Function Call: COM Support 1.0 Type Library" which links to the SAPGui Provided library librfc32.dll. However, this alone doesn't seem to work...
Stop Internet Connection In VB Works On One PC But Not On
I have the following code in Excel which stops the connection to the internet
Private Const FLAG_ICC_FORCE_CONNECTION = &H1
Private Declare Function InternetCheckConnection Lib "wininet.dll" Alias "InternetCheckConnectionA" (ByVal lpszUrl As String, ByVal dwFlags As Long, ByVal dwReserved As Long) As Long
Private Const INTERNET_AUTODIAL_FORCE_ONLINE = 0
Private Const INTERNET_AUTODIAL_FORCE_UNATTENDED = 0
Private Declare Function InternetAutodial Lib "wininet.dll" (ByVal dwFlags As Long, ByVal dwReserved As Long) As Long
Private Declare Function InternetAutodialHangup Lib "wininet.dll" (ByVal dwReserved As Long) As Long
If InternetAutodialHangup(0) Then
Write Values From ADO Connection
i have a ADO Connection. My query is like this:
select [C1] from [values$]
It works, but my problem is to write this values.
Cause i work with my workbook open (not that from connection) and i have something like 55,000 rows to change value. A complex range:
I have a loop for each cell in this range, but this process is very slow.
For Each rng In rng
rng.Value = objRecordset(0)
55,000? So slow...
I saw something about QueryTable,
Access To Excel Data Connection
I've created a connection which draws information from an access table to excel. The issue is that any numbers (they are ratings from 1-10) that come from access are shown in excel 1 number higher than the correct data that is in access (i.e 10 is shown as 11, 9 as 10, etc). I've looked all over the forum as well as google and can't find any information on how to correct this.
Dim strSearch As String
strSearch = "abc"
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.xyz.com/&=" & strSearch & "", _
.Name = "search?hl=en&ie=UTF-8&oe=UTF-8&q=" & strSearch & ""
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
Create An Conection With MS QUERY To A Csv And Query Data
I have a csv file on another drive on the network that i need to query. I believe that ms query would be the best way. I know that a DSN needs to be setup but this macro will be used by various users who wont know how to do that. thus I would like to create one via VBA every time the task needs to be run.
I haven't a clue how to do this and i need it to be explained to me in general terms with words of one syllable!
Query Parameters Which Takes The Date From The Cell Into The Query
Need the query parameters which takes the date from the cell into the query. How should I modify my query if it needs to take the date from a cell?? The bold one date should be picked from one of the cell in sheet 2.
My query is this
To Set Up A Condition Is Vba
Been teaching myself VBA and I've gotten stuck, and no matter how I search I can't seem to find an answer...
Trying to set up a condition is vba where if a cell value minus another cell value is greater than a static value (elsewhere in the sheet), then to turn the font green.
Code so far:
Dim Cval As Integer
Cval = Cells(3, 6).Value
FinalRow = Cells(65536, Cval).End(xlUp).Row
For i = 10 To FinalRow
If Cells(i, Cval) - Cells(i, Cval + 1) > R6C5 Then
.Font.ColorIndex = 10
.Font.Bold = True
I can't work out the right sintax to get a if condition to work correctly.
I have gotten this to work with conditional format, but if you want to copy/paste elsewhere, it doesn't paste the format result correctly...
Set Locations Using VBA
I have to manually cut/paste each excel doc which belongs to a specific Store folder, and copy then into their own shared directory, so for example:
Lets say my company has 10 Stores, and I know their online folder locations, the first step I guess would be for me to map the locations of each store, I am not sure what the best way to do this is, would I use the Case select ? to make the set = ?
So for example, Stores 1 - 10.
Set 1 = Dir("Z:Northwest1")
Set 2 = Dir("Z:Northwest2")
Set 3 = Dir("Z:Northwest3")
Set 4 = Dir("Z:Northwest4")
Set 5 = Dir("Z:Northwest5")
Set 6 = Dir("Z:Northwest6")
Set 7 = Dir("Z:Northwest7")
Set 8 = Dir("Z:Northwest8")
Set 9 = Dir("Z:Northwest9")
Set 10 = Dir("Z:Northwest10")
I am not an expert on Case Select, but was thinking:
Select Case myStores
Case Is = 1.......................
Set Protection Using VBA
I tried the following code and it doesn't work:
Selection.Locked = True
Selection.FormulaHidden = False
Range(Range("A" & Rows.Count).End(xlUp).Row & "A300").Select
Selection.Locked = False
Selection.FormulaHidden = False
Selection.Locked = False
Selection.FormulaHidden = False
I want to be able to unlock all cells after the last cell that has data in column A down to row 300. Also need to unlock cells AE11:AG300. What's wrong with my code?