Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  1. ebrooks54

    Excel 2003 files in XL2007 querying named range

    Tables, Views, System Tables, and Synonyms are all Checked. If the file isn't open, the Named Range is not appearing.
  2. ebrooks54

    Excel 2003 files in XL2007 querying named range

    Thanks for replying. The range does not appear to be calculated. According to Name Manager it is defined as =OTIS!$A:$BX. OTIS is the name of the worksheet. In playing with MSQry32, I have noticed that if the spreadsheet is not open, the available tables it discovers are OTIS$ and...
  3. ebrooks54

    Excel 2003 files in XL2007 querying named range

    I have an XL2003 spreadsheet that a user has created an MSQuery in to a named range in another XL2003 spreadsheet. In XL2007 it appears this only works if the spreadsheet being queried is opened first. If it is not open, the spreadsheet doing the querying gets an error message saying it can...
  4. ebrooks54

    QueryTable statement when converting from XL2003 to XL2007

    Skip, Thanks very much for the feedback. I am looking at re-recording the macro, but I am not immediately seeing how to stop the macro from doing more than I want. XL2007 seems intent on inserting headings, activating filters, and applying a table-style. All I want it to do is retreive the...
  5. ebrooks54

    QueryTable statement when converting from XL2003 to XL2007

    I have a macro (see code below) in an XL2003 spreadsheet that works fine until it is run in XL2007. It fails on the .Refresh statement with a run-time error 1004, ODBC failure. I recorded a macro to try and recreate the code in XL2007, and noted that instead of using QueryTable.Add, XL2007...
  6. ebrooks54

    Update Query using .csv file

    Thanks, Randy. That did not seem to help: "Operation must use an updatable query" was the result.
  7. ebrooks54

    Update Query using .csv file

    Maybe I am having a brain spasm. In any event, I don't know what I am doing wrong... Using Access 2007, I am trying to create an update query that will update an Access table with data contained in a linked .csv file. Anything I try gives me an error message saying that updating data in a...
  8. ebrooks54

    Range Name reference from a value in a cell

    Skip, My apologies. What you suggested did work; brilliantly. I had a typo somewhere in the convoluted statement, which now reads: =INDEX(OFFSET(INDIRECT(G7),0,4,ROWS(INDIRECT(G7)),1),MATCH(F7,OFFSET(INDIRECT(G7),0,2,ROWS(INDIRECT(G7)),1),-1)) Many, many thanks for your help.
  9. ebrooks54

    Range Name reference from a value in a cell

    Skip, Maybe I have expressed myself poorly, but this fails in two ways. The replacement for the named range "A1A5E8" in my formula has to reference the value in G7. If I just make the change as you suggest, the result is #Value!. In terms of the construct I am creating, there are a series of...
  10. ebrooks54

    Range Name reference from a value in a cell

    This may be about the most complicated formula that I have come up with as an amateur, but I am afraid it needs to be a little bit more complicated abd I can't figure out these last two steps: =INDEX(OFFSET(A1A5E8,0,4,44,1),MATCH(F7,OFFSET(A1A5E8,0,2,44,1),-1)) A1A5E8 (which is a Canadian...
  11. ebrooks54

    Strange Query Results when Nulls are involved

    I haven't noticed this behaviour before, and it really has me scratching my head... Using Access 2003 I have a table with 12,104 records in it. A query with no criteria returns 12,104 rows. A query with Message Type = 'TMSG' returns 1,312 rows. A query with Message Type <> 'TMSG' returns...
  12. ebrooks54

    Conditional Printing Variables

    This is brilliantly simple to suppress the printing of a zero value. I'm not sure that the 'can shrink' parameter is working, though. It appears that the space that the field would take is still being reserved. That's a minor issue, I can live with.
  13. ebrooks54

    Conditional Printing Variables

    Is there a way in Access to only print a value in a report if it contains something meaningful (IE non-zero)? I have a record with multiple tax jurisdictions in it, and I only want to print the description and the value, if the value is non-zero. If the value is zero, it should print nothing...
  14. ebrooks54

    Passing parameters between Excel and Access

    I thought I had a creative answer to a problem I was having, but it doesn't quite work: I have a spreadsheet that retrieves external data from an Access Table. In the spreadsheet, I have a named range called "AsofDate", which I link to in the Access Database. In the query in Access, there is...
  15. ebrooks54

    Launching Files

    An annoying feature about Excel: If you launch a spreadsheet from an email attachment or from an explorer window, and Excel is not already open, you do not get macros that are stored in personal.xls or in any custom .xla's. Apparently, they are only available if you open the application before...
  16. ebrooks54

    Macro Condition with DIR command

    OK. Learn something new everyday. When I converted the macro to a module it worked fine. Beats me as to why. Thanks for the help.
  17. ebrooks54

    Macro Condition with DIR command

    I don't see what you are getting at. What I am trying to do is as follows: If the text file is present, there are 6 queries to run and then code is run to move the text file to a backup folder. I do this by putting conditions on each line of the macro: Dir("xxxx.txt")>"" openquery ... ...
  18. ebrooks54

    Macro Condition with DIR command

    I have a macro in Access 2003 that has the following condition associated with it: Dir("Indigo POS Data.TXT")>"" In Access XP the query ran with no problems. Running it under Access 2003, I am getting a dialog box with the message: "The expression you entered has a function name that ...
  19. ebrooks54

    File Permissions in WinXP Pro

    I'm sure this must be in the forum somewhere, but I can't find it... I have a machine with WinXP Pro, and a second drive mounted. On the machine there are two users, UserA and UserB. The highest level folder on the second drive is called F:\main and it is shared. I'm not sure how I ended up...
  20. ebrooks54

    How can I compare the data on 2 excel spreadsheets?

    If I was doing this, I would create a little Access database, and link to the Excel Tables (as opposed to importing). Then use Access Queries to analyze the two files for differences. Problems like this are best handled by database programs, especiallly if there is a possiblity that there are...

Part and Inventory Search

Back
Top