Close Window   
Free Ebook
Got an Excel Question?
Ask it in our Excel forum!
TeachExcel.com
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



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 Tutorials

Import a Worksheet from One Workbook to Another in Excel
In Excel, you can quickly copy an entire worksheet from one workbook to another workbook.  This allows you to impor ...
How to import Text Files (CSV) into Excel
Text files in CSV format are one of the easiest ways to store and transfer data as it is one of the most compatible ...
TRIM - Remove Spaces From the Sides of Text and Extra Ones in the Middle in Excel
This allows you to make sure that there are no hidden spaces at the start or end of the text in a cell and also tha ...
Reference Other Excel Files with Formulas and Functions
In Excel you can use formulas and functions to reference data that is stored in another Excel workbook.  This creat ...

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.


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.


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 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.


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'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


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 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


I have a pretty simple embedded query in an excel 2003 spreadsheet.
The query looks up information from our database for each work order number entered in column A.

The query as built works for one row but now I need to figure out how to get the cell referenced in the parameter value to vary as I copy the contents of the working row into row 2,3,4 etc and change the work order value in column A 2, 3, 4 etc.....

I do not know what function to use when defining the parameter value that will allow this to happen when I copy the query into a new row.

Currently the query parameter setting references cell A1 and works.
Now when I copy the query to row 2 I would like the parameter to reference A2 like when copying cells using a relative cell reference.

Here is the current SQL code that is running

SELECT wo_mstr_0.wo_nbr, wo_mstr_0.wo_so_job, wo_mstr_0.wo_due_date, wo_mstr_0.wo_part, wo_mstr_0.wo_qty_ord, wo_mstr_0.wo_qty_comp, wo_mstr_0.wo_status, wo_mstr_0.wo_vend, wo_mstr_0.wo_rmks
FROM PUB.wo_mstr wo_mstr_0
WHERE (wo_mstr_0.wo_nbr=?)

Any help pointing me in the correct direction would be a blessing.




I've done this before in another application, so I don't know why this is so difficult right now. I have a spreadsheet, and when I put a value in a cell, I want today's date to pop in another cell, but I don't want the date to update to today when I re-open the spreadsheet at a later date.

Similar to this thread: http://www.excelforum.com/excel-gene...eadsheets.html

The only difference is, my autofill of the date is dependent upon the contents of another cell. I know I have to do VBA for this, but I don't know the function to use. I thought it would be like this:



Please Login or Register  to view this content.


I guess I just don't know how to use the Target parameter. Plus, I need to avoid the infinite loop, which means I need code to only look for change in cell C4, rather than the whole sheet.


I would like to built a Pivot which gets it's data from an MS Access
query which requires a parameter, which I would like to pass from
Excel.... is this possible?

if so, could someone please supply some sample code, the issue I am
having problems with is the parameter. I do not want to create the
pivot table or querytable using a SQL string from within Excel, I want
to use the Access Query.

Help or advise would be appreciated....fast [smile]

PWS


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Hi All,

I hope somewon can help.

I have a workbook (workbook1) with 1 sheet (data source) which has around 1200 lines of data and 111 columns. Every cell is filled with data and there are unique headings accross the top.

I then have another workbook (Workbook2) which has about 50 sheets in it. Each sheet has a pivot table linked to Workbook1 via an ODBC connection.

ODBC is setup under USER DSN and is 'Driver do Microsoft Excel(*.xls)' linked to workbook1.

Problem
-------

Last year I created about 50 pivot tables in Workbook 2 with charts attached to each one. I was refreshing them every day between Jan and Feb 2006. I came to re-use them the other day and when I hit refresh I had the message:

[Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not filnd the object "Data Source$". Make sure the object exists and that you spell the name and path name correctly."

The database name (spreadsheet name), location, or sheet name hasn't changed. Nor has the ODBC link name changed. I have checked! :-(

I have tried re-pointing the ODBC link to the spreadsheet, and re-creating the ODBC link and nothing.

If I go into the Wizard and press back and click on get data I get the error:

'The query cannot be edited by the Query Wizard'


Any ideas. Any help would be very much apreciated.

Tim


I'm working on a spreadsheet that is intended to pull data from an old database and put it into a better format.

The source data looks like this in the "date" column.

7/9/2006 0:00. I used this formula to pull the quarter - =INT((MONTH(L3)-1)/3)+1.

My question is this - the company I'm at does 4-4-5 accouting, which breaks months into periods of 4wks/4wks/5wks. It's important that I identify the "period" that things happen in. I've set up the period breakdown in a seperate sheet (below) and I want to have excel scan the ranges and assign a period.

1 1/1/2006 1/27/2006
2 1/28/2006 2/24/2006
3 2/25/2006 3/31/2006
4 4/1/2006 4/28/2006
5 4/29/2006 5/26/2006
6 5/27/2006 6/30/2006
7 6/1/2006 7/28/2006
8 7/29/2006 8/25/2006
9 8/26/2006 9/29/2006
10 9/30/2006 10/27/2006
11 10/28/2006 11/24/2006
12 11/25/2006 12/31/2006

I'm sorry if this has been asked before/is asked every day - I'm new to the forums and I swear I tried to search.

Thanks,


M


Hey everyone,

I seem to remember there is a special handling of dates in Access involving #'s but cant quite remember the specifics.

I have a UDF in Excel VBA that needs to accept a data as a parameter to the function and then pass it through to a DBVLookup-esque type query. It works when I manually set a value of #yyyy-mm-dd# but I cant seem to get it working with a cell reference. Must I convert the reference to text and pass that along with the #'s?

Also am using a TOP query to find the most current rate from the database and pass that back to Excel but it seems to run really slow - was much worse when I had a correlated sub-query.

Any ideas on how I can get around this please?

Much appreciated!


Hi all,

I've got a query that works IF I enter a date in the code. I'm trying to get this to work based on a cell value rather than hard-coded. Any ideas?

Code:

 
'    Create the new RecordSet.
     Set Recordset = New ADODB.Recordset
          With Recordset
'    Define the appropriate Filter(s) and notify the user of the selection criteria.           
               Src = "Select * from mData where Date LIKE '1/3/2011' "
               .Open Source:=Src, ActiveConnection:=Connection


Any way to do something like ...
Src = "Select * from mData where Date LIKE 'Cell A1' " ??????
where the value in cell A1 is a date?

Many thanks!!
Gino




Hi, I trying to import external data (from Quickbooks) into Excel using Microsoft Query. I filter TxnDate using Between [Start Date] and [End Date] in parameter (Criteria Value). I tried to enter so many different formats in parameter value such as: 12-15-2012, 12/15/2012, 2012-15-01, etc but shows the same error "Conversion Error". I looked up on the table detail (QuickBooks), the format TxnDate is Date, Length 10, Validate: cccc-mm-dd.
I also tried to change the Criteria Value to be: >= [Start Date] and <= [End Date], still the same problem.
It works if I enter sample date such as #12-15-2012# in Criteria value. I really appreciate if there is anyone who can help me to solve this problem. Thanks so much!

Hi! I am trying to import following .txt file to Excel by using Get External Data -> From Other Sources -> MS Query.

Date Value date Maturity 7.3.2006 8.3.2006 8.3.2032 7.3.2006 8.3.2006 8.3.2032 21.6.2006 21.12.2006 21.6.2011 9.10.2006 9.10.2007 9.10.2013 9.10.2006 9.10.2007 9.10.2013 17.11.2006 17.11.2006 21.6.2011 30.11.2006 4.12.2006 5.12.2016

I am using Microsoft Text Driver as a driver and from "Define Format" I choose Format = Tab Delimited. However, I must be doing something wrong because the Query doesn't separate the columns. So as a result I have only one column with Data_Value data_Maturity as column name.

Any ideas what could be the reason?


Hi

I am trying the import data into excel (using excel data menu) from a table/query I have made in Access so I can then use a little macro to refresh it automatically. But I am having challenges...

This works with other tables but not the ones I have built, anyone know what I am doing wrong?

Im doing the following:
In excel: Data>Import External Data>New Database Query>Select New Data Source, click OK>1.Enter a name>2.select "Driver Do Microsoft Access[*.mdb]">3.Connect to the database/table, click OK>4.select a default table,click OK>then OK again>In the next screen "Query Wizard - Choose Columns" I can see the name of the table/query but in the +/- drop down there are no column names to select??

Does anyone know why it is not pulling through the column names? What might I be doing wrong here?

Any help will be greatly appreciated as I have already spent hours trawling the web and testing.

Many thanks

Cross-post he http://www.mrexcel.com/forum/showthread.php?t=486877


I am trying to compare the date in an active cell to a reference date in cell U1, using:

If "U1" > (ActiveCell) Then

I've got a loop that is evaluating this in turn for each cell in the B column. Sadly, Excel is always interpreting the above statement to be true, even when U1< the active cell (i.e. the active cell is more recent than the reference date).

Here's the full code: Sub test4()
Sheets("Consolidate").Select
Range("B2").Select
Do
If "U1" > (ActiveCell) Then
ActiveCell.EntireRow.Delete
Else: ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell.Offset(0, 1))
End Sub
When I run this code, Excel considers each row and then deletes it, regardless of whether the U1 is greater than or less than (i.e. newer than or older than) the active cell.

