Email:      Pass:    Pass?
Close Window   
TE
Free Excel Tips in Your Email!
Join Over 20,000 Happy Subscribers!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:


Advertisements


Free Excel Forum

Excel, Reference A Cell For Parameter In Sql To Import Data

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I am using SQL in Microsoft Query to import data from a ODBC data source
into Excel. I am linking mutiple tables from the source and this seems
to prevent from being able to reference a cell in the spreadsheet as a
parameter value. The import works when I 'hard-code' the date in the
code, but I would like to be able to reference a cell in the spreadsheet
so the end user can refresh the data after just changing the date in the
spreadsheet. Here's a portion of my code:

SELECT trim(gltrans.ref), GlTrans.Value
FROM GlTrans INNER JOIN SYSFIL
ON (GlTrans.SUB_ACCT = SYSFIL.DB_SUB_ACCT)
AND (GlTrans.ACCT = SYSFIL.DB_ACCT)
AND (GlTrans.DEPT = SYSFIL.DB_DEPT)
AND (GlTrans.BRANCH = SYSFIL.DB_BRANCH)
AND (GlTrans.COY = SYSFIL.DB_COY)
WHERE GlTrans.Period_Date < '2006-06-01' AND
(SYSFIL.Module_Type='VHS' AND SYSFIL.Acc_des='RT') AND
GlTrans.Year_End_Date =
(SELECT current_year_end_date FROM company WHERE coy = (SELECT db_coy
FROM sysfil WHERE acc_des = 'debtor'));

What I would like to do is replace the '2006-06-01' date with the date
supplied in cell C1 in my spreadsheet. I do have the date formatted
correctly in the cell.

I have searched for resolutions on the Internet, trying several
examples, but have not found the correct resolution yet. Any help is
appreciated.
Brian
Business Analyst
JDIS

*** Sent via Developersdex http://www.developersdex.com ***


View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Basic Web Query in Excel - Import Data from the Web into Excel
- Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web
Return the ISO Standards Start of the Year in Excel - First Monday of the Year - UDF
- Return the date of the first Monday of any year with this UDF ISO Year function in Excel. This is a really simple and e
Format Cells in The Short Date Number Format in Excel
- This free Excel macro applies the Short Date number format to a selection of cells in Excel. This number format display
Get Values from a Chart
- This macro will pull the values from a chart in excel and list those values on another spreadsheet. This will get the s
Remove (Delete) Hyperlinks from Worksheets
- This macro will remove all hyperlinks from the active worksheet. It will delete the hyperlinks but it will not delete t

Similar Topics







Hello friends,

I have an Access VBA program that will run great one day and next it will lockup. I don’t get an error message when it lockup, I just know because when it locks up it just sit with the hour glass and I have to close the db and restart it and it runs fine.

I cannot figure out when it keep doing this, can anyone help me?

Code:

