ADODB Connection: Running SQL Queries On Data Within The Same Workbook

Oct 30, 2008

I am simply trying to have some code that would allow me to run relatively simple SQL Queries on Excel Data. This data however will be in a table format within Excel and I will only be needing to query on table at a time; meaning that I don't need relational database features or SQL Joins at all. At most, the SQL Statements will involve WHERE and SORT statements. So, my method is relatively simple:

1. Select an Excel Range (The table being Queried)
2. Provide an SQL Statement
3. Provide an Output range to print the results

After my recent research, I concluded that ODBC/ADODB was the best route. I am very new to ODBC Connections though. Basically, I just set up an ODBC Connection and DataSource using the Control Panel/Administrative Tools. After that, following some templates of others I constructed the following

Sub getData(SQL As String, outRange As Range)
Dim conn As Variant
Dim rs As Variant
Dim cs As String
Dim outCell As Range
Set outCell = outRange.Range("a1")
Dim row As Integer
Dim col As Integer

Set conn = CreateObject("adodb.connection")
Set rs = CreateObject("adodb.recordset")

cs = "DRIVER=Microsoft Excel Driver (*.xls);UID = admin;UserCommitSync = Yes;Threads = 3;SafeTransactions = 0;ReadOnly = 1;PageTimeout = 5;MaxScanRows = 8;MaxBufferSize = 2048;FIL=excel 8.0;DriverId = 790;DefaultDir=F:2008Cell PhoneCall Details;DBQ=" & ThisWorkbook.Path & "" & ThisWorkbook.Name...............................

View 9 Replies


ADVERTISEMENT

ADO ADODB.Connection: Preserve Cell Formatting

Oct 4, 2006

With ADO (ADODB.Connection), is there a way to preserve the cell formatting on the worksheet that the recordset data is copied to? Right now, if I format a Cell's font and font-size, then run the Macro to refresh the data, the formatting is gone and it's back to default formatting.

View 2 Replies View Related

Excel 2010 :: Data Connection In Shared Workbook?

May 29, 2013

Im using Excel 2010 and have a data connection that I want to use. I added it to a regular workbook and it updates perfectly as-designed. However, I need this feature to work in a shared workbook, but it doesn't.

View 2 Replies View Related

Excel 2007 :: Workbook Use One Data Connection With Different SQL Query For Each Worksheet?

Oct 13, 2013

I'm trying to build a workbook (2007) with multiple worksheets where each worksheet corresponds to a different query/dataset pulling data from the one database.

I would have expected that you could define the one data connection object and then specify the SQL that each worksheet table will invoke. The apparent structure of having the query coupled to the connection object is confounding me. I'm therefore trying to set up a separate connection for each of the worksheets and Excel is getting tied up in knots.

View 2 Replies View Related

Running Access Query In Excel And Connection

Aug 5, 2003

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
FROM bookings
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?

View 9 Replies View Related

Excel 2007 :: Data Connection Between Two Workbooks Locks Source Workbook When Refreshed

Feb 10, 2012

[URL]....

I have 4 workbooks, all of them contain data that is managed by other teams at my company. I need to take all of this data, organize, and concatenate it into one sheet for myself, that I will then reference in other workbooks to various OTHER departments in our company that need the data for certain projects. I have the organization laid out in a lot of very complex formulas so that most of this is automated, and not manual.

I need a solution that can pull data from the 4 workbooks without locking them for use, to allow me to keep my document open all the time, and they can update theirs at their leisure. Everything works fine, until I hit the refresh button. Once this is hit, all of the linked documents are locked and cannot be opened, even in "Read Only" mode. Once I close the master document, the files are free to be opened. I know a workaround for now is that i can not "refresh" the document, but rather close and re open it for changes.

Some steps I have already tried :

1. Create an intermediary file that no one uses, and can be locked all day without a problem. This doesn't work for me since the intermediary file needs to be open to refresh, which locks the original source doc, leaving me in the same place as before.

2.Modify the connection string to display "Mode=Read;" instead of "Mode=Share Deny Read"

3.Create a new connection with "Read" only selected in the Advanced Tab

View 1 Replies View Related

Refresh All Queries On Workbook Open When Finished Print To PDF Then Close Workbook

Jan 23, 2014

I have data that daily needs to be refreshed and printed to pdf.

I figure the simplest way to do this would be to task schedule the workbook to open daily. Then on open it will refresh the data, print it after all data has been refreshed and close the workbook.

I set it up originally without the need to print so I have all the queries set to refresh when opening the file, however when I now try and put the code to print to pdf on the workbook open event it runs before the queries are finished running. (Query notes: queries were created through Microsoft query, and are accessing a MySQL database queries set to refresh when opening the file queries set to enable background refresh).

