I have a spreadsheet that queries another spreadsheet, using MSQuery. The query is of a named range on another workbook. What I am trying to do is change the workbook reference, to another similar workbook, with the same range name. MSQuery is not easily letting me do this. I figured there was a way to simply change the file that the created table (in MSQuery) is using. There doesn't seem to be a way to do this.
The named range is "data." This is what the SQL view looks like: ...
I need grouping and then summing in either msquery or back in excel after the data has been extracted from msquery.
I have external data that i have interogated in msquery, and I would like to to sum the amount column if possible. I have two fields, first field = account number 2nd field = amount due
the first field could contain 5 sets of the same account number, but with different amounts in the Amount Due column. I would like to sum the amounts due for each of the account numbers in one column.
If i cannot do this in msquery, how do i then go about doing it back in excel. I know i can use subtotals, but then when the data updates from the external data source.
I would like to reference cells in another tab in my worksheet, but change the orientation.
For example, in Tab 2, I want to make rows A1:A26 equal to columns A1:Z1. Do I have to click back and forth between worksheets 26 times, or is there a way around this?
I have a large excel matrix that contains many references to a Word document. The path to that document includes /ref Doc1/ in every reference. I want to change that to /ref Doc1 rev1/. Is there a 'replace' tool to do that?
I am setting up VBA code to create different excel workbooks. One of the differences is in the VBA Tools References that must be enabled to make the whole thing function.
How do I write VBA code to turn on and off specific tool references ie. if i had a reference called OrbitCOM Library how can I turn it on and off using VBA code.
This issue is similar to absolute/relative references, but it's related to the data being referenced, not the cells doing the referencing. If I have a call the references cell A3 then I insert a row above row 3 (making the original A3 cell now A4), my reference automatically changes to A4. How can I get it to stay at A3 no matter what happens?
This may sound like an odd request, but I reference a bunch of cells on a worksheet. This sheet retrieves data from a database which changes regularly. The changes that it receives are not just updated in the cells, but rather the rows are deleted and reinserted changing all my references to this sheet. For example, say I import data and it fills cells A1 to A20 and I make a reference to A15. If I change the data that is imported and the data now only fills cells A1 to A10, the reference to A15 is now set to #REF since it actually clears all cells from A1 to A20 and then reinserts new data.
How to change from absolute references to relative references.
Example : ws.Range("G" & NextRow).Formula = "=" & Range("H" & NextRow).Address & "+" & ws.Range("I" & NextRow).Address This code return the absolute references---> =$H$365+$I$365 , and i want change to relative references, like this ---> =H365+I365
Is it possible to create a new field in MS Query which uses an IF / IIF statement to determine it's value?
I am querying an Excel file/table, and have a field called 'Mo Nm' (month name) which stores the name of the month of a sales record, with the sales value stored in 'Sale Value'.
I'm trying to create 12 new fields in my query output to report the total sales by customer by month in a single record, rather than 1 record per month, and having to then do a Pivot Table on the result.
MSQuery is using a table called Data$ (the XL file has a sheet called 'Data', which stores the XL list being queried).
Need to create Working order (in Excel 2007) for filling machine in my factory. Working order is changeable from day to day and the deference is product quantity. Product quantity in working order [File - Working order] is changing according to the each sales plan [7-Jan-13, 8-Jan-13] - (File Working order is Linked to the appropriate sales plan in this case - [7-Jan-13]).
Anyway, I need to change the source workbook "sales plan" from [7-Jan-13] to [8-Jan-13] by changing source workbook name in red cell [File - Working order] and not to browsing location?
All source workbooks [sales plan 7-Jan-13, 8-Jan-13]are at the same location in the same folder, only difference is the source workbook names. So, is it possible to change source workbook name (File - Working order - red cell) for all external references (File - Working order - green cells) at the same time, and avoid annoying browsing?
I have an ODBC connection to a db2 database in MSQuery. I want to return the length of a string. I've tried Len(string) but it says its not in the library.
I am attempting to use MSQuery to extract records from an Oracle transaction table by passing user defined Date/Time field parameters.
So if the date range for the query is for Fiscal Period 10 of this year, my parameters would be: [StartDate] = #2002/10/1# [EndDate] = #2002/11/1#
Daily transaction activity is in the early morning hours ending at 6:00AM so my Criteria is: Between [StartDate] + .25 and [EndDate] + .25 Note: The +.25 represents 1/4day = 6hrs (6:00AM)
If I use the hardcoded Start/End Dates, my query works fine, but if I try and pass the dates as parameters I get "ORA-00932 inconsistent datatypes" error. And I can't seem to pass the Date/Time combination successfully....
If anyone has encountered a solution for how to pass a Date/Time Paremeter into MSQuery I would love to hear how you did it. I'm starting to pull my few remaining hairs out!
[ This Message was edited by: Tuner on 2002-11-07 12:23 ]
[ This Message was edited by: Tuner on 2002-11-07 12:28 ]
[ This Message was edited by: Tuner on 2002-11-12 12:51 ]
how to pass multiple parameters using a range of cells to MSQuery? When I try to it tells me that I can select a single cell only. Anyone know of a new and improved sql driver to use with Excel?
I have a number of statements within the Sheet Event Code (Excel 2007). Three times lately I have added a column and had to go back into the code and find all of the references that needed changing to reflect the new column.
I have been working on this for a couple of days and even tried EE, but to no success.
I have read that Defined Names / Constants should be used as often as possible, but even trying that, the VBA code errors out or "hangs up". Even within Bill Jalen's book (VBA and Macros 2007), there is nothing that addresses this, especially using Intersect.
Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range
On Error GoTo mEnd Set rng = Sheets("Log").[F14:F10000] If Not Intersect(rng, Target) Is Nothing Then If Target = "" Then With Sheets("Log")
Running XL07. Need to have one workbook pull data from several dozen others.
Have columns to the right that refer to the query table.
As I add in queries to other workbooks, the time to update each individual query goes up a lot; it feels as if the update time is increasing geometrically. I'm giving up after 2 hours, for query updates that used to take
I set up formulas to count text characters in a range of cells. I'm tracking attendance and payments for a small yoga studio.
All I need to do is count "Y"s for prepaid attendance and "DI"s for drop-ins. I have the formulas working but they are absolute so inserting a row will break my sheet.
It is suppose to be that if the employee is "FT" and has worked >=4 years the return is 15. But if the employee is FT and has worked 2 years but less than 4 years then it is suppose to return 10 (these are days off) Or if the employee is FT and has worked 1 year, but less than 2 then it should return 5 days off. And all the others in the column get no days off.
I have tried to do it with structured references and with cell references I get a column of zeros!
I'm trying to create a simple Excel spreadsheet (not looking for Access or complex relational database stuff) to have the following:
A tab with data about a person : name, date of birth, join date, and a list of sessions they attend (eg. Monday Morning, Monday afternoon etc).
A different tab in the same sheet, or a form to select from that list of people, and allow changes to the information, and the ability to save it back to the data tab. Ability to add new people or delete people would be useful.
I am looking to create this as a basis for managing sessions, creating a register etc....
Any simple spreadsheet which I can use as a starting point, or to use as a reference in getting it established?
I am trying to do a query against my database using MsQuery, but I am having a problem with it...
One of the tables in my database stores information as Binary (Bits) instead of actual text.... When I create the Query in MsQuery, the text is displayed as desired...but when I send it to Excel that column is not displayed..
Does anyone have any ideas on how I can get it to send the column to Excel?
See attached for what I mean. The Bits column shows in MSQuery, but not in Excel.
What I'm wanting to do is essentially use a reference to update yet another reference.
In more detail, I want to retrieve a value on a worksheet and use that value to alter yet another reference to another worksheet.
For example, I want to retrieve a value (let say its 16 from either a local cell or a cell on another worksheet) and then retrieve yet another value from another worksheet based upon that value (lets say the worksheet I would want is then "Subject 16").
I've attempted various nesting formats of the typical reference formula but obviously have had no luck. Would I need to use VBA or is there a simple solution that I am missing? If I do have to use VBA how should I go about doing this?
for example, is it possible to check if a Reference to Set a reference to Microsoft Internet Controls (shdocvw.dll) has been enabled in user Reference settings. And if not set, then set it via VBA
I can do this by clicking on a cell OTHER than one of those four above, and do:
=A1/4 (if 15.2 was A1) and copy that formula down.
But then I have the originals AND the new ones in separate columns.
I want to just be able to divide those original numbers by 4 without resorting to using other cells, but if I do =A1/4 on A1 itself it says that i have made a "circular reference"
I'm trying to connect to SAP R/3, and I think I'm not sure which references (Tools --> References) I'm supposed have checked.
All I want to do for now is the following:
Dim sapConn As Object Set sapConn = CreateObject("SAP. Functions")
Unfortunately, I am getting a "Run-time error '-2147024770 (8007007e)': Automation error. The specified module could not be found."
I have read somewhere random on the internet that I should add "SAP:Remote Function Call: COM Support 1.0 Type Library" which links to the SAPGui Provided library librfc32.dll. However, this alone doesn't seem to work...