Data Menu

TheMSsForum.com: The Microsoft Software Forum

The MSS Forum ‹ Excel
- Archive
  - Biz
  - MCSE
  - CRM
  - Drivers
  - Framework
  - ADO
  - ASP
  - Compact
  - Forms
  - Dotnet
  - C#
  - VB
  - FontpageGen
  - Excel
  - WorkSheet
  - Exchange
  - Setup
  - Fox
  - Fontpage
  - ASP
  - IIS
  - Entourage
  - Money
  - Messanger
  - PocketPC
  - Powerpoint
  - Project
  - Publisher
  - Excel
  - VB
  - Security
  - Portal
  - Services
  - SQLServerDev
  - SVCS
  - SQLServer
  - VB
  - VC
  - MFC
  - ExcelGen

- Previous
  - 1
    - Same series, different coloured line? If I have a continuos data series populating a chart, is there any way of having some of the values plot in a different style line? i.e I have 24 values, half of which are extrapolated which I format in red. They are all the same series but I want the red formatted, extrapolated values to chart as a dotted line. Is there any quick way of doing this without breaking it into more than one series? Any help greatly appreciated. Thanks, Jason Tag: Data Menu Tag: 113976
  - 2
    - reshape array of numbers Hello all, I would like to know if there is an easy(?) way to change the shape of an array of numbers. I have a data set consisting of 300 numbers. They are in an array of 12 rows and 25 columns. I would like to change the list to just one column of 300 numbers. Of course I could (and did) cut and paste until I had one column, but I was thinking that Excel should have an easy way to put all the numbers into one column. Occasionally, I receive data that comes in different sized arrays that should have been in one column. Is there an easy method to do this, without any major programming, etc. Thanks for your help Phil Tag: Data Menu Tag: 113975
  - 3
    - Query combined with formula Hi, I have made a query, that gets the parameter from my cell A11. There is always only one result, which is written i cell B11. Now i wish to copy that formula from A11 to the area A12:A3000, so it picks up the parameter from the row, where the formula is placed. The result should be, that a query request data based on A11 and spit it out in B11; another query uses A12 and spits out in B12 (...) Now I need it for now in 3000 rows, and that might expande drasticly over time, so how do I easy copy it down? Regular copy/paste only copy the formula or value of the cells, not the queries... Kind regards Jesper L=FCtzen Tag: Data Menu Tag: 113973
  - 4
    - I can consolidate data, but what about text items? Hi, I have got the hand of consolidating data from multiple sources, but when I consolidate non-numeric data - i.e text, it returns 0 by default for these columns. Is there anything I can do to consolidate and retain text items? Thank you! Tag: Data Menu Tag: 113969
  - 5
    - Creat a chart to show peak usage times for a resource I'm no genius at Excel (2002 or 2003)and I could really use some help on the best way to display some data I have. What it shows is the usage of a training drop-in room, with details of the arrival time and period spent / per user / per day. There seems far too much data to put into a chart in any way that I can think of, but I really would like to be able to illustrate the spread and volume of clients throughout each day. Please can anyone give me simplistic information on how best to creat this as a visual report so that we can easily summarise usage information? Any ideas would be appreciated, but I'm not up to creating code! Thanks, Gill Tag: Data Menu Tag: 113964
  - 6
    - a problem there is an A column has 1500 line and a B column has 1700 line and B column already has A column entries, there just 200 entries are different i want to find 200 diffrent entries. how can i find? Tag: Data Menu Tag: 113960
  - 7
    - Text into a VBA Text Box Hi, I'm trying to create a touch screen keyboard so I can input a postcode/ zipcode into a user form (the users wont have a real keyboard). I've got a basic one working where text typed goes into worksheet cells. But I could really do with it going into a VBA Text box within the user form in real time. Any help as always appreciated DonH Tag: Data Menu Tag: 113958
  - 8
    - Design issue; and finding and displaying row on other workbook 1. An academic course is divided into two sections: lecture and discussion. The lecture section contains all classes, but the discussion classes do not. The discussion section is further divided into three subsections, each containing multiple classes. The need is to coordinate student attendance and grades for all sections. Currently we have 1 lecture section workbook with all student names, IDs, and other info; and 3 discussion section workbooks with the same only for the relevant subsection. And then there is a consolidation workbook where lecture and discussion are put together and attendance and grades totaled horizontally by student. The teachers email in their workbooks weekly for the consolidation. (Ideally, we'd just all update a common database, but that isn't practical at this point.) Currently the consolidation workbook just composed of links to the teacher workbooks, and they were gotten by copying and pasting links from those. So at weekly consolidation time, all the teacher workbooks are loaded and then the consolidation. This seems to work well as long as no rows are out of place or deleted in any workbook, so care must be taken that no workbook has been sorted differently from the original linking. My question is, does this seem a reasonable design or not, or should something else be done, like copying rows per session or lookup etc. 2. We have another workbook of private student info by student ID, but it may or may not contain all the IDs on our attendance/grade workbooks. We'd like to be able to click on a link beside an ID in our attendance/grade workbook, or select a cell containing an ID number and pressing a button, and be taken immediately to the correct row in the student info file, to send an email or get the phone number. Attendance Workbook A ID Absences etc. A 1 444555 4 2 666777 6 Private Info Workbook B ID Email A 1 333554 joedoe@aoll.com 2 444555 janedoe@aoll.com 2 666777 (empty) So say we want to get from 444555 in A to 444555 in B quickly without having to manually do a Find or filter. Any easy way of doing that? Appreciate any help, thanks! Tag: Data Menu Tag: 113952
  - 9
    - How to add formulas together, hours & minutes function. Hi Group, Please could you give me an all singing, all dancing formula, or an explanation of how to put these together. A1 B1 23:00 07:30 =IF(B1<A1,B1+1,B1)-A1 This gives me the hours and minutes 8:30 in C1 (Format/Custom/HH:MM) One hour needs to be added so... =C1+01:00 gives me 09:30 in D1 (Format/Custom/HH:MM) Then I need it dislayed in decimal format =D1*24 which gives me 9.5 in E1(Format/General) It's not actually set out like that A1 B1 C1 D1, with the result I need in E1, but it makes it easier to explain. It's not urgent, as I already have the calculator working, I'd just like to use this function elsewhere :-) Next on the list is how to round up and down, for when the hours are not as straightforward, I round them to the nearest 1/4 of an hour, for example 23:10 07:45 = 9.666666667 (It should be 9.5 by using 23:15 07:45) 21:05 11:30 = 15.41666667 (It should be 15.5 by using 21:00 11:30) Can all of this be put in one formula? These functions will be used on a spreadsheet and the final values copied across to another spreadsheet, so it doesn't matter if extra columns need to be created or hidden. Can you help? Cheers Rob Tag: Data Menu Tag: 113946
  - 10
    - Null value syntax of export XML Dear all, I use excel 2003 to export a excel file into xml file. For those blank cells in the excel file, I input a ' so that excel can recognize it as a null value when exporting to xml. After exporting, the syntax of null value in the xml file will look liked <remark/> However, I expect the syntax of null value will look liked <remark></remark> Everytime I will use Notepad to modify the syntax. May I ask whether there is any method to modify the xml output syntax? Thanks. Regards, Michael Tag: Data Menu Tag: 113945
  - 11
    - Advanced Conditional Formatting and Changing Cell Colours I have three basic (for programmers) conditional formatting problems to solve in Excel for which I require some help. These are the last 3 conditions I need to complete for the worksheet so any help will be really appreciated. They are as follows: 1. If cell Y2 is > 0 and cell Z2 = 0 I need to change the colour of cell Z2 2. If cell Y2 or cell AA2 > 0 and cells V2, W2, or X2 = the value "Z" I need to change the colour of the cell containing "Z" (V2, W2 or X2). If changing the colour of only the single cell containing "Z" cannot be done, I guess changing the colour of the whole row would be okay. 3. If cell Y2 or cell AA2 > 0 and cell R2 <> 100 I need to change the colour of the row Thanks to the google group community in advance! Tag: Data Menu Tag: 113939
  - 12
    - Test Tag: Data Menu Tag: 113934
  - 13
    - expanding nested expressions Hi, Sorry, I'm an Access person, but I'm being paid to sort out someone's spreadsheet that looks like it has grown over the years. I have a very simple problem. I have a formula similar to the one below Column N = ((+AR15/+(1-T$12-AC$2)+BS15)) then AR = AD15+AQ15 AD = a constant (so stop) AQ = SUM(AF15:AP15) I'm trying to move toward something like: SomeColumn = Constant1 * Constant2/Constant3 Where Constant(n) is an "underived" cell. (Has no formula). etc etc. I can use Trace Dependencies to find all these, but is there an easy way to have Excel do the work for me? I am doing this so that I can basically re-create the calculated values in queries or expressions in Access. The vast majority of the functions are simple add, subtract, multiply, divide. About as complicated as this spreadsheet gets is using absolute cell references. It's just a nightmare trying to sort out all the dependencies because you almost have to start at one end of the dependency chain and work your way up. Then finally you have cells that are only based on other cells that contain fixed values, if that makes any sense. (Well, think of simplifying algebraic expressions. That's EXACTLY what I'm trying to do, but in this case, they're Excel formulas. Once I have them substituted down as far as they can go, I'll move them to Access. But there are like 70 columns of them. Just wondering if there were an *easy* way to do this. I'm fine with Excel VBA (well, I can understand VBA, but maybe not all of Excel's object model, if that helps) Thanks! Pieter Tag: Data Menu Tag: 113930
  - 14
    - Creating the occurrence frequency chart in Excel I had a need to put a series of numbers into buckets of size 5. I created the following formula, and "dragged it down" =A1 + IF(MOD(A1,5), 5-MOD(A1,5), 0) I am sure that there is a better way of doing this. Can someone suggest it? Also, I wanted to come up with an occurrence frequency chart for my data. For example if I have numbers 4, 9, 13, 13, 17, 19, 19, 19, then I would like the occurrence frequency chart to plot the following informatiuon in a 2D chart: 0-4: 1 5-9: 2 10-14:2 15-30: 3 Thanks, Song Tag: Data Menu Tag: 113929
  - 15
    - concatenating two fields in one cell, of which one is a date. I have two cells: Cell A2 Today's date is Cell A3 02/08/2007 I want cell A4 to state: Today's date is 02/08/2007 When I use the formula =CONCATENATE(A2," ",A3) I get the following: Today's date is 17669 How do I make this state [Today's date is 02/08/2007] in one field? Tag: Data Menu Tag: 113912
  - 16
    - Countif and Time fields I have a list of times. The column is formated for "Time 13:00" format and it is calculated from two other fields. The calculation to create this time is correct and the format is correct. I want to be able to count the # of times that a time is listed between two values (e.g., # >=00:00 and <=00:30, # >00:30 and <=01:00, # >01:00 and <=01:30, etc.). I have used the countif statement the way I have seen it listed here but it doesn't count the times correctly (I think the formula was =countif(I1:I7201,">=time(00,00,00) AND <=time(00,30,00)"). It just does not give the correct answer. Does anyone have an idea? Thanks Bob Tag: Data Menu Tag: 113910
  - 17
    - Add-ins in Excel 2007 I have a series of add-ins that apparently load on Excel 2007 but do not appear in the Ribbon. Example: all my COM add-ins. (eg asap utilities etc.) What's up? Tag: Data Menu Tag: 113905
  - 18
    - Conditional formatting again Okay I have had a google and a read and am no wiser as to how I did it or how to do it My problem is this put simply. I have a range of cells called "Name" When Tom appears in the range I want it to be red When Jim appears in the range I want it to be blue When Ron appears in the range I want it to be green When Sue appears in the range I want it to be pink I have more names but won't bore you with them all. Is there a limit to how many I can have? Once again, Many thanks. Andrew Tag: Data Menu Tag: 113895
  - 19
    - Static reference to worksheet in formula I have several Documents. For example one is called Budget 2007, Budget 2008. In each of these I have worksheets for each month, Jan, Feb, etc... I also have a worksheet called Year. In the Year I have cells that reference the month worksheets. For example I might have =Jan!$B23 My problem is that when I now copy the worksheet Year to the new file the cell reference is ='C:\[BUDGET 2007.xls]Jan'!$B$23 I can't edit the links because I do have some formulas which I want to reference previous years and otherwise will create lots of circular references. 2 questions I have. 1) Is there a formula in Excel which I can put into =Jan!$B23 so that when I copy the worksheet it doesn't insert the whole path since the Tab Jan does exist in the new file? 2) Is there a formula in Excel which I can put into =Jan!$B23 so that if I drag it accross to the cells to the right it will change the Jan to Feb, Mar, etc... similiar to changing B to C and D Thanks Sam Tag: Data Menu Tag: 113893
  - 20
    - Conditional formatting I have in the past used a way of formatting a cell due to certain conditions. I used more than the usually allowed 3 in Format > Conditional formatting helper. But I cannot for the life in me remember how I did it or where the sheet is now that I did it on ( It was a few years ago) Anyone with a refresher on how I might have done it ? Andrew Tag: Data Menu Tag: 113883
  - 21
    - Export highlighted data Is there a way to export a highlighted section of a worksheet to a text file? Or is there a way to only export parts of a spreadsheet that have data in them? For example, I have a spreadsheet with many hidden formulas from rows 1 - 100. Sometimes only rows 1-12 have data. If I save the spreadsheet as text, the empty rows (13-100) show up with a set of columns. For compliance reasons, I can not incorporate a macro. Any ideas? Thanks! Tag: Data Menu Tag: 113877
  - 22
    - Dynamic filtered lists Hello - Let's say I have 2 sheets ... sheet1 has 5 stores (A2:A11), with a number (0-5) in B2:B11. The value of B2:B11 is based on a formula and is (of course) dynamic. On sheet2, I'd like a list of ONLY those stores with a value greater than 0 and the value assigned to that store. So, an example: Store 1 = 0 Store 2 = 3 Store 3 = 1 Store 4 = 0 Store 5 = 5 Result on sheet2 would be: Store 2 3 Store 3 1 Store 5 5 How would I do something like this? I'm comfortable with VBA, so that's an option... TIA, Ray Tag: Data Menu Tag: 113876
  - 23
    - Need to merge multiple cell data into a new cell. Hi, I was wondering if someone could help me with an Excel function. What I would like to do is automatically merge some cell data: For example, i have a csv file with columns, infoline1, infoline2, infoline3 etc.. I would like to create a new column, totalinfo, and I would like it to be a collection of all the data found in the infoline1, infoline2 etc.. columns. In addition, everytime new column data is added in I would like the text <BR><BR> to be inserted. So effectivly each columns data is seperated by <BR><BR> Make sense? An example file is here: http://www.poweryard.com/example.csv Row 2 has the added data. Thanks for any pointers, zeebop Tag: Data Menu Tag: 113875
  - 24
    - max and min in an array hi, i have these two data columns 1 0.34564 2 0.4645 3 0.464 4 0.4786 5 0.47611 6 0.951 7 0.894 8 0.65785 9 0.654 I have two known numbers, 3 and 5, which relate to column 1. i want to find out what it the minimum between the corresponding numbers in column 2 between 3 and 5. note that 1 may not relate to A1 (or B1 C1 etc) so is not related to cell position, so if the cell position is needed, this needs to be found. e.g. the numbers in column two which correspond to between 3 and 5 in column 1 are: 0.4645 0.464 0.4786 0.47611 the minimum is 0.464. the maximum is 0.4786. how can i do this in excel, without knowing the numbers in the columns, i.e. they are on a different sheet, and you just want to find between 3 and 5 by entering 3 and 5 into a cell. Thanks, Phil Tag: Data Menu Tag: 113874
  - 25
    - customize buttons Hello, I am trying to figure out if I can have 2 custom buttons. 1 to turn off auto calc. 1 to turn on auto calc is this possible? I guess i could write custom vba code. but I am looking in the customize. and I cannot find them, already created. thanks Tag: Data Menu Tag: 113871
