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?
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 ?
I have 3 Databases for three different departments, namely Finance, Services, IT. These 3 databases are housed in the same workbook - Sheet 1(named Finance); Sheet2 (named Services) and Sheet3(named IT).
They each have the identical columns and column headings, but I have to maintain them for each of the departments.
I created an Excel UserForm to enable me to input data onto the first database (i.e. Finance). In the VBA editor, lefthand side, it has the name 'frmFinance', and all the codes are pasted on the righthand window of the 'frmFinance' sheet.
Is there anything I can do to be able to use the same UserForm to enter data onto the other two databases without having to create a new UserForm for each one of them. As indicated above, the three databases have identical columns and headings, and the data entered onto each of them are identical. The only difference is that they are for three different departments and I have to maintain each one of them - albeit in one Workbook.
I have a "form" type spread sheet that I have created where the user can enter different information (like name, company, addresses etc.). I have figured out how to create a drop down list. But what I would like to do is create on a seperate sheet a "data base" of information which will be "copied" into the appropriate cells on the first sheet depending on what the user selects from the drop down list.
The drop down list will list company names, which are stored on the second sheet. However, each company will have other data such as the company address (which will be entered in seperate cells with information like, box, street, town, province, country, code).
Now this is the clincher... The user must be able to add to the data list on sheet two and any new entry must automatically appear on the drop down list on sheet 1, and when selected it's "data" must be transferred to the appropriate cells on sheet one from sheet 2.
I have two huge database and need to test if Employee name and ID in database A matches to name and IDin databse B. What formula could I use here?
Employee ID Database A
Employee Name Database A
Employee ID Database B
Employee Name Database B
1
JONES JR,STEPHEN W
1
NULL
[Code] ...........
I have a dataset from Oracle that includes a study patient ID, study visit that a UC sample was collected at, date the sample was collected, and sample type. I also have a dataset from Quest, where the UC samples are sent to for testing, that contains similar information.
I need to reconcile this data, making sure that patient IDs, study visits and collection dates all match up with out using just vlookups and having to check the reverse of each (vlookup Quest into RDC, then vlookup RDC into Quest) and I've been wracking my brain trying to figure out VBA code to get my data from Oracle (orange header) to merge with Quest (blue header) and merge on Subject ID and Visit Name only.
In exhibit C, this is what ideally I would like to see as a result, data points from both are reported, but if Oracle subject id = Quest subject ID and Oracle Visit Name = Quest Visit Name, they are reported in the same row. I can then just run a simple formula to compare the collection dates and also identify discrepancies between visits (example: exhibit C, rows 10 and 11, these two UC samples look like they should reconcile because the collection date is actually the same for both, however either the study site or Quest is reporting the wrong study visit name).
I am generating sets of data in Sheet2 to be used for Sparklines in Sheet1. These Sparklines will sit in Column D alongside other pieces of data. The data needs to be sortable by Column A which is a list of Cities.
When sorting my data, Z-A for example, all the relevant data sorts just fine but the Sparklines do not move with the rest of the data.
Is there a way to make Sparklines move with Column sorts?
I have a formula which sorts data so all data that has EUSTON in column C will be shown first and then then all data which has EUSTON in column D once its been sorted.
=IF(C1="EUS",0,1)
What im trying to do is to be able to add BLETCHLEY so it does the same thing.
I need a formula that compares 2 databases and returns a count if both values or within the specific range.
Column A is titles Column B is values The first database is simple the range is
I have a file called "registrantExport" with "_great north", "_new england", "_new england", etc. I'm trying to sort them by Division and then by Last Name. Here is the macro I've tried to make, however it doesn't work properly, as it only applies to great north. How could I make so that it works for any worksheet I apply the macro to?
Code:
Sub macro1()
'
' macro1 Macro
'
'
ActiveWorkbook.Worksheets("great north").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("great north").Sort.SortFields.Add Key:=Range( _
[Code]...
I'm trying to create a very simple map.
I've got three columns.
Member, X, Y
Naturally, the first column contains someone's name.
The other columns contain XY coordinates.
These coordinates start at 375 and end at 499 on both axes.
I'm needing the points to show the member name and xy coordinates in the popup when hovering over the point.
How do I accomplish this?
Here's some sample data.
MEMBER X Y
JOE 397 397
JIM 421 384
JANE 467 406
JEFF 389 395
I need a macro or some idea on how to sort the following numbers (I hope this makes sense!)... The problem is with the zeros get sorted to the top (or even if I have blank cells sorted to the bottom) and I need them to ignore the zeros and sort according to the value in their column but not any other column. However, I need them to be sorted in order from D, C, B, A.
A B C D
0000
4000
101000
0540
0050
6660
ie I need them in the following order.
A B C D
0000
4000
0540
0050
6660
101000
Therefore they need to sort based on the following columns.
This is because I am charting the values in a column clustered chart that need to be in ascending order.
I have a macro built that sorts my data by column in descending order depending on what column of information is selected from the drop down menu.
However I need the macro to sort column F in ascending order, and continue to sort the other colulmns in descending order when selected.
I can't get my macro to switch the sort from descending to ascending order for this one column. Looking to see if this is possible, and if so what macro formula I would use.
Here is what the macro i am currently using:
Sub ddSort_Click()
Dim iDdSortVal As Integer
Dim CurrSheet As String
Dim lFirstSectionRow As Long
Dim lLastSectionRow As Long
Dim strCol As String
Dim lRptHeaderRow As Long
Dim iRptMeasColumn As Integer
CurrSheet = ActiveSheet.Name
[Code] .......
I am trying to add a macro that finds a dynamic range (number of rows changing with different data sets) sorts column B, then column A, moving blanks to the bottom. My dilemma is as follows:
I have a template with formulas that cannot be changed (I cannot paste special values, or clear the cells). Column A includes HLOOKUP formulas for cells A14:A120. Column B includes VLOOKUP formulas for cells B14:B120. The different data sets I pull in have different numbers of lines, which is why I need to maintain the formulas. However, I cannot seem to find a way to accurately sort column B first from smallest to largest and then column A from smallest to largest. In all data sets there will be at least a few blank lines that I need to move to the bottom. However, due to the formulas within the cells (column A returns a value of 0 and column B returns a value of “ “), excel does not recognize them as being blank. Here is a sample data set.
Branch
S&C Group
77770000583-DOWNERS GROVE BRANCH
MWBF2
77770000585-CHICAGO RIVER BRANCH
MWBF1
77770000587-WEST TOWN BRANCH
MWBF1
[code].....
In this case, I would like St. Charles & Algonquin to be at the bottom because column B is blank.
Below is an extensive macro that basically writes data from a user's template to a certain data sheet (in the form of rows) depending on their "service group." There have been 3 separate instances of a user saving data and instead of adding rows onto the bottom of the entire list of data, the sheet only has their data and no headers/filters. I believe it is somehow deleting all the previously written data that was on that sheet.
I should also mention that this is a shared workbook. I specifically created separate sheets for each "service group" to eliminate the possibility of users overwriting each other's data (Perviously 60+ users were saving to the same sheet and running into conflicting changes errors. Now the max users writing to one sheet is 10-12). Since it is not occurring every time, I am not sure where the error is. Could it possibly be in the sort or delete sections in bold below?
I am using Excel 2003.
Code:
Sub SaveData_Test()
'---------------------------------------------------------------------------------------
' Procedure : SaveData_Test
' Author : Julie/Cecil
' Date : Fri, 1/13/12
' Purpose : Modified Julies code to capture date entries
' associated with hours logged per category.
' Check notes attached to "sOp" string comment
' for additional details.
[Code] ..........
I have three very large columns that I need to deduplicate and dump back into three separate email campaign databases.
Here's the scenario:
First column: Largest (15,000+ records)
Second column: Medium (10,000+ records)
Third column: Small (7.000 records)
I need to know if the first column contains dupes from the second & third columns. If so, I must delete the DUPES ONLY so I have unique email addresses remaining in column 1. Then I need to know if the second column contains dupes from the first and third column. If so, I must delete the DUPES ONLY so I have unique email addresses remaining in column 2.
Then I need to know if the third column contains dupes from the first and second column. If so, I must delete the DUPES ONLY so I have unique email addresses remaining in column 3. I can't just dump all of them in one column and auto filter on "unique records only" because the three columns MUST remain separate from each other.
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.
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"?
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 RelatedFrom 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 RelatedI 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.
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.
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
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...
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
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".
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].....
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.
The following code deletes a specific connection in a workbook:-
ActiveWorkbook.Connections("text123").Delete
How can I code this so that I can delete ALL connections in a workbook without having to specify the names (as these will always be different).
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.