View 4 Replies View Related

Workbook Connection

Jun 8, 2006

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.

View 9 Replies View Related

External Data Queries

Apr 1, 2008

I use external data queries in excel. That's how I look up data in my access databases because I have so many access and SQL databases it's just eiser to be able to do quick queries on multiple databases at the same time in one spreadsheet. And, I can save the external data query as a tool to use in future queries. I have tons of tools and external data query is the foundation of most of them.

What bugs me is EDITING data.

If I want to do some light simple editing in an SQL database I am required to use a linked table in Access or SQL Studio Express Manager. Great programs, but I want to integrate!

Is there no way to make excel a database editor? I can retrieve data from any database or program with excel just fine, but is there a way to use excel to DIRECTLY EDIT the data in an external database with EXCEL like I can in access linked tables?

View 9 Replies View Related

Excel 2010 :: Access Data Connection Query - Missing Data All Of Sudden

Oct 19, 2012

I created a slick little excel sheet with the data coming in automatically via Access query. It has been working fine for months. Now all of the sudden there are a bunch of cells with missing data. The weird part is it's not as if whole columns are missing data, more like 90% missing. When I go to Access and run the query all cells are populated as the should be. There have been no changes to the query at all during this time.

btw...I am running Office 2010

View 2 Replies View Related

Data Connection From Web / Web Query Returns Code Instead Of Selected Data Table

May 31, 2014

A web query I am trying to use to bring an online data table into my worksheet is broken. Now, instead of returning the data table nicely into my worksheet, it imports the code of the webpage, instead, and turns my worksheet into a mess.

The query used to work but there was recently an "upgrade" to the program that populates the web table and the query no longer works and just returns the code for the page, instead. I can see the html code for the table in all of the code it returns but I really need the table to import cleanly into excel.

I can't talk to the people who changed the web program.

View 3 Replies View Related

How To Remove Data Connection

Aug 4, 2014

I was messing around with data connections and importing stuff from a network in real time. Cool stuff. But everytime I open ANY excel worksheet on this computer, it tells me "file cannot be found" and refers to one of the first files I used for this. this happens no matter what workbook I am opening.

When I click data --> connections its empty.

View 2 Replies View Related

Web Data Connection Parameter Problem

Oct 10, 2008

I'm having problems with date parameter for a web connection. I can provide the parameter and type the parameter via message box ... but when I want to reference a cell the query errors out.

View 3 Replies View Related

Access To Excel Data Connection

Nov 9, 2009

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.

View 9 Replies View Related

Today Or Now Functions In Queries To External Data Sources?

Dec 2, 2009

Is it possible to use a today or now function in an sql query to an external datasource in excel 2007? I have an excel 2007 spreadsheet, that I have linked to our financial database (MySql) using the database wizard. I can get the tables to display in excel no problem. One table shows how much staff have billed in each invoice period. I would like to create a query in excel, so that when this table displays in excel it shows how much each staff member has billed for the current invoice period, as opposed to all of them.

We weren't sure how to do this, so we decided to do a query to pull out the current invoice period, then we could combine this pivot table with the one for the amount billed table to give a table showing the amount each staff member has billed in the current invoice period (do this bit in excel).

The problem is we can't seem to make the query to pull out the current invoice period work. We have tried the query below, and all the variations of it that we can think of

SELECT invoice_period FROM tbl_invoice_periods WHERE STR_TO_DATE('" & NOW() & "', '%d/%m/%Y') BETWEEN start_date AND end_date;

We have also tried referencing a cell which shows the current date, but we cannot get this to work either (we may have made a mistake in the query here, we are not entirely sure how to reference an individual cell). The only way we have been able to get it to work is if you enter the current date directly into the query, but obviously this means it's no longer automated, which isn't a solution.

View 3 Replies View Related

Data Connection Locking Source File

Jan 10, 2012

I have 2 excel files. One excel file (File A) is a basically a source file on a shared network that users input data into. The second excel file (File B) I created in hopes of basically using to analyze the data in File A. I used a data connection to access the data on File A while on File B. The issue I'm facing now is that when I refresh the connection, it locks File A from being opened until I close File B. Currently, I have the data connection to refresh only manually, so I'll open File B, refresh it, save, close, and then re-open without refreshing, so that way File A only stays locked for that short amount of time. Is there any way to make it such that when I refresh the connection, it doesn't lock File A?

View 1 Replies View Related

OLEDB Data Connection And PivotTable Report

May 8, 2014

I am connecting Excel to a database and there is a field called Data Created which is timestamp. Data comes out nicely in a Pivot Table