- Next
  - 1
    - inserting data into password protected excel file How do we insert data into an excel file that is password protected using ODBC connectivity. I am getting an error whenever I am tryin to insert date into the password protected excel files. Its saying cannot decrypt the file. How do I solve this issue?? Tag: Data Menu Tag: 113868
  - 2
    - Printing Entire Template on One page Please Please Please...I need your help. I have an excel document with 12 pages for each month in 2007. I need to print off all pages on to one page for a board meeting! I am a temp and I really want this job...please help me! Danielle Tag: Data Menu Tag: 113867
  - 3
    - Expand to fit. I have seen the formatting property 'shrink to fit'. Is there a way to make the cell expand to fit? For example I have a cell that concatenates multiple cells in my questionnaire. Many times the amount of words in the cell is too long for the length of the cell. I am using word wrap but I have to physically enlarge the cell until I can see the last line of words. My goal is to be able to print out all of the fields with all of the words in the cells even if they do not show on the screen. I want the width of the cell to remain the same, but only the number of lines printed to be determined by the actual contents of the cell. Tag: Data Menu Tag: 113866
  - 4
    - Testing for existence of a Range Hi, Bernie Deitrick gave this macro to me a few weeks ago for converting relative to absolute references. Sub ConvertFormulasToAbsoluteReferences() Dim mycell As Range For Each mycell In Selection.SpecialCells(xlCellTypeFormulas) mycell.Formula = Application.ConvertFormula(mycell.Formula, xlA1, xlA1, True) Next mycell End Sub It works great, with the proviso that one must select a range before activating it, or all cells in a sheet will be subject to the macro. Therefore, I'd like to add a test to the macro's front end. If no range is preselected I'd like only the active cell to be affected; if a range is preselected I'd like the entire range to be affected, as is the case now. Thanks for any help. Also hope to learn a bit more about ranges by this exercise. p. -- MS Office '03 Basic on Win XP Tag: Data Menu Tag: 113862
  - 5
    - Edit a worksheet command button Using 2003 - I have inherited a workbook that has various command buttons on the worksheets. The buttons are attached to a macro. I need to edit the buttons but I can't seem to get the button into the edit mode. How do I do that? Glen Tag: Data Menu Tag: 113855
  - 6
    - How can you set a column of cells to be hyperlinks? Hi, I'm trying to set a column of cell values to be hyperlinks so that when clicked on a web page will open showing the information for the paricular order selected from the spreadsheet. What I have done so far is to insert a column next to the column that has the data in it and use the HYPERLINK function with reference to the cell to the left. That works just fine but now I want to get rid of the column to the left and leave just the column with the hyperlnks in it. If I remove the column then the cell reference is bad because the cells have been removed. What can I do to get this to work? Tag: Data Menu Tag: 113849
  - 7
    - What to do when you have lost the passord to a VBA ? What to do when you have lost the passord to a VBA ? I need some help.... What can be done when you loose the password to your VBA?? Is everything lost or is there a backdoor??? Tag: Data Menu Tag: 113846
  - 8
    - sort in excel 2007 I use this (fOrderMany() ) for sort number in excel 2003 and it works well. Now i try in in excel 2007 and it doesen't work. Why? In worksheet there are some emply cell. Sort cell in groups of 7 by row C2 AK2, C3 AK3... C2 I2, J2 P2, Q2 W2, X2 AD2, AE2 AK2 Sub OrderMany() Dim Rng As Range Dim iLastRow As Long Dim i As Long, j As Long iLastRow = Cells(Rows.Count, "C").End(xlUp).Row For i = 2 To iLastRow For j = 3 To 31 Step 7 Range(Cells(i, j), Cells(i, j).Offset(0, 6)).Sort _ Key1:=Range(Cells(i, j), Cells(i, j).Offset(0, 6)), _ Order1:=xlAscending, _ Header:=xlGuess, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal Next j Next i End Sub thx Tag: Data Menu Tag: 113842
  - 9
    - Survey Form I've been asked to do a survey form for work. Users would be using a touch screen laptop to input data with at least one field, postcode/ zipcode requiring data entry from an onscreen keypad. Is this something that could be created using excel and a VBA form? Any advice and suggestions greatly appreciated. DonH Tag: Data Menu Tag: 113841
  - 10
    - Autofilter in Excel 2007 I have a sheet of financial data to which I have applied the autofilter. Clicking on the downarrow in each of the header fields produces a series of options one of which is the filter. This filter box then lists the discrete data for that particular field with an associated checkbox. However, the one field I do want to filter in this manner - "Payees" - displays a Text Filter box with only one choice: Select All. In Excel 2003 all the Payees would be appropriately listed for selection. Que pasa? I want to use this approach to extract records pertaining to selected payees. Tag: Data Menu Tag: 113839
  - 11
    - excel 400 problem When I click formaton the toolbar, then hide, an error box pops up with a x on it and says 400, I have tried everything and have now removed office XP from the computer. Before I reinstall it does anyone have any Idea what was going on here or any sugestions. This hapens even on a blank sheet. When I run excel 2000 on another computer I don't have this problem. Thanks Tag: Data Menu Tag: 113837
  - 12
    - Comparing Values In 2 Columns Hi, i have 2 list of students names both of which are not up to date with each other and because of this one list has more students than the other. I want to search for students names and see if there is a match, if a match is found i need to copy the email address and paste it into the cell by the other name in the other list. See Below My spreadsheet has columns titled, (A)Display Name, (B)Display Email, (C)Sims Name and (D)Sims Email. I basically want to write a script that takes each individual display name in column A and searches in column C for an identical match, if a match is found i then need it to look at column D and if an email address is present copy it into column B on the row that the display name is on? Is there a quick method? Thanks In Advance Jay Tag: Data Menu Tag: 113836
  - 13
    - any shortcut for double click the column width auto hi , is there any short cut keyboard available to get the column set to the text in it automatically , we have to it with mouse at edge of column double click . or any short cut for complete work sheet to set the column with the text in it. Thankyou Tag: Data Menu Tag: 113833
  - 14
    - MANY conditional formats (appear to) make Excel 2007 SLOWWWwww !!! I have developed some EXTENSIVE worksheets for the production of our financial statements (& various other purposes). I make extensive use of conditional formatting to highlight variations in data & 'aberrant' financial results. This was developed -- & runs just FINE -- in Excel 2003. The formatting of 'raw' financial data (output from our accounting system reports) in these worksheets is controlled by code -- which iterates row-by-row through the worksheet to set up all the formulas, references, conditional formatting, etc. All starts off FINE -- but, as the worksheet formatting continues, Excel 2007 slows to a CRAWL after (only) 25-50 rows. [The number of rows on these financial reports typically is 300-400 rows.] I have done (roughly) timed comparisons between Excel 2003 & 2007 on the SAME worksheets / data. I would say that 2007 appears to be probably 1/5th to 1/10th the speed of 2003. In fact 2007 (on worksheets with HEAVY use of conditional formats) is SO SLOWWWwww that it is nearly UNUSABLE. I have done various experiments / comparisons -- & to the best that I have been able to determine, it is the CONDITIONAL FORMATS that create the problem. The bulk of the cells with data on my worksheets have conditional formats (typically 3 levels deep) -- which means that a typical worksheet may have (upwards of) 50-60K conditional formats per worksheet (300-400 rows by ~50 principal formatted columns by ~3 conditional formats per data cell). Large numbers of conditional formats creates NO problem (or slowdown) in 2003 -- but is a DISASTER in 2007. So far, the other aspects of 2007 have been (pretty much) GREAT -- but the (apparent) conditional format issue is a GIGANTIC PROBLEM. Imo, conditional formats in general -- & the SUBSTANTIAL enhancements in conditional formats in 2007 -- are a VERY useful & powerful feature. It is a shame to have them 'spoiled' by LOUSY PERFORMANCE. Incidentally, workbook sizes for this app run 250-500MB in Excel 2003. In Excel 2007, the file size (on disk) is reduced to about 20% of 2004 files. However, when loaded in memory, it seems to take up (probably) 2-3x the space -- & takes a LONG time to load. The workbooks contain 100-150+ worksheets. I also have used (in 2007) pivottables -- some in excess of 50K (to over 100K) rows & MANY columns. These operate just fine (adjusting for their relative size). In fact, for these (& most other things) Excel 2007 is MUCH superior to 2003. So, here's hoping that MS takes (RAPID) NOTE of -- & FIXES -- this (VERY IRRITATING & PROBLEMMATIC) glitch in Excel 2007... [This has been cross-posted on (at least) one other item in this forum -- in the hopes that MS will TAKE NOTE & FIX the problem.] Tag: Data Menu Tag: 113830
  - 15
    - Automation and communication between Access and Excel I'm starting a new database for the geological department in our museum. It' s still in the pencil and paper, discussion and design stage. It will track both publications and actual specimens, so the main structure will be a many-to-many relationship. (One publication may talk about several rocks, one rock may be mentioned in several publications.) The linking table in the middle of the one-to-many-to-one will also have an offshoot, a set of Excel workbooks. One of the properties of the members of BOTH main relations is a set of analyses. Either, both or neither (publications and rocks) may have one or more analyses attached. Each analysis is essentially a table of numbers listing proportions of components of a particular specimen. I don't want to embed the workbooks in the database, for two reasons - database bloat and the desirability of being able to mess with the analyses directly from Excel, without starting the database. There are many different types, X-ray, photo-spectrometry, various chemical methods and others. Each combination of rock and/or publication will have at most one attendant workbook, whose name will be a combination of the unique identifiers of the publication and/or rock, and the individual analyses will be on separate worksheets in the workbook. So far so good (although comments on this are welcome as well). What I want to do from the database is automate some of Excel's operations. How might this best be accomplished? I know how to start and operate Excel from Access, I have done similar stunts automating Word from Access. But this time I have several asynchronous tasks I would like to accomplish. 1. When the database starts, I want to immediately start a hidden instance of Excel, and have Excel cruise through the subfolder below the database, which will contain all the workbooks with the analysis worksheets. Excel's task will be to read and store the name of every worksheet in every workbook. When this is done, the list of names will be handed to Access, so that as the user wanders about in the database, he will immediately know whether a particular publication or rock has an analysis, and if so, what all kinds, without the necessity of Access looking for a workbook and examining the worksheet names each time a record becomes current. (The response time would be unacceptable.) But neither do I want to force the user to wait while Excel does this. The data waiting to loaded into this database is already fairly large (cca. 100,000 members) with the possibility to grow considerably. What is the best method for Access to determine when Excel is done? It is of course possible to write a file and have Access constantly look for this file, but that seems rather crude. I would have to put an timer on every form that the user might have open and have each form repeatedly interrogate the disk drive until the file is found, then read in the data and cancel the timer event on each form. It seems there should be some way for the Excel application to notify Access directly and have Access respond, whether the user is doing something right then or not. 2. When the user sees that there is an analysis that he might like to examine in detail, a button on the Access form (labeled "Show analysis") will send a message to Excel that a particular workbook should be opened to a particular worksheet and displayed to the user. I need to be able to deal with two possibilities, one is an idling Excel process, possibly the same one that read in all the names upon start-up, and the other is no Excel running. There may have been a previous use of Excel, which the user closed by completely closing Excel, or they may have simply Alt-Tabbed back to Access and the Excel process is still there. I know how to deal with both those possibilities, but again, I need to communicate between the two processes. This time, I need Access to tell the Excel process something. If I was starting a new process each time, there are command line parameters that may be used, but how do I pass information to an already running process? Pete ---------------------------------------------------------- (This e-mail address is fake, to keep spammers and their auto-harvesters out of my hair. If you want to get in touch personally, I am 'pdanes' and I use yahoo mail. But please use the newsgroups whenever possible, so that all may benefit from the exchange of ideas). Tag: Data Menu Tag: 113826
  - 16
    - References to outside sheets Hello there ! and thanks to everybody providing help! I have spreadsheets in folders call November06, December06, January07... Files names structure is always the same 1-11-06, 2-11-06 ... 12-11-06 and so on I want to do list on a single brand new sheet the value of cell F14 on every daily sheet Obviously I can open every file and refer the new sheet the daily sheets and the link structure would sounds as '[1-11-06] Sheet1'!$F $14 but as the daily files names always have the same structure, I may be able to enter "1-11-06" in lets say Brand New Sheet Cell A1 and create an expression in cell B1 to get reference to this sheet, can I ? Many many thanks for this! Kind regards Daniel Tag: Data Menu Tag: 113825
  - 17
    - Combining Columns in Excel to Create Date I have a spreadsheet with three columns showing month born, day born and year born. I want to combine these 3 columns to create a date in a single column, like 02/14/1942 so that I can accurately calculate a persons age as of the current day. I would also like to then calculate that persons zodiac sign. For example, if I had a birthdate in three columns showing for January 1st, 1911 (or 01/01/1911) when I try combining the columns using =A1&B1&C1, I end up with a result like 111911. Thanks. Tag: Data Menu Tag: 113818
  - 18
    - Total or Sum Cell Location Always Just Below Latest Row Entry. Possible? I would like to have the location of the cell bearing the total of a column of figures vary with the number of figures in that total. EXAMPLE: I place an entry in row 1. The total appears just below it in row 2. I add an entry in row 2. The total of row 1 and 2 then moves to the cell just below the row 2 entry (row 3), and so on. If the total has a label to its left, I would like that label to move as the total moves. I'm sure this is possible, I just can't figure a way to do it! Help much appreciated! Thanks in advance! Frank Tag: Data Menu Tag: 113813
  - 19
    - Importing a web page to excell Is there any way, other than copy & paste, to copy a web page into excell using a program? Thank yoy, Gordon Tag: Data Menu Tag: 113809
  - 20
    - Importing a web page to excell Is there any way, other than copy & paste, to copy a web page into excell using a program? Thank yoy, Gordon Tag: Data Menu Tag: 113808
  - 21
    - Excel 2007 - only administrators can open files read-only When files are already open by another user, we have found that only users with Administrator permissions on their computers are able to open files read-only. If the user only has basic user permissions they receive a message stating the file could not be found. This was not a problem with Office 2000 (which we recently upgraded from). Any ideas? Thanks, Chris Tag: Data Menu Tag: 113806
  - 22
    - Max characters in a cell work-around Thanks - you helped me today! I've been looking for a way around the 1024 display limit and this is much better then the <alt><enter> suggestion I've found elsewhere. EggHeadCafe.com - .NET Developer Portal of Choice http://www.eggheadcafe.com Tag: Data Menu Tag: 113802
  - 23
    - Remove unusual text/Keep text that has A-Z I download a file that contains text as you see below. I need to remove the ... The problem is that the "." is not really a "." its "..." as one. So if you replace or subsitute all "." it will not replace that. You can see what I mean if you backspace once it will delete all "...". Its as if its one character. Is there a way to remove this unusual character, like maybe just keep all letters? NICEâ?¦ NICFâ?¦ NICIâ?¦ NICJ.... NICKâ?¦ NICPâ?¦ Tag: Data Menu Tag: 113801
  - 24
    - formulas Can someone please help me with these 2 formulas that I need to enter into my Spreadsheet?. I am trying to set up a Profit calculator for my Ebay listings. This would be appreciated thanks so much Selling Price - US$25.01 - US$100.00 10.00% of the initial US$25.00 (US$2.50), plus 7.00% of the remaining closing value balance Selling Price - US$100.01 - US$1,000.00 10.00% of the initial US$25.00 (US$2.50), plus 7.00% of the initial US$25.01 - US$100.00 (US$5.25), plus 5.00% of the remaining closing value balance US$100.01 - US$1,000.00 Tag: Data Menu Tag: 113796
  - 25
    - Combine Cells and Retain Color Fomatting Here is the issue I am trying to figure out. *There are three cells, A2, A3, and A4 lets say. *A4 contains black text only. *A3 contains an arrow (special character) that, depending on the slope of a line, will change to an up or down arrow thanks to conditional formatting and will also change color based on whether or not the up/ down direction is a good/bad thing. *I want to combine the contents of A3 and A4 in the same cell, namely A2 and keep the color formatting of the arrow while the rest of the text remains black. *I am doing this so I can reference A2 for a chart label. Any suggestions? Thanks, Thomas Tag: Data Menu Tag: 113795