[Option Compare Database
Global Date2 As String
Sub ImportGLTrans()
Dim rs As ADODB.Recordset
Dim sSQL As String
Dim IntVar As Long
Dim UserId As String
Dim Date1 As String
Dim Password As String
MsgBox Date2
DoCmd.Maximize
DoCmd.Echo True, "Export GLTRANS "
DoCmd.Hourglass True
DoCmd.SetWarnings False

On Error GoTo ErrorHandler

Set LawsonCn = New ADODB.Connection


LawsonCn.ConnectionString = "Provider=Lawson.LawOLEDBC;Data Source=h6h489.tcn.org;Prompt=Complete"

LawsonCn.CursorLocation = adUseServer
LawsonCn.Open

'EmptyTable ("TBL_GLTrans")
CurrentDb.Execute "DELETE FROM TBL_GLTRANS"

Set rs = New ADODB.Recordset


rs.ActiveConnection = LawsonCn
rs.Open "dme:PROD=PROD&FILE=GLTRANS&FIELD=COMPANY;FISC AL-YEAR;ACCT-PERIOD;CONTROL-GROUP;SYSTEM;JE-TYPE;JE-SEQUENCE;LINE-NBR;OBJ-ID;STATUS;ACCT-UNIT;ACCOUNT;SUB-ACCOUNT;SOURCE-CODE;DATE;REFERENCE;DESCRIPTION;BASE-AMOUNT;UNITS-AMOUNT;POSTING-DATE;ACTIVITY;ACCT-CATEGORY;TRAN-AMOUNT;ORIG-PROGRAM;OPERATOR;RECONCILE;EFFECT-DATE;UPDATE-DATE;APDISTRIB.PO-NUMBER;APDISTRIB.VENDOR;APDISTRIB.INVOICE;APDISTRI B.DESCRIPTION&SELECT=COMPANY%3D07%26UPDATE-DATE%3E12/31/07"
Set rsGLTrans = CurrentDb.OpenRecordset("TBL_GLTRANS", DB_OPEN_DYNASET)
rs.MoveFirst
IntVar = 0
Do While Not rs.EOF
IntVar = IntVar + 1
Date1 = Format(rs.Fields("UPDATE-DATE"), "mm/dd/yyyy")
If Date1 = Date2 Then
rsGLTrans.AddNew
rsGLTrans!COMPANY = rs!COMPANY
rsGLTrans!FISCAL_YEAR = rs.Fields("FISCAL-YEAR")
rsGLTrans!ACCT_PERIOD = rs.Fields("ACCT-PERIOD")
rsGLTrans!CONTROL_GROUP = rs.Fields("CONTROL-GROUP")
rsGLTrans!R_SYSTEM = rs.Fields("SYSTEM")
rsGLTrans!JE_TYPE = rs.Fields("JE-TYPE")
rsGLTrans!JE_SEQUENCE = rs.Fields("JE-SEQUENCE")
rsGLTrans!PO_NUMBER = rs.Fields("APDISTRIB.PO-NUMBER")
rsGLTrans!LINE_NBR = rs.Fields("LINE-NBR")
rsGLTrans!OBJ_ID = rs.Fields("OBJ-ID")
rsGLTrans!R_STATUS = rs.Fields("STATUS")
rsGLTrans!ACCT_UNIT = rs.Fields("ACCT-UNIT")
rsGLTrans!ACCOUNT = rs.Fields("ACCOUNT")
rsGLTrans!SUB_ACCOUNT = rs.Fields("SUB-ACCOUNT")
rsGLTrans!SOURCE_CODE = rs.Fields("SOURCE-CODE")
rsGLTrans!R_DATE = rs.Fields("DATE")
rsGLTrans!R_REFERENCE = rs.Fields("REFERENCE")
rsGLTrans!R_DESCRIPTION = rs.Fields("DESCRIPTION")
rsGLTrans!BASE_AMOUNT = rs.Fields("BASE-AMOUNT")
rsGLTrans!UNITS_AMOUNT = rs.Fields("UNITS-AMOUNT")
rsGLTrans!POSTING_DATE = rs.Fields("POSTING-DATE")
rsGLTrans!ACTIVITY = rs.Fields("ACTIVITY")
rsGLTrans!ACCT_CATEGORY = rs.Fields("ACCT-CATEGORY")
rsGLTrans!TRAN_AMOUNT = rs.Fields("TRAN-AMOUNT")
rsGLTrans!ORIG_PROGRAM = rs.Fields("ORIG-PROGRAM")
rsGLTrans!R_OPERATOR = rs.Fields("OPERATOR")
rsGLTrans!RECONCILE = rs.Fields("RECONCILE")
rsGLTrans!EFFECT_DATE = rs.Fields("EFFECT-DATE")
rsGLTrans!UPDATE_DATE = rs.Fields("UPDATE-DATE")
rsGLTrans!VENDOR = rs.Fields("APDISTRIB.VENDOR")
rsGLTrans!INVOICE = rs.Fields("APDISTRIB.INVOICE")
If rs.Fields("APDISTRIB.DESCRIPTION") > " " Then
rsGLTrans!R_DESCRIPTION = rs.Fields("APDISTRIB.DESCRIPTION")
End If
rsGLTrans.Update
rs.MoveNext
Else: rs.MoveNext
End If
Loop
rs.Close
rsGLTrans.Close

'TerminateLawson
LawsonCn.Close
CurrentDb.Execute "Qry_UpdateTranAmount"
Set LawsonCn = Nothing

MsgBox "Load completed successfully!", vbExclamation, "GLTRANS Load"
DoCmd.Hourglass False
Exit Sub

ErrorHandler:

MsgBox Err.Number & " " & Err.Description & " Please contact Systems Development", vbCritical, "AAL Software"

End Sub





Hi,

I have created a query (tblSvcVolumesQuery) that accepts a parameter to filter in access. So in Access I can pass the parameter manually and the query is filtered.

Now using Excel 2003, I import external data from the database in access.

Data > Import External Data > Import Data > Select the Table tblSvcVolumes

The data from that table is loaded into the excel file perfectly fine. Now I right click on any value of the data and select Edit Query. The Edit OLE DB dialog box appears with the table name.

Now I type the following, 'exec tblSvcVolumesQuery 2008'. The 2008 is the parameter value that the data in the table is being filtered on. This works fine as well.

However, I want to pass the parameter value dynamically from a cell in the excel file itself. So basically instead of 2008 I want to put a cell reference so the value can be picked up from the cell and then passed to the query to filter. I have tried the following:

=Sheet!$L$1
Range("L1")
Range("L1").Value
'("L1")'
and many others but nothing seems to work. I am getting all kinds of errors such as syntax error, unknown name, data type mismatch, etc.

I cannot use macros since we are trying to use excel services and apparantely you cannot use vba code macros with excel services.

Any help would be appreciated. Even if you can tell me that this is not possible with some sort of credible source, that is totally fine as well.


Thanks in advance.

btw, I searched in this Forum before posting and I couldn't find anything and I have looked all over the internet as well.


This may be a simple question but I can't figure out why the formula keeps breaking down.

I have an Excel Spreadsheet that anaylzes a bunch of data imported from another Excel Spreadsheet. ie... program exports to excel, I copy into my spreadsheet and it analyzes the data.

One column of the import returns strings such as... Found, Not Found, Unknown, N/A, Access Denied

I want to compare for the "Found" field and return a 1 if it's met or 0 for everything else.

I've tried several options and resulted in creating a reference cell. So copy the work "Found" from the import to a reference cell and then write a formula like If (Reference Cell = Import Cell, 1 , 0). I've also tried Exact and VLookup as well.

The problem is, each time I import data I have to copy "Found" to the reference cell for this to work. There is something with spaces, or bits, or font.... I don't know why actually but I have to manually look in the import for the word "Found" and paste to the reference cell for the formula to work.

Has anyone had this issue before or know a fix?


I have a database I need to extract information from using Excel's Import External Data, then New Database Query. One of the fields in my database is "Transaction Date" and I only want to import transactions for a certain date range that will often change. I know how to hard code the Query so I can get the transactions I'm looking for, but is there a way to have the beginning date I'm looking for say in cell A1 on my spreadsheet and the ending date in cell A2 and then have those dates passed to the query so when I refresh the data I get the transactions for the dates I'm looking for?

Thanks!!


I am importing an Ingres table using ODBC and want to use a parameter query linked to a cell in the worksheet.
Problem 1
The query works fine in the query editor if I use only one criteria for a date field >[Start Date].
If I try to add a second criteria like "Between [Start Date] And [End Date] " The query causes the following error "SQL data type out of Range"
If I check the parameters under the view menu the first parameter shows a valid date data type but the second parameter does not have a data type and it cannot be edited.
Even when using only one parameter if I try to return the data to excel I get what looks like an ODBC error. If I enter a date in the query as opposed to a [parameter] the data is returned to the worksheet fine.
I had success with this in earlier versions of excel but no luck with excel 2007.


I am wondering whether it is possible to use a cell reference as a parameter to change the target file for a MS Query and to do so for new files without first setting them up as a data source.

My situation is the following: I have remote sales reps posting weekly sales data via excel. They create a new file name for each week in the form 'YYYYMMDD-AB.xls' where AB is the sales rep's initials. The excel files are set up in database format with a single worksheet which is always called "input".

I have a master spreadsheet which performs two functions:
A. for a given week displays sales data by rep
B. for a given rep displays historical sales data

Currently I am using Harlan Grove's pull function but I am now at the point where it is takes forever to recalculate the spreadsheet each time (impatient management, urgghh!).

I thought one way to achieve this with more speed is to use MS Query to pull the relevant data into some hidden worksheets in the master spreadsheet and then reference to these sheets.

I have three questions in this regard:

1. Is it possible for an MS Query to use two cell references in the receiving spreadsheet (one cell for date and one for sales person's initials) from which the file name of the data source spreadsheet is passed into MS Query?

2. Can such a query access spreadsheets if they have not previously been set-up manually as a data source?

3. Is there some other elegant and efficient way to achieve the above without changing the sales rep's submission process?

Many thanks for your advice.


Hello,
operating system is XP SP3, Excel 2003 SP3. I am a relatively new excel user and am not skilled in use of VBA. A spreadsheet with several worksheets is sent to me on a monthly basis. I wish to produce a report in a new spreadsheet the orders the data in a different format (red/amber/green and date descending). My difficulty is in importing the data.

I use Data>Import External Data>select the appropriate spreadsheet that contains the data>select the first worksheet listed which does import the data from that worksheet.

My problem is that I want to import the data from all the worksheets and not import the first 3 rows from each worksheet (headers). I cant select all the worksheets using shift or ctrl.

I can sort out the ordering of the data myself.

Any advice would be very welcome.

rgds

Graham


Hello,
operating system is XP SP3, Excel 2003 SP3. I am a relatively new excel user and am not skilled in use of VBA. A spreadsheet with several worksheets is sent to me on a monthly basis. I wish to produce a report in a new spreadsheet the orders the data in a different format (red/amber/green and date descending). My difficulty is in importing the data.

I use Data>Import External Data>select the appropriate spreadsheet that contains the data>select the first worksheet listed which does import the data from that worksheet.

My problem is that I want to import the data from all the worksheets and not import the first 3 rows from each worksheet (headers). I cant select all the worksheets using shift or ctrl.

I can sort out the ordering of the data myself.

Any advice would be very welcome.

rgds

Graham


I am wondering whether it is possible to use a cell reference as a parameter to change the target file for a MS Query and to do so for new files without first setting them up as a data source.

My situation is the following: I have remote sales reps posting weekly sales data via excel. They create a new file name for each week in the form 'YYYYMMDD-AB.xls' where AB is the sales rep's initials. The excel files are set up in database format with a single worksheet which is always called "input".

I have a master spreadsheet which performs two functions:
A. for a given week displays sales data by rep
B. for a given rep displays historical sales data

Currently I am using Harlan Grove's pull function but I am now at the point where it is takes forever to recalculate the spreadsheet each time (impatient management, urgghh!).

I thought one way to achieve this with more speed is to use MS Query to pull the relevant data into some hidden worksheets in the master spreadsheet and then reference to these sheets.

I have three questions in this regard:

1. Is it possible for an MS Query to use two cell rferences in the receiving spreadsheet (one cell for date and one for sales person's initials) from which the file name of the data source spreadsheet is built?

2. Can such a Query access spreadsheets if they have not previously been set-up manually as a data source?

3. Is there some other elegant and efficient way to achieve the above without changing the sales rep's submission process?

Many thanks for your advice.


i am trying to import the data from one spreadsheet into another spreadsheet using Import External Data. It doesn't want to work. That said, I am able to Import the data in full using this method: Data, Import External Data, Import Data and then selecting the table name by browsing files. However, I am unable to use criteria within the Edit Query table. The alternative: Data, Import External Data, New Database Query, and then selecting Excel when asked to select a data source hangs excel and nothing happens. When hitting Escape, it flashes up the insert at pop up box but then closes the Import.

Can anyone help?

PS at the moment, both spreadsheets, source and destination, are in My Documents on my C drive.


By using reference data supplied on one tab of a spreadsheet, I need to set up a macro that will log the date onto another tab using the cross-reference of cells given the data supplied.

Example: data entry tab contains the name of a month (e.g., August), the value of a cell on another tab (EX-71). On the other tab I have the abbreviated months as column headers.

When I click the macro, it will log the current date in the cell that corresponds to the cross-reference of the row containing EX-71 and Aug on the specific tab.

Note there are 4 tabs that can be valid entry points for the date (based on the data supplied).


Hi All,

I'm developing 3 types of macros: 1 daily data import, 1 weekly data import, 1 monthly data import

Each of these macros will import data to their corresponding worksheets. Once each macro has has finished successfully, I want to prompt the user to enter a date or select a date that their import corresponds to.

For the daily data import, it should simply be a date selector/date entry of DD/MM/YYYY for example.

For the weekly data import, it should be a date selector/date entry as above but I want Excel to only allow them to pick the week ending date (e.g. every Sunday only).

For monthly data, it should be a date selector/date entry as above but I want Excel to only allow them to pick the last day of the month for example (e.g. 31st August). I'm not sure if this would be problematic because of February's each year so maybe this could just be a month chooser.

Once this has been entered/selected, I want this date value to be pasted as a value to a specified cell, e.g. A1, for specifically named worksheets, e.g. "DailyImport1"..2..3,"WeeklyImport1"..2..3, "MonthlyImport1"...2...3)

Is anyone able to:
a) Confirm if this would be possible?
b) Help with the code for this?