1. I would like to be able to group by month but the grouping fiction is grayed out

2. How can we convert the timestamp to only date format. I am able to select only Date for that column but it seems that it keeps the time, even though it only shows the date in the pivot table the bar above shows date and time.

View 1 Replies View Related

Conditional Formatting With Sharepoint Data Connection

May 30, 2007

I have an Excel workbook that has a data connection to a Sharepoint List. I have 4 columns showing the quaterly status of a project. I want to apply conditional formatting based on the value in the column. I have 5 possible choices so I have to use VBA code to accomplish this because of the limit of 3 option in Excel. I am using this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Intersect(Target, Range("X:AA"))
If rng Is Nothing Then
Exit Sub
Else
Dim cl As Range
For Each cl In rng
Select Case cl.Text
Case "1"
cl.Interior.ColorIndex = 4
Case "2"
cl.Interior.ColorIndex = 6
Case "3"
cl.Interior.ColorIndex = 3
Case "4"...............................

View 3 Replies View Related

How To Remove Data Connection But Preserve Data

Jul 24, 2013

If I run this vba it removes the external data connection to the pivot table:

ActiveWorkbook.Connections("EXAMPLE").Delete

When I attempt to drill down into the pivot table it states the data connection was removed.

Is there a way to remove the data connection but preserve the data in the pivot cache?

It's probably possible to store the data somewhere else in the document and link the pivot table up to it but that seems cumbersome.

View 4 Replies View Related

Pivot Table - Break Connection To Data Source?

Apr 14, 2011

if it's possible to break the connection between a pivot table and the data source whilst still maintaining the data in the table? I could try copy/paste special/paste values but thought there migth be a 'proper' way to preserve the data.

View 8 Replies View Related

Excel 2010 :: Remove Data Connection And Keep PivotCache

Nov 26, 2012

With Excel 2010 a yellow warning bar is shown when opening a file with data connection. I don't want to show this warning to other users who will be opening the file, especially when they won't be able to update anything since the original data is on my computer. The logical way would be to remove or disable the connections before sending the file out. But when removing the data connections through Connection window all the pivot tables are converted to static tables, which is not acceptable. Is there any other solution to this? Maybe use a macro?

PS: I know that these warnings can be turned off though Trust Center, but I am not interested in changing these settings for other users.

View 2 Replies View Related

Excel 2010 :: Multiple Sheets One Data Connection

Apr 8, 2014

I have a sheet that contains a Microsoft Query (SQL) data connection. In cell C3, I have the month name and I also have a year cell (C4), which contains just the year based on what the current month (=YEAR(TODAY())) is.

For this example let's use May (C3) and 2013 (C4)

C3 and C4 are used as parameters for the SQL query, so if I change the month to January and the Year to 2010, data is refreshed to bring in that data.

Works smashingly.

I then want to add 11 more sheets (12 total), so each sheet is a month. The value in cell C3 on each sheet will be the month name.

If I just copy this sheet 11 times, Excel adds 11 extra data connections; 1 for each sheet. As the query in each sheet needs to be exactly the same, I don't want this. I want the 12 sheets to use the same data connection, only with the specified parameters at the sheet level.

Otherwise, if I need to add or remove a column, or change a column name, rather than just doing it once, I'd have to do it 12 times.

This can't be right, surely, as that would be the most ridiculous design flaw I've ever seen in a piece of software. How can having the same query 12 times be a good thing?

I'm a bit concerned that googling 'excel data connections multiple sheets' always wants to be 'excel multiple data connections one sheet' - I want 'excel multiple sheets one data connection'

View 1 Replies View Related

Date Format Not Remaining After External Data Connection Refresh

Jul 9, 2014

I have an excel table that connects to an external sharepoint site.

When I refresh (via external data connection), the "start date" and "end date" column formats change. The only way I can correct this is to copy those columns to notepad, and then paste them back into the spreadsheet.

I have several calculated cells based upon these date fields. Once refreshed, the data is incorrect until I copy and paste those dates as stated above.

This removes the functionality of simply "refreshing" the data resulting in a manual process.

View 2 Replies View Related

Working Access Query Fails When Using Excel Data Connection

Sep 6, 2012

I've about 10 pivot tables in Excel that are populated via an External Query link to Access. In each case I have pre-written views with all the columns I need so that the query can just pick them up. Nine of the ten queries work perfectly, but one doesn't - reporting "Too Few Parameters - Expected 1" when I try and click on the "Return Data to Excel" in the import wizard.

I can see the column headers in Excel to select; so I'm assuming that it can parse the SQL but no results are being returned. Just to confirm though - results are returned (>8000 of them) in Access.