Trying to creat a Web Query. The book says to choose Data, Import
External Data. I can not find the Data menu. Can you help me?
Excell 2002, VBA 6.2

Gordon

Top

Re: Data Menu by Don

Don
Fri Feb 09 08:27:45 CST 2007

Top line between tools and window. Great start.

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Gordon" <gwelch1938@yahoo.com> wrote in message
news:1171025326.647737.6040@j27g2000cwj.googlegroups.com...
> Trying to creat a Web Query. The book says to choose Data, Import
> External Data. I can not find the Data menu. Can you help me?
> Excell 2002, VBA 6.2
>
> Gordon
>

Top

Re: Data Menu by Earl

Earl
Fri Feb 09 21:02:26 CST 2007

Gordon,

You should have File Edit View Insert Format Tools Data Window Help on your menu. If Data
(or any) is missing, do this:

View - Toolbars - Customize - Toolbars tab.
Select Worksheet Menu Bar (not the checkmark -- it should already be there. Click it to
make it turn blue)
Click Reset.

--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"Gordon" <gwelch1938@yahoo.com> wrote in message
news:1171025326.647737.6040@j27g2000cwj.googlegroups.com...
> Trying to creat a Web Query. The book says to choose Data, Import
> External Data. I can not find the Data menu. Can you help me?
> Excell 2002, VBA 6.2
>
> Gordon
>

Top

Re: Data Menu by sybmathics

sybmathics
Tue Feb 20 13:30:58 CST 2007

maybe you are on a chart cheet, or have chart selected.

either go to to a datasheet, or click on a cell.

this changes the menu bar.

hth

greets,

sybolt

Top