Any help would be greatly appreciated.

Many thanks,

Ravi

I am looking for a macro that will import the data from the same spreadsheet each day and add a parameter to add the date to the date field. The date field is not in the spreadsheet.

If the date already exist, then cancel the process.

It would be great if I could run this from a spreadsheet.

Thanks


I wrote a small VBA routine that imports the data from an Excel spreadsheet
into another spreadsheet.

The second spreadsheet contains a button. When I click on the button it
opens a dialog where I can select the first spreadsheet and import data from
it. I haven't included any error handling, the data seems to import without
problems.

Here's what's weird. After I've imported the data, my computer system's
date/time are change. I've noticed that the system's date matches the date of
the file that I've just imported. I can't figure out why. Has anybody
experienced this? I can provide the code if needed. I'm using Excel 2002.

Franco



I'm trying to make a parameter query using a cell as a holding spot for the parameter. I ciick connections, then properties, then the definiition tab, then parameters. One choice there is "get the value from the following cell". I click on the cell, it puts in a cell reference, then I ok everything and back out. It works fine, but when I go to save the spreadsheet, then retrieve it, the cell reference is gone! Excel crashes if I try to do a refresh of the data without going back to that spot and reentering the cell number. How do I fix this?


I have set up an database connection (oracle). I have my SQL working like a charm and the data is returned to the spreadsheet as advertised.
What I would like to do is have the DATE in the SQL reference cell "F1". That way anyone can simply change the date and the query will execute automatically providing the user with that dates data.