SQL code below:

Code:
SELECT z.pathdurationhours AS PathHours, Count(z.pathdurationhours) AS Paths,
Count(z.pathdurationhours)/(select count(*) from (select [activity date],
[conversion ID], count(*) from tbl_dfa_p2cdata group by [activity date], [conversion ID]) ) AS PCT
FROM (SELECT a.[activity date], a.[conversion ID], val( max(a.[interaction time])-min(a.[interaction time]) )
*24 AS PathDurationHours FROM tbl_dfa_P2CData AS a WHERE a.[interaction time]
"" GROUP BY a.[activity date], a.[conversion ID]) AS z
GROUP BY z.pathdurationhours;

View 2 Replies View Related

Using ADODB To Add New Field Into Access Table

Dec 17, 2012

I'm using the below code to add data to an access database and it's working fine, but I'm trying to figure out what code I would need to add a completely new field to the access database?

So maybe before the export, once connected to the DB, add the new field(s) to the table and then add export the data. I'd have to rewrite export bit to allow for any new fields that i've added, but I can do that.

It's just the actually command I need add the new field to the table. Sure it's just a simple couple of lines, but trying to find something that makes sense !!

Code:
Sub Update_data(strSite)

strDir = Worksheets("Parameters").Cells(2, 2)
strDB = Worksheets("Parameters").Cells(3, 2)
strTable = Worksheets("Parameters").Cells(4, 2)

' exports data from the active worksheet to a table in an Access database

[Code] .........

View 1 Replies View Related

SQL Server Stored Procedure Via VBA And ADODB

Feb 13, 2013

I'm creating my first stored procedure ever and it looks relatively good so far. The problem is with passing the parameters, more exactly:

Arguments are of wrong type, are out of acceptable range, or are in confilict with one another

The essential part of the stored procedure looks like:

Code:
CREATE PROC GetUserAuthForApp
@User varchar(7),
@application int
AS

while the essential code calling it from VBA looks like:

Code:
Dim strConn As String 'Connection string to SQL Server
Dim strSQLtoExecute As String 'SQL query string to execute
Dim oConn As ADODB.Connection 'Object for connecting
Dim rs As ADODB.Recordset 'Object for recordset
Dim cmd As ADODB.Command
Dim prmUser As ADODB.Parameter
Dim prmApplication As ADODB.Parameter
Dim stProcName As String 'Stored Procedure name

[code]....

and in that rs.Open comes that error.

What have I done wrong, how do I fix it?

View 9 Replies View Related

(Find) Function With ADODB Recordsets

Jul 23, 2013

I am wanting to find a row in a SQL database using VBA in excel. I know you can use find to find the row, but then how do you replace data on that row with the row I have in my spreadsheet?

I currently have:

Code:
If lastrow 0 Then For i = 3 To lastrow
recset.Find "Col2 = " & Sheets("Changes").Cells(2, i) 'find the value in B from B3 onwards (i)
' Now its found the row in the SQL, replace with values from that excel row (i)
Next i
End If

View 9 Replies View Related

Code To Check If ADODB Exists

Oct 27, 2009

I have some code that will Kill a DB if it already exists, but I want to check if it exists and warn the user before this happens.

I am not that familiar with ADO, so I was fumbling through the Help topics trying to learn about ADO type names, etc. b/c i thought I could use something like:

If TypeName(MyDB) = "ADODB" Then . . .

but even if that ran, the argument in parentheses would be a string and not the actual DB object, so I am at a loss.

View 9 Replies View Related

Excel 2007 :: Security Alert - Data Connection Message Every Time?

Dec 20, 2012

I open a spreadsheet or workbook that has links to other workbooks I am notified by the message bar that content has been blocked. I click Options > Enable this content > OK. Even if I don't make any changes I'm prompted to save changes when I close the file. If I hit Yes or if I hit No, I will still be prompted to enable content in the message bar the next time I open the same file.

I have changed the External Content settings in the Trust Center to "Enable all data connections (not recommended)" and "Enable automatic update for all workbook links (not recommended)" but I still get the messages.

My Message Bar is set to "Show the message bar in all applications when content is blocked" because I read on Microsoft KB that the other option of "never show information about blocked content" will still block the content, just not tell you about it, which doesn't fix my issue.

The files I'm opening and the files linked to and from them are all on the same shared drive on a file server in the office. Is there a way to tell Office '07 that our file drive is a trusted source by default?

View 2 Replies View Related

Convert Excel Table To Range Without Losing Data Connection To Access?

Sep 20, 2013

How to convert "Excel table" to a range without loosing data connection to access?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved