Editing Data Connections In 2003
Oct 20, 2009
I have a workbook with multiple worksheets and over 25 Data Connections using MS Query to connect to different tables in an SQL Server database.
The problem is that the SQL Server database is now being moved to a different server. The database name and the schema of the tables will be unchanged. Only the server name needs to be changed from XXXXX to YYYYY.
I have been unable to find a way to successfully edit the data connections in Excel 2003. A web search produced one potential option. However, that did not work for me very well. Thought the code is designed to cycle through all tablequeries and pivotqueries, the change is only made to one of the data connections. Here is the option I tried:
http://support.microsoft.com/kb/816562
Note that it is very easy to edit data connections in Excel 2007. So I also tried opening the .xls file in Excel 2007. I used the Data Connections button under the Data tab in the ribbon and was successful in editing the connections to point to the new server. However, the graphs used to display the queried data behave weird. The "format axis" settings and chart sizes change. In general, default display settings for charts seems to differ between Excel 2003 and 2007.
I used Excel 2007 to only edit the data connections and saved the workbook in the original .xls format
View 3 Replies
ADVERTISEMENT
Mar 14, 2014
I have created a workbook to input, store and part edit defects on plants and machinery by userforms. I have everything working except either picking up a number I have pre-loaded into a column or auto generating a number onto the inputting userform. I need a number in the userform to be either generated in the next empty row in column I or taken from the numbers in the next empty row I have loaded into column I. Password is snatch.
Input 2003 test1.xls
View 3 Replies
View Related
Dec 16, 2011
I have a workbook which uses data connections to our sage accounts product. Everytime i open the sheet i get the "Data Connections have been disabled" security message, and need to go in and enable them. Is there any way of enabling the data connections by default? I only want to do it for this one workbook - i still want to be prompted when i open other workbooks.
View 2 Replies
View Related
May 3, 2013
I currently have a spreadsheet that has data connections to a file on a shared drive. On opening the file I have it update the data connections so that the data needed is always correct. The file I use is to be distributed out to work colleagues to use and on testing it works really well apart from if you have the file open and somebody tried to open the original file where the data is pulled from for the connection. It says it currently in use by 'another user'.
Is there any code to add to workbook_open that will close/disable the data connection links once the data has been updated? The code I currently have is this:
VB:
Private Sub Workbook_Open()
ActiveWorkbook.RefreshAll
Application.Wait Now + TimeValue("00:00:02")
Userform1.Show
End If
End Sub
What this basically does is gives it time to refresh the data connections and show a progress userform. The only thing is the connections stay live and I don't want that as the connection file is then locked. I'm hoping its something simple like ActiveWorkbook.CloseDataConnection added in after the userform has been shown (or something along them lines ). Also the data connections would have to be able to reconnect on open so that they can refresh again.
View 5 Replies
View Related
Jun 2, 2014
My issue is we have a remote office, using an access DB which links to an Excel file. We sometimes send the files to other people to make changes, but the link is then broken (it was a network location).
You can just change it manually, or accept the many prompts that come up, but it needs doing at each end, each time.
I thought I'd do a routine to change the connection paths, this is what I have...
Code:
Sub alan()
Dim i As Long
Dim cnt As Long
Dim modtext As String, modfile As String
Dim modrange As String, OldPath As String, NewPath As String
OldPath = "\192.168.1.100MultimediaCommissioning Folder"
NewPath = "C:matts"
[Code] ...
I added the sourcedatafile part to the routine afterwards, because the connection string part worked fine. But I now get Run-time error '1004' Application-defined or object-defined error...
View 3 Replies
View Related
Apr 26, 2013
While making my first foray into defining external connections (to an Oracle database) I ended up with an "Existing Connection" that reports "File could not be opened. It is either damaged or the file format is not valid. Try opening the application that created it and saving to a new file." Followed by "Unable to open or read this connection file. Either the file has been damaged or the file format is not valid."
Ok, fine. So how do I delete it? There seems to be no option for that in the "Existing Connections" dialog. A search for a *.odc or *.udcx file doesn't turn up anything with the name of this connection. A search of the registry doesn't turn up anything.
extdata.jpg
View 1 Replies
View Related
Mar 31, 2009
I am trying to write a macro to delete all connections from a very large excel file
I created this file by using a Macro to extract data from a external data source (url's listed on a sheet)
The issue is the 1000+ connections on this file make it next to impossible to operate
I cannot recreate the file
macro to delete all connections in all sheets in a given work book.
[URL]
Macro used :
Sub Dump()
Dim myURL
Sheets("Sheet1").Select
ActiveCell.Offset(1, 0).Select
myURL = Worksheets("Sheet1").Range(ActiveCell.Address).Text
Sheets("Sheet2").Select
Range("A65536").Select
[code].....
View 3 Replies
View Related
May 23, 2013
I'm trying to use vba to create an offline backup file for a sheet that is linked to an access database. the code I have is simple and worked for a file that is not linked to any database.
Code:
Sheets("Data").Select
Sheets("Data").Copy
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=myfilename
Application.DisplayAlerts = True
Now that I insert it into the actual file, which is linked to the database I get an error. I also tried to move the display alert up, before the select instructions, but also this does not work. this is not what I wanted since I want this file not to possibly connect to any database.
View 9 Replies
View Related
Oct 10, 2013
I've tried to remove connections after saves and have been unsuccessful.
HTML Code:
' Delete Additional Connections
' If ActiveWorkbook.Connections.Count > 0 Then
' For i = 1 To ActiveWorkbook.Connections.Count
' ActiveWorkbook.Connections.Item(1).Delete
' Next i
' Else
' MsgBox ActiveWorkbook.Connections.Count
'
' End If
HTML Code:
' Remove named ranges
For Each nm In ActiveWorkbook.Names
nm.Delete
Next nm
I've tried both of these and the Excel file is still attached with XML to the query table. I get an error message saying the format is different then if you click okay it shows its connected to the database still. Is there an aggressive command that will severe all connections before saving? What commands to run to stop this connection after save to another workbook?
View 2 Replies
View Related
Jul 29, 2014
Currently We enclosed a Workbook (with exiting VBA code) In this workbook 3 sheet exit (Edit, Details, Summary)
In Details Sheet store Detail Entry of Invoice & in Summary Sheet we store all generated Invoice details in summary form
We want new VBA or correction in existing VBA.
My Exiting VBA work following..
When Enter Invoice Number in Edit Sheet (Cell B2) then Display Entered Invoice Data
First Code Find Invoice Number in Summery Sheet if Invoice Number found then Record Display of Selected Invoice
Here we Edit As per our requirement then We Press Y in Edit Sheet Cell C30
If Found C30 "Y" & "Data ok" in E30 then DELETE Selected Invoice Data From SUMMERY & DETAILS SHEETS & COPY THIS EDITED DATA (RANGE A33 TO G33 In Summery Sheet & Range A35 to A60 in Details Sheet)
My Exiting code work perfectly as per above
We want only following thing
Currently when we copy edited data that move to end of sheet We want to Change & same place where data exit previously before edited. Because serial number disturb.
NOTE : in Editing Time is it possible Few Item Delete or Few Item Add
View 12 Replies
View Related
Jan 29, 2008
I have 5 columns Data sheet in Excel and >5000 Rows. Time to time I have to change data parameter of some certain item (complete Row). I want to Database form (Userform) which can add new item, find and can modify, or delete from sheet. I Shown userform format in attachment without macro
View 3 Replies
View Related
Dec 4, 2013
I have multiple data tables that are linked to a site. My issue is that there are breaks in the data every 15 cells with a row that shows the column categories. I locked my top row so I don't need the recurring column categories at all, but when I delete them and refresh the data they reappear.
How to delete these rows? Could I possibly run a macro that deletes the rows upon hitting refresh? Or is there a simpler way?
View 1 Replies
View Related
Jun 17, 2009
I'm currently working on sorting a column of data from a PDF file into separate columns to later compare to another sheet. I'm having a few problems getting the data into a workable format, and also don't know how to compare to the other file.
Ultimately, I want the data in columns that look like this:
Name, City, State, Zip, $123.00, mm/dd/yy
Currently it looks like this:
A
1 Name
2 City State Zip
3 $123.00 mm/dd/yy
The sheets I'm working with are thousands of rows long, with these three rows of data repeating for every person. I have two problems with them, however:
1. I ultimately need each of those pieces of information into a seperate cell, in separate columns. I've been transposing the data then doing "Text to Columns" to get it from three separate rows in Column A to one row in Columns A, B, C etc.
The problem with this is the names aren't always uniform. Sometimes there's Mr. or Mrs. in front of them, sometimes they have a middle initial, or an "esq." behind them, which completely messes up the "Text to Columns." Is there a better way to break them up into separate cells?
2. Secondly, not all of the names are individuals- some are businesses or charities. I want to remove all of these (and the two lines of data that go with each of them), so I only have individual people. Do you have any idea of how to make excel know the difference between, for example, "Mr. John Smith" and "Smith and Sons Realty"?
3. Lastly Once I have all of this data spread out into the different columns
(Last Name, First Name, City, State, Zip, Date, Amount), I need to compare it to another list. Basically, I've got one list of data configured in a similar way, and I want to identify any repeats between the two lists. I know that excel has a way to delete repeats, but what I need is a way to delete everything BUT repeats.
View 9 Replies
View Related
Apr 12, 2006
I am trying to figure out how to enter and edit data using a form for stock traceability.
I have created the form with the headings etc and everything works well at data entry.
However, I am trying to create a search and edit form that searches and edits existing entries.
A third form displays the data in form format when the user types in the appropriate ID.
View 9 Replies
View Related
Jun 24, 2014
I have a simple Excel file composed of three tabs:
-EDIT
-QUEUE
-RESOLVED
On each tab, line 1 has the same headers: Status, Name, Surname, Issue, Details, Last Update
In the EDIT tab, line 2 is dedicated to editing the relevant information under the headers, and once done is validated with a "Done Editing" button: This button would cut line 2, and place it in line 2 of the QUEUE tab, moving down all the others in the list
The QUEUE tab lists all the issues keyed into line 2 of the EDIT tab. An "Edit Selected" button would cut the selected line (or the line in which a cell is selected), and copy it to line 2 of the EDIT tab for further editing, after which it would be placed on top of the queue once again as above.
The RESOLVED tab lists all of the issues marked "Resolved" on the Status column.
What I can't figure out are the macros for the two buttons:
- "Done editing" in EDIT tab adds today's date in the "Last Update" column, then cuts line 2, and pastes it in line 2 of the QUEUE tab pushing down all the lines in the list, and keeping any existing conditional formatting if possible (colours in the "Last Update" column, for ex.). If the status is set to "Resolved" before clicking the button, it does the same, but pastes it to line 2 of the RESOLVED tab instead of QUEUE.
- "Edit selected" in the QUEUE tab just cuts the selected line from the QUEUE tab and pastes it in line 2 of the EDIT tab.
View 2 Replies
View Related
Mar 18, 2014
is it possible to unlock a chart but then disabling to change the source data?
Background: I want the user to be able to add numbers etc. but he shouldn't see the underlying table...
View 2 Replies
View Related
Apr 10, 2007
i want my userform to operate like this:
>each time the user opens the userform, he has to choose from the 1st combo box which AREA will he worked on (e.g., Math, English, History). upon selecting, the AREA chosen will populate the choices in the 2nd combo box (e.g. if Math, 2nd combo box will show: Algebra, Calculus, Trigonometry).
**thru some posts, this part is already solved. thanks. but this will lead me to my questions.
>1st and 2nd combobox selections (e.g., MATH, CALCULUS) serve as a match. Unique from the other matches.
>upon choosing the match, the user can encode in the succeeding textboxes (he would encode numbers).
**this is in another Userform Which Saves Data To Two Different Worksheets DATA TO TWO DIFFERENT WORKSHEETS
>my userform has an EDIT command button.
***what VBA codes can i use so that when I click EDIT, the userform will display all the matches that I encoded. If I choose the match from such display, all that i encoded which are related to that match will be reverted to the userform so that i can edit it right at the userform?
View 9 Replies
View Related
Apr 22, 2009
What I'm trying to achieve is to be able to lock the text length of a column so that users can enter text data into that column but not exceed the allowable length for our purposes. I've found a way to use auto loading macros to make a popup if they exceed the set length.
However, that is useless for our purposes because 1) pasting data into the cells as opposed to manually entering data overrides the validation just as it does when you set it with the data validation menus, 2) it requires that macros be activated in the end user's computer which we cannot guarantee will happen. If you lock in the data validation by protecting the worksheet, the end user can't enter data. Do you know of a way to lock in the data validation without using active controls like macros, and yet allow the end user to enter data into the cells. (Perhaps a solution would be to somehow prevent the pasting of data into a cell forcing the data validation to be utilized?)
View 3 Replies
View Related
Nov 12, 2013
I have a database which I want to send to my peers. I want to set a rule which will enable me to keep track of the data which are changed. For instance, in a cell if someone edit that number, the color will change from black to red.
View 7 Replies
View Related
Jun 11, 2014
I am working on a project that I feel should be relatively simple but I seem to be stuck. My Goal: To add information to specific rows of data in the worksheet. I want to use a user form, and eliminate as much typing for the user as possible. I have already designed my user form, and written the code to identify the row in which I want to edit. Nonetheless, I do not know how to code the insertion of the new data. The data I want to add will be in empty cells at the end(right) of the data table.
I have attached a portion of my data set.InsertQuote.jpg
Here is where I am. The words in red are just colloquial words that I cannot seem to put into code.
Private Sub CmdInsert_Click()
Dim Company As String
Dim PartNumber As Variant
Dim Condition As String
[Code] .....
View 1 Replies
View Related
Feb 9, 2012
I am trying to create a simple xy graph in Excel 2010 using dynamic source data.
I have data in three columns.
There are headings on line 2 and the data starts on line 3.
A B C
1.
2. dp ss ff
3 .1 .2 .3
4 .3 .4 .4
5 .4 .8 .7
6 .1 .6 .8
7 .3 .5 .1
8 .5 .4 .4
I want to create an XY chart with C3 to C8 as the X axis (or dynamic to what ever the last data point is) with A3 to A8 as the Y axis (or dynamic to what ever the last data point is).
I have Defined names for C3 to C8 (myXvalues) and A3 to A8 (myYvalues).
I have created a simple XY graph and selected the ranges as normal.
This is =DATA(,Data!$C$3:$C$9,Data!$A$3:$A$9,1)
The page is named DATA
What I am trying to do now is to edit this range to make the range dynamic.Unfortunately I get error messages which ever way I try to edit the range.
I have tried:-
=SERIES('Data'!$c$3,'Data'!myXvalues,'Data'!$A$3,'Data'!myYvalues,1)
=SERIES('Data','Data'!myXvalues, 'Data'!myYvalues,1)
=SERIES(,Data!myXvalues,Data!$myYvalues,1)
=SERIES(Data!,Data!myXvalues,Data!myYvalues,1)
The data range can go from 8 to 30000 hence the need for a dynamic range!
PS: Also, is there any way to create this graph and dynamic source data in VBA. I have tried this with no success.
View 2 Replies
View Related
Mar 16, 2007
how to protect a worksheet from editing and at the same time allow all data source links within the sheet to be refreshed? I need my users to be able to refresh all data links as required but not be able to change the worksheet in any other way.
View 2 Replies
View Related
Feb 9, 2010
I've got a little program which uses webqueries to import data from anywhere between10 and 200 web pages. It's working pretty well, but I need a way to clear out the residual connections via the code.
The part of the macro which imports the data was simply recorded and very slightly modified. I've included it below... but what I'd like to do once the data has been imported is delete the connection.
View 3 Replies
View Related
Mar 7, 2007
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"?
View 9 Replies
View Related
Jul 22, 2006
From within VBA I need to open a database connection and issue a select and store the results in my spreadsheet. The issue is that I only JUST learned that you can do this and I dont know where I can look for instructions. Incase it matters, I am trying to connect to a Sybase DB. I have used perl, c# and a little bit of Java to do this but never from with in Basic (let alone basic within excel!)
View 9 Replies
View Related
May 16, 2008
It extracts Employee ID's and there Names from our database and lists them in columns A:C
What I want to do is list the ID's manually and get it to extract only the Names for the ID's I listed in Column A...
I am not sure how to amend this code to look at ID's listed in column A instead of extracting the whole database.
I know I need to add a "WHERE ID =" clause in the SQL but not sure how to say WHERE ID = A1, A2, etc.
View 14 Replies
View Related
Nov 14, 2012
I have a connection to an SQL database using microsoft query.
The query definition includes: ORDER BY AAStocktrans.Code But I need a two level sort along the lines of:
ORDER BY AAStocktrans.Code THEN ORDER BY AAStocktrans.Date but obviously that is not correct - cannot use THEN. what I do use to make this work?
View 2 Replies
View Related
Jan 17, 2014
I've created several access databases to which I've connected my excel files. However I've either deleted/moved these databases but my connections are still showing in Data -> Existing Connections tabs.
I've tried going to C:Program FilesMicrosoft OfficeOffice14QUERIES but no such queries exist.
Is there any way to clear my list ?
View 2 Replies
View Related
Jun 12, 2014
The main objective of the code is to create different workbooks that are going to be send to different people(with only their data).
I am using the filter to delete the data that I don't need and then create a new workbook, save it and close it. This has been working fine.
The issue is that I have two slicers in the 'DistMenu' sheet. When I open again the newest workbook, they slicers are disconnected to the pivot tables. This can be fix manually by selecting the slicers --> PivotTables Connections etc
I use the macro recording to get a code and work from there, but is giving me error '1004' "Unable to get the PivotTables property of the Worksheet class".
View 1 Replies
View Related
Feb 15, 2013
The main objective is to create a, UDF with 2 parameters (Currency, Date) in order to display currency exchange rate based on the currency and date. Let's say I want to find an exchange rate between USD/ EUR, then I would have a formula something like "=FX(EUR, 12/31/2011)". All currencies will be against USD.
The trick is that it will have to be created as Add-On so that it can be used in any Excel spreadsheets. The second trick is that I cannot have VBA to make any internal connections to other workbooks or applications.
So the only thing that I can come up with would be to write a VBA code that would include all values and currency rates in it. The structure that I have in mind would be something like:
Code:
Function Fx(Vl As String, Dte As Date)
Dim Rte As String
If Vl = "EUR" And Dte = "12/31/2011" Then
Rte = 0.7723
ElseIf Vl = "EUR" And Dte = "12/31/2010" Then
Rte = 0.7546
[code]....
The above code does not work!There will be at least twenty different currencies and at least 15 periods.
View 2 Replies
View Related