SELECT A.UAB_USER_ID_AUTHOR ,
SUM(A.UAB_TOTAL_AMT) ,
COUNT(*)
FROM UAB A
WHERE TRUNC (A.UAB_DATE_CREATED) = TO_DATE ('01/03/2008','MM/DD/YYYY')
GROUP BY A.UAB_USER_ID_AUTHOR;

Thanks everyone!
Using Excel 2003


Hi everyone,

I use an Excel 97 spreadsheet at work to track stats for a number of
employee's. I have a problem with it, and I was wanting some help.
First, the basic structure of the sheet is thus:

*Individual worksheets for each employee, which just (as the rows),
have the days of the week, and in a couple of columns, have the actual
stats for each day:

Col B Col C Col D Col E
John Smith Bill Jones
13/2/2006 100 50 200 120
14/2/2006 120 70 100 98
etc

*One 'control' (or summary) worksheet. This sheet just has the days of
the week on it. Under each day, there are 5 lines (one for each
employee), and the cell's for each line reference the raw data from
the individual employee worksheets:

13/2/2006 Column B Col C Col D
John Smith 100 50
Bill Jones 200 120
etc
14/2/2006
John Smith 120 70
Bill Jones 100 98
etc

Now, here is the problem. I create 5 days at a time on the control
sheet (ie just the working week). So, in this example, I would
currently have 13/2/2006, 14/2/2006, 15/2/2006, 16/2/2006, 17/2/2006.
What I do at the end of the week is to highlight the entire area of
the last date (17/2/2006), Edit-Copy and then Edit-Paste onto a new
blank line. I do this 4 more times (to create my new 5 day week),
change the dates to reflect the correct ones (in this example, all 5
new days would have 17/2/2006 on them), and then I update the cell
reference's manually. This updating of cell references is the
problem. As each date on the control sheet has basically 10 rows in
it, when I copy and paste to a new day, the cell reference's in the
pasted copy have been updated by 10. However, the cell reference's
(which refer to the individual worksheets), actually should only be
increasing by 1 (as each date on the individual sheets only uses 1
row, not 10 like on the control sheet).