As a reference, to make sure that I'm doing things correctly, I entered the formula "=U$1>B2" into cell C2 and filled down the whole C column. This correctly gave me "true" when the corresponding B cell was older than the reference date and "false" when it was newer, so the code above is my effort to duplicate that formula in VBA code.

Can anyone tell me what I'm doing wrong?


Hi All

Is there a way to launch 'Select Data Source' to propmt user to select a .html file when using a VBA or Macro. In excel 2003?

I wanted to create a button on sheet 1 that linked to a macro which performed the following

When the button is clicked Cell A1 on sheet2 is selected
click Data import external data import data, to launch the Select Data Source window for the user to select the .html file to be imported.
Once the user has selected a .html file and clicked Open the marco continues to
click Import
Target cell exsiting worksheet A1 sheet2
click OK
Please can you suggest VBA which would perform the above?

import data,Select Data Source,VBA


I have what I think is a pretty simple question.

I have a column (A) of stock symbols (IBM, WMT, CAT). As the first part of this project, all I want to be able to do is to make a query to yahoo finance to get the name.
What I do is this.

1.Click on cell B1 and go to Data -> From Web

2. the web query window comes up and in the address I type http://download.finance.yahoo.com/d/quotes?s=IBM&f=n
- I import this into B1 and I get the correct name "International Bus" in column B1

3. I now click B1 and right click and choose "Edit Query".

4. I substitute IBM for ["CellToIncludeHere"] so the query now reads
http://download.finance.yahoo.com/d/quotes?s=["CellToIncludeHere"]&f=n

5. I click on import and I get a prompt with the label CellToIncludeHere as expected. I enter a Cell Value of $A1 (note that I hope the column is fixed and the row is not)

6. I enable both "Use this value/reference for future refreshes" and "Refresh automatically when cell value changes"

7. Ok - so that didn't work (as expected). I get text '$A1' (without the 's) in the cell B1.

8. .. but now I can right-click on cell B1 and I now get the option to "Parameters" so I click on that

9. In this window I check "Get the value from the following cell:" and enter "=$A1" (without quotes) again, and enable "Refresh automatically when cell value changes". Click OK

10. This seems to work, it gets the IBM stock ticker from A1 and uses that in the query. I can change A1 to WMT and the name changes to "Wal-Mart Stores"

NOW for the tricky part

11. I enter another stock symbol in A2 (XOM - Exxon)

12. I select B1:B2 and hit Ctrl-D (copy down)

13. This does NOT pick up XOM but instead is still the same query as B1 with the parameter CellToIncludeHere and that parameter points to $A1 which is IBM.

So the question is - How do I get the query string to get the value from column A and the corresponding row and simply use that value in the query without the named parameter (CellToIncludeHere) being there? It seems like a simple thing but I'm not getting it!

Thanks for your help!