Using Excel 2013. Is it possible to delete some records from Sheet1 if the records exist on Sheet2?
I tried several SQL variations in MSQuery but all come back with error messages.
I tried:
[Code].....
I don't have Excel 2013 so unfortunately I can't easily create relational data in Excel 2010. I'm looking for a solution to a design problem. I'd like to have 2 tables which I can join with Microsoft Query and run pivot table reports.
My department processes payments, both for internal clients and external clients. My Payments table looks like:
Date | Type (internal/external) | Operation (what type of payment) | Method (internet, mail, etc) | Quantity
Additionally, I have a table for Mail Opening, which looks like:
Date | Employee | Operation | Quantity
My overlapping fields are Date and Operation. Using each table individually, I can get nice pivot table reports. What I'd like to do though is be able to not just see what operations and methods were run each day with what quantities, but also to compare that to how much mail was opened. Employee and Operation is a multi-multi relationship, so when I join by date, I end up getting incorrect numbers because of problems with the data layout. I'm open to changing my data structure, as I know the way it's set up right now isn't great, but I'm having a mental block on how to redesign it. I attached a sample workbook.
I am trying to use queries I have been running in MS SQL Server Management Studio, to return data in Excel where it would display as pivot. Some queries I was able to use through Excel but few others are not returning anything.
I am doubting it has something to do with the query itself - but they are displaying results properly in MS Query, they are just not returning any data to Excel.
Im using a query to connect to a SQL server and return data into Excel. I can query and return the data in Microsoft Query editor but when I attempt to return the data to Excel in a table, it just says the name of the connection in cell A1. If I attempt to return it into a pivot table a get and "Problems obtaining data" notification.
View 1 Replies View RelatedI have a query which is giving me some trouble when returning the data from ms query. The query was written in mysql query browser and returns 2 columns of data - a date and a number. MS Query correctly processes the query but when the data is returned to excel only the second column is returned. Here is the query:
SELECT
(SELECT
max(l.the_date)
FROM
nc_view_date_functions AS l
WHERE
l.week_of_year = d.week_of_year
AND l.yyyy = d.yyyy
) AS week_end_date,
count(r.consent_id) AS weekly_count
FROM
rg_resource_consents AS r
INNER JOIN nc_view_date_functions AS d
ON r.application_date = d.the_date
GROUP BY
d.yyyy, d.week_of_year;
I used the Get External Data function within Excel and the query returned the fields in alphabetical order. I went back into the query and rearranged the fields into a more logical order, but when refreshed the query continues to return the fields to Excel in the original alphabetical order.
Am I missing something, or do you only get one shot at the order of the fields being returned?
I would like to have set of data as Parameter if possible.
Example: In Microsoft Queries I have two data sets. In first one shows let's say Item Number, and second one I should have table with components of all Item numbers listed in first table.
So first table I set one Parameter (?) and i get like 8 results. I would like that second table takes those 8 results (sometimes more or less then 8) as parameter and shows data (components) for them.
If I set sign '?' I can only select one cell as parameter, is there any way I can select multiple cells as parameter and use it like SQL function IN ?
Code:
SELECT 1.ITMNR, 1.COMNR, 1.QTYPR
FROM 1.1.1 1
WHERE 1.ITMNR IN ?
And second table should look for results in first table under 1.COMNR and select it as 2.ITMNR
Code:
SELECT 2.ITMNR, 2.COMNR, 2.QTYPR
FROM 1.1.2 2
WHERE 2.ITMNR IN ?
I have a worksheet that pulls data from an ODBC datasource (import, External Data). The datasource is a SQL server 2005 database.
the query's sql is complex enough that it says it cannot be represented graphicly.
part of the WHERE clause fro my query specifies a date range for one of the date fields.
I am wondering if from within the SQL query in Microsoft Query I can reference the value of a cell. that way i coudl have the user enter his date range values in two specific cells.
I am querying a database in Microsoft Query and I have it set to pull in jobs that match a customers code in cell A1. I would like it so that if cell A1 is blank, it pulls in jobs for every customer.
View 3 Replies View RelatedTrying to work through tutorial on creating offline cube from an Excel file, A CompleteGuide to PivotTables: A Visual Approach, Cornell, ISBN 1-59059-432-0, pg 248. Steps I am taking:
1. Click on Data Menu
2. Import External Data
3. New Database Query
On Databases tab
4. Click on Excel Files*
5. Click on Browse Button
I migrate to the loction of the sample files I downloaded, but no files appear in the Browse Data Sources dialog window. Files of type drop down reveals only "Data Sources" (w/o quotes). I browsed the folder with Windows Explorer. There are several Excel files in the folder as well as sample, .cub and .mdb files. I do have Excel 2007 installed on the PC as well, but working through the tutroail with Excel 2003.
Is there a way to put a floating date range in the criteria much the same as can be done in excel ie greater than Today() but less than Today()+3? I don't want to keep going in and adjusting the query if I don't have to and if I don't keep the range tight there is too much data.
View 9 Replies View RelatedIn a large table what is the simplest way to delete all empty rows? Excel 2013.
View 13 Replies View RelatedI am importing data that contains specific start dates and I was wondering how to filter the criteria in Microsoft query to only show start dates greater than today or perhaps yesterday. This would eliminate all entries that have already occurred. It seems I can only select a date in the criteria that exists in the data.
View 14 Replies View RelatedI am using below code to delete the sheet from workbook.
For iTemp1 = Workbooks.Item(funEach.Name).Sheets.Count To 1 Step -1
Sheets(iTemp1).Select
If UCase(Sheets(iTemp1).Name) "TEMPLATE" And UCase(Sheets(iTemp1).Name) "WORKING" Then
Sheets(iTemp1).Delete
End If
Next
How can i avoid the below popup..
-Data may exist in the sheet(s) selected for delettion. To permanently delete the data press delete.
I'm making a Excel 2013 spreadsheet that has formula in a column that auto enters a number 1-40 when something is entered to the left of that cell. There are 300 rows in the spreadsheet. I would like to make a drop down list in a column cell to the right that would delete that number in that cell from the drop down list. For example cell C1 has 39, that 39 then is deleted from the drop down list. C2 has 22 in it, click on the drop down list cell and it shows 1-40 less 39 and 22.
View 11 Replies View RelatedI have an MS Access query that contains a parameter. The parameter is a date field, and I have configured that in the Access query. If I run the query within the MS Access user interface, it prompts me for the paramater value as expected, and runs just fine. However, I want to connect to this query from within Excel as a data source.
I have created a connection to the Access file using ODBC from within Excel. In the MS Query window, I am merely selecting all of the fields resident in the MS Access query, and returning all values. In other words, there is no selection criteria in the MS Query. I have done this many times with Access queries that DO NOT contain a parameter, and everything works fine. However, in this instance, I need to pass a parameter through to MS Access in order for the query to run. At the moment, I get the "Too Few Paramaters...1 expected" error message. This makes sense, because I haven't figured out how to pass the paramater to MS Access.
Is there a way to structure this that does not involve VB code? If so, I'd love to know how. I have tried creating parameters in MS-Query with the same name, but although I get the prompt it doesn't connect with the Access query as the source for the parameter value.
If the solution requires using code, I'm good with VB Code in Excel...is there VB for Excel code that could make this happen?
Failing that, I guess there must be (I've seen a few in my search thus far) Access VB Code that can make this work. I'm very rusty using VB with Access, so this is my least favored solution. However, if this is the only option, keep in mind that I need to pass the paramater ultimately from a user who will initiate the process using Excel.
i don't like the "- Microsoft Excel" typed on the Title Bar. how would i be able to remove it?
View 9 Replies View Relatedin the date fields excel only recognises some of them as date, so when I try to change the format of dates (mm/dd/yyyy to dd/mm/yyyy) it only changes some of them so I end up with some wrong dates.how I can make excel read them all as date?
View 3 Replies View RelatedI have a Macro button in my Excel Spreadsheet, every time I close the spreadsheet it gives me this error "Microsoft Excel Stopped Working" .
I have attached Spreadsheet to show example : Inventory List.xlsm‎
Can I insert the microsoft calculator into any excel sheet? I have it attached to my ribbon. But would find it much handier embedded into the sheet.
View 2 Replies View RelatedConsider this procedure to execute a MS Word mailmerge from within Excel:
Code:
Option Explicit
Dim objword As Object
Dim odoc As Object
Dim odoc2 As Object
Dim wdsendtonewdocument As Object
Dim mypath As String
Sub merge()
[Code] ........
The application hangs on the line in red. The file name (worksheets("Frontpage").Range("B15")) exists. It hangs with periodic dialogues "Microsoft Office is waiting for another application to complete an OLE action."
I end up having to go into task manager to close the word application before I can regain Excel control again.
I am looking for an IPad App which can open Macro-Enabled Microsoft Excel Files.
View 1 Replies View RelatedI have created several reports containing pivot charts with slicers, pivot tables and data sets. Total file size is around 5MB. Need to do an update in my pivot table Like adding an additional grouping on one of my dimensions. As soon as I start doing so the Microsoft Excel stopped working error message pops up and my file crashes! Removing existing groupings seems for some reason to be impossible as well: nothing happens when I do this. Ahow to solve this without having to rebuild all my reports?
View 1 Replies View RelatedI have a macro set up in Excel that formats and deletes rows matching a citeria. Once the macro runs I then manually copy the data across into Word. I would like to automate this.
I would like the macro to copy over any cells containing data iinto a new word document. I also have standard text that I would like to include at the beginning and end of the word document. With the excel data being placed in the centre.
I have searched the web and tried a couple of macros with no luck. All the macros state "' requires a reference to the Word Object library: ' in the VBE select Tools, References and check the Microsoft Word X.X object library"
I am unsure how to reference this - but I have checked and found that the object library ticked is Microsoft Word 11.0 Object Library.
I need to generate a microsoft word document from the data encoded in my excel spreadsheet. I am currently using MS office 2007.
in the Excel Spreadsheet from columns C to F "a) b) c) d)" was not typed but in the word document it automatically appears before the choices encoded in excel. Another thing is that some of the choices typed in the excel spreadsheet are in bold font and I want it to be generated in word document with the bold font as well.
By the way I am planning to use this technique to create a 100 item multiple choice exam for my students as I find using EXCEL to generate the document a lot easier than creating the test manually in WORD.
I am trying to add a calendar picker to Excel 2010. Everything I read online says that it is possible, you just need to use the ActiveX control "Microsoft Date and Time Picker Control 6.0", and that it should be listed under Developer Tab -> Insert -> More Controls ->... But it's not listed.
I also tried downloading the Microsoft Windows Common Controls-2 6.0, but when I tried to add it to Excel using "Register Custom...", it says "Cannot register this control".
Why this control is missing? and I can't add it?
I was trying to use this guide to add a date picker in:
[URL]
and noticed that the date picker does not appear in my active x controls list.
I am definitely on Excel 2010 so it should be there right?
What my excel sheet is doing is importing a bunch of data from a query. I have all my queries linked to buttons through macros. There are going to be 20-30 buttons that are linked to a query, and each query will produce a different amount of data(in this case, account numbers). When pressing a button linked to a query(i also have one 'Clear' button that just deletes the data, thats all), i use a macro to delete the previous query so it can import the new query into the same cell. The problem I am having is that when a button is pushed and there is no query, I am getting an error message. Here is my original code(macro), and the underlined code is where i am getting the error
View 3 Replies View RelatedUsing EXcel 2013, Windows 8
I have an Excel worksheet with one column being e-mail addresses. Other columns are Christian names, etc
Ideally can I create a full Mail merge with Outlook using whatever data I want. But probably just e-mail address and Christian name?
Otherwise be able to send one e-mail to all the e-mail addresses, without a major re-type.
I am currently working on a project that uses Excel to parse a .txt document. Its working quite well for me. It functions by having two worksheets. In the first worksheet I use the " import external data" menu to import my .txt file. In another worksheet I have set up fields that show only the important information from the .txt file and leave the junk behind.
I’m looking to improve the functionality of this by adding a button to automatically clear the data in the first worksheet so that new data can be added quickly.
I have searched the forum and found a couple of threads on "QueryTables". After reading up on those, I have made a simple button with the following code.
Sheets("Sheet1"). Cells.Clear
Sheets("Sheet1").QueryTables.Item(1).Delete
After I load a .txt file and parse it using the formulas set up, I copy my needed information and then press this button. The cells clear, and the QueryTables are "reset" (maybe not the right word). Now a different .txt file can be imported and the process starts again.
The problem I am having is that. If there is no "QueryTables.Item(1)" to delete, I get a run time error. (Run-time error '9': Subscript out of range).
Would anyone know how to make my button conditional to having a "QueryTable" active? I.E. If I press it when there is no data loaded it doesn't do anything or give me that error.