What I basically have to do when I update the cell references, is
check what cell should be referenced, highlight the cells for each
date, and then Edit-Replace. What I basically want to find out, is
there a way in Excel, to force it to only increase each new cell
reference by 1, instead of the 10 it currently does (due to their
being 10 rows in each date)?
--

Regards,

Spartacus



Hello -
Does anyone out there use the Excel Add-in called Spreadsheet Server (purchased software, not a Microsoft product)?

My company installed MS Office 2007 on my computer. Now I have this issue with all spreadsheets where I am using the Spreadsheet Server Add-in:

I want Cell A10 on Worksheet C to reference the date (no formula) entered in Cell A5 on Worksheet A.

So, I create the simple formula in Worksheet C, Cell A10: =WorksheetA!A5 by doing the following basic steps:

1. I click in Cell A10 on Worksheet C. I type "=" symbol.
2. I select the Worksheet A tab.
3. I click on Cell A5 to select that cell containing the date (no formula in this cell, just a date in date format).
4. I press ENTER.
5. Instead of the date appearing in Cell A10 of Worksheet C, it appears in Cell A5 of Worksheet C (with the cell reference formula in the address bar). Cell A10, where I wanted the reference to appear, is blank.

It seems that no matter where the the cell is in which I enter the formula, the result (and underlying formula) always appears on the destination worksheet in the same cell the referenced date is in. For example, if I am referencing the date entered in Cell A5 of Worksheet A, no matter what worksheet or cell in which I enter the cell reference formula (Worksheet B, Cell B6; Worksheet C, cell H19; etc.), the result will always be in Cell A5 of the destination worksheet.

Can anyone help? Anyone had this issue?

Thank you,
Andi


Ok, I'm going to give this another shot. My orginal question was a little
vauge so I've rewritten below:


1) I use Data>Import External Date>New Database Query

2) I then brows to an Access data base, select the table and fields I want,
complete the wizard and return the data to a sheet in Excel.

3) The data is correct and updates correctly but the parameter button is
ghosted. I realize you have to have a cell selected within the data range
and and I do.

Any ideas?




Hello,

I have posted this question in msdn and excelforum but i didnt get any solution. So, I'm posting it here.

http://social.msdn.microsoft.com/For...d-92979bbeae2b



I have a spreadsheet( Sales.xlsx) in company webfolder. The path of the file is http://onlineFiles.company.com/SalesData/Sales.xlsx

It contains sales detail of different region.
I'm trying to create a spreadsheet Report.xlsx on my desktop with a dropdown list (EAST, WEST , NORTH , SOUTH).
If I open Report.xlsx on my Desktop , Select a Region eg. EAST from dropdown and click on the button , I want it to connect http://onlinefiles.company.com/SalesData/Sales.xlsx and import data of EAST Region.


I am searching for this for past couple of month and found that I cannot import data from Excel File on internet using ADODB or OLEDB.

The workaround for this is to 1) Download the file on my desktop
2) import the data from this downloaded file.

I need the help of expert here to achieve the above to function through VBA code. On clicking the button the VBA code should
1) Ask for webserver Username and password to login to webserver.
2) import the file from webserver to cache (it should not be visible to user)
3) get a reference to that file so that I can import the data from that file in cache.


Any help in this regards will be highly appreciated.

Thanks in advance,
Cjace


I currently have a webserver that spits out realtime data in the form of simple HTML tables.

I want to import (and periodicly refresh) the data on these web pages into Excel. This is very easy by using...

Data > Import External Data > New Web Query, and then refreshing the table whenever I want to see current data.

This is fine, but I want the data to be placed into a pivot table.

In most cases, I could simply refresh the data table from the web query, then point a PivotTable to this data table, and refresh it, BUT...

Sometimes these web queries return more than 65K rows, and our company is still using Excel 2K3.

So, I would like to import the result of the web query directly into the PivotTable.

