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
ADVERTISEMENT
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
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
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
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
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
Feb 12, 2009
I have a VBA application that uses data from a sheet in the excel workbook. It accesses this data with an ADO connection. On workbook open I open this connection--I declare it as public variable so I can continue to use it throughout the session. My problem is that the connection really doesn't ever dissappear. I have the problem that once I close the workbook, the project still exists in the VB Editor. How do I get rid of the connection? I am stuck because I declared it as public? I want to keep it open, rather than opening every time I want to use it, because it is faster to keep the connection open.
My code is below.
Option Explicit 'requires variable declaration
'declare global connection
Public cn As ADODB.Connection
Sub OpenDBConnection()
'open db connection
'this happens on workbook open
If cn Is Nothing Then
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & WCHSBook.FullName & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"""
.Open
End With
End If
End Sub
View 9 Replies
View Related
Jan 29, 2013
I will be receiving a regular .csv file with data I need to manipulate in Excel. There will be a column of data, some of which has leading zeros. These are critical text data - in Excel it's easy enough to format a cell as text - but when I OPEN a new CSV, it assumes numbers and doesn't save them. When I change the format to text, it does not re-populate that column correctly - I'm going to have to catch it on the "open in Excel" command .
View 4 Replies
View Related
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
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
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
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
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
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
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
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
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
View Related
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
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
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
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
Sep 20, 2013
How to convert "Excel table" to a range without loosing data connection to access?
View 2 Replies
View Related
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
Apr 7, 2014
We have our system set up with 2 folders one with blank forms and the other with these forms filled out and completed (records), is their a trick to removing the data from a completed form fast with out removing tables and text used to make the forms questions? If I could do this it would save time from opening in the blank forms folder then saving in the records folder.
View 4 Replies
View Related
Dec 15, 2013
I have a workbook that has ben imported into excel and I need to extract the Cabinet sizes (red text) from ColumnC On Sheet2 leaving out the rest of the info. I have a button on the home sheet that has the code that I am running to generate this report it will do most of what I want but I am stuck at extracting the cabinet sizes.
The data is coming from the SheetComponetListing worksheet and going to Sheet2. I have manually created the end result that I am looking for on the CabinetSize worksheet. [URL]....
View 3 Replies
View Related
Jun 10, 2007
I am "designing" a time- tracking database. The way it works is that each user (there are multiple users) creates a new day, which is mirrored in the sheet name (i.e. if today is 06/09/07 and my name is Newuser, the sheet name is "NewusER 060907"). Each sheet is filled in, calculated based on in-sheet formulas, etc. At the end of said day, the user can " upload" the daily data from multiple days worth of data into another sheet, that the graphing macro draws from.
The one serious problem I'm having is that users can upload the daily time data for the same day multiple times, to no end. I would like to do one of two things. Either:
a. make it such that once data is uploaded it cannot be uploaded again (probably more difficult), or
b. write a loop to pull out the highest data point up the sheet and delete all other rows for that one specific day's . (probably easier)
example:
Data loaded into the sheet before the macro runs:
Row: Date:
1 06/09/07 *
2 06/10/07 *
3 06/11/07 *
4 06/09/07
5 06/10/07
6 06/12/07 *
7 06/13/07 *
8 06/09/07
9 06/11/07
10 06/14/07 *
* The rows with asterisks are the ones that I would like to have pulled to the second set of data (below)
Data in the sheet after the macro runs:
Row: Date:
1 06/09/07
2 06/10/07
3 06/11/07
4 06/12/07
5 06/13/07
6 06/14/07
Because of the constant changing nature of the information within, I would like to to make this dynamic range selection, I would prefer to avoid using Advanced Filters, if possible.
View 6 Replies
View Related
Jul 20, 2009
I Want to Delete Data.
Filter I Column And Delted "N" ....
View 9 Replies
View Related
Aug 5, 2013
I have data like this....
6
4
5
2
.......in the first column A1:A4
I would like to add and remove data to the column so I have data that would look like this...
6
4
5
2
1
9
.......in the first column A1:A6
I would like to find the average on differing numbers of data without changing the formula. So how can I tell the software that my average has now changed from 4 data to 6?
View 2 Replies
View Related
Jun 2, 2014
Trying to select all cells below the last row of data based on column B and remove borders.
So far I have this.
[Code] .....
View 4 Replies
View Related
Nov 24, 2008
Suppose I have data in a range that was imported from "somewhere". Some of the data is preceded by team rankings in parenthesis...like so:
Alabama
(21) Boston College
Notre Dame
(10) Oklahoma
Texas
Florida State
(2) Florida State
I want to strip the data of the team rankings, (as well as any brackets and spaces before the team name). Is there a macro I could write to do this?
View 5 Replies
View Related