I have found a page at Microsoft that explains how to do this (http://support.microsoft.com/kb/164020), but it requires the creation of an ODBC entry on the client machine. This is impossible, as this Excel tool could be used by any of the thousands of employees in my company. Requiring each person to create an ODBC entry would not be feasible. I need a 'portable' way of doing this.

Short of upgrading to 2007 (and bypassing the 65K row barrier), can anyone thing of a way to do this?

Thanks.


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.

is that possible?


Hi

I have a ws which needs to check the date whether it is over 90 days and then delete a cell value and add that value to another cell.

1) I do not know how to call system date in VBA
2) How to compare the date with the date in cell
3) How to remove and add cell values

The debtor ws records, when the debtor owes over 90 days, then remove the debtor to bad debt:

date - cell C3
due amount - cell D3
bad debt - cell E3

VBA if condition

if date-(cell C3)>90 then

remove cell D3
add to cell E3

end if

How should I write that VBA? does date a variable used in VBA to represent system date?

Since I did not get solution from MrExcel, I reference the link here to prevent cross posting.

http://www.mrexcel.com/forum/showthread.php?t=379223

Thank you for any expertise advice.


I was given a spreadsheet and asked to add queries to make the existing tables and charts data fed from Oracle via ODBC. I am able to create simple queries using a single hard-coded criteria and get the appropriate data. But the required queries are more complex than the MS Query GUI can handle so I replaced the simple query with the complex query using the SQL editor. I included the ? in the query filter assuming it was being replaced by whatever value method I chose (prompting or a cell reference).

I was able to get this to work one time, but I have more queries to add and when I follow the same sequence I get "Reference is not valid" error. I tried copying the sheet that contains the working query then modifying but still get the "Reference is not valid" error even when all I do is try to rename the data connection.

There are 9 queries required and changing the hard-coded filter every time I refresh the spreadsheet for a new value would be too time consuming. I want to be able to change the value in one cell and have all the queries update with the new value.

Thanks for your input


Hello,

I've got a couple of problems I could use some help with.

I have daily production data for every line we have


Line Date Prod Prod_2
N16 01/04/2006 00:00 0 0
N20 01/04/2006 00:00 752 614921
N28 01/04/2006 00:00 804 562132
N31 01/04/2006 00:00 530 443524
N32 01/04/2006 00:00 1416 951925
N33 01/04/2006 00:00 138 114702
N36 01/04/2006 00:00 327 139235
N38 01/04/2006 00:00 434 627277


We also test these lines to give us an idea of how many solid particles are
in the line (ppm)



Linecode TestDate Duration ppm
N20 19/04/2006 00:04 15 9
N36 06/05/2006 21:44 8.5 10
N44 08/05/2006 18:00 7.94 9
N41 10/05/2006 20:02 6.91 7
N28 12/05/2006 00:00 15.5 19
N16 12/05/2006 12:00 9.39 11
N32 29/05/2006 15:37 19.57 14
N41 10/01/2006 21:05 15.3 10
N31 24/01/2006 17:04 12 10
N33 16/02/2006 10:19 4.6 12



I want to have a spreadsheet where I can type in two dates and the sheet
refers to the most relative test date data and uses the particle test info
and places that next to the production line
i.e Production line N41 returns the test data supplied in January not the
later test data of May as the production date required is April. I think a
VLOOKUP is required but I'm not having much success getting a solution

I'm hoping the solution should look like this:


Line Date Prod Gas Returned Info Here
N16 01/04/2006 00:00 0 0 11
N20 01/04/2006 00:00 752 614921 9
N28 01/04/2006 00:00 804 562132 19
N41 01/04/2006 00:00 500 124506 10
N31 01/04/2006 00:00 530 443524 10



These production lines also run into tanks which is given as a data import



Line Date Tank
NP16 01/04/2006 00:00 A
NP32 01/04/2006 00:00 B
NP38 01/04/2006 00:00 A
NP41 01/04/2006 00:00 A
NP31 01/04/2006 00:00 A
NP33 01/04/2006 00:00 C
NP39 01/04/2006 00:00 B


And I want to be able to calculate how much of these particles are flowing
into each tank between the two dates I stipulated earlier, bearing in mind
that the lines flowing into each tank change day to day, and the line is
referred to as NP rather than N earlier which is throwing VLOOKUP all to pot.

ANY help on any of these requests would be much appreciated.

Thanks
R