file lock with excel when file, close workbook instead of exiting Excel completly

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
    - Shared Templates in 2007 How do I enable a network folder for Shared Templates in 2007? I don't want my users to have to navigate out to the network and doubleclick on the template, I want shared templates available when doing a New option in Excel 2007. In 2000 and 2003 it was labeled Alternate Start location, I cannot for the life of me find that as a choice in 2007. Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116715
  - 2
    - Worksheets Locked Hi, Have an Excel Spreadsheet that I am unable to add or delete any worksheets from. I can't for the life of my work out why. Can anyone help? Cheers, Joe Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116710
  - 3
    - chart links I have an excel workbook that consists of several sheets and 4 of these sheets are charts, i am wondering if its possible to see where the source cells are that drive the chart columns. I know they are not external they are all within the workbook. The chart button at top only lists greyed out buttons when in the chart sheets. Any help appreciated.. Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116709
  - 4
    - pivot table formatting Using 2003 (11.5612.8107) This is my first real work with a pivot table. In 10 minutes I was able to create a beautiful table with exactly the desired look. However if there is any slight change, I have to rebuild things from scratch or at least reformat it. For example in the left most (Row field) i use Request Type. If I change the Request Types being displayed, the format of the table comes undone. I have read an old post and selected Pivot Table, Table options, Preserve Formatting. I've also clicked on Select, Enable selection. Yet if I add or remove one of the items to display and all the column widths need resetting. Is this normal PT behavior? It makes it unviable if I can't lock it down. Thanks much, Russ Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116707
  - 5
    - Consolidating Information Hi there hope you can help. Sorry long explanation in the hope it helps. One of the projects I've been working in is a NON TABULAR form which is split into about 6 worksheets each covering a different part of the information required. Cells normally have an explanatory heading on the left with an input cell to the right, but this is not always the case. The form has been designed to look nice :-) and straightforward for the person doing the data input. Each workbook provides information on a single location. Its now been decided that this would be good to link into an access database for testing prior to moving into a SQL database :-( So all information on premises are linked and searchable. So far we probably have around 150 workbooks each covering a seperate location (premise). This is far too much information to dump and start again. I need to be able (I think), gather the information from the various cell addresses over the 6 or so worksheets and compile a new worksheet where this could be setout into a tabular table format ready for exporting. Question 1 am I correct in thinking that would be a good place to start? Question 2 I need to be able to replicate this tabular worksheet in the rest of the workbooks! is a formula such as =INDIRECT("Sheet1! E15") etc the best way of getting around cells keeping the reference to the originating workbook. And finally Question 3 if this is the right way to go, is there then a peice of VBA which will add each tab table to a master workbook with info being added to next blank line. Sorry if thats too much in one go. Thanks in anticipation Don H Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116703
  - 6
    - Changing Color in Cells Is there an easy way to make a cell content change to a certain color? For example if you input say $2.00 into a cell can you have it automatically become green? Can you do the same thing with the background color? Thanks Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116700
  - 7
    - List in different formats A short intro: I work for a company that sells dvds and cds. Every week we make a saleslist based on sale and expected sale of new titles. The problem is that I have to make the same list in different formats, based on what it's to be used for. The articles on every list is the same, but the articleinfo varies. I have to make the following formats: csv, doc, excel, html, ppt. The info are mainly text, but there is also a collumn with pictures. So to the question. Is it possible to make one list in excel, and then generate the rest of them from there? Today we use a lot of time every week to maintain these list. Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116697
  - 8
    - from offline excel to online access problem Any thoughts on how to solve this are appreciated! I am the scheduler for a baseball league. Our schedule for games is automatically generated by some programing in our online web space, but it needs to be hand adjusted to correct inequities. The data with the schedule is in an online access database. The data can be downloaded from the access database to offilne, vut only as an excel spreadsheet. I can make the adjustments to the sheet, but I've been told that I can't reupload the corrected info to the access database and must, once I have the changes worked out, re-enter to changes on line. Does anyone have anyinfo on taking offline excel and flipping the data into access on line? TIA Phil Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116692
  - 9
    - Worksheet management I'm struggling with worksheet and workbook management. I'm hopeful that someobody has a solution that I can't seem to come up with. I've searched and can't find any mention of this particular issue. I have a workbook with 10 worksheets in it. I have 2 copies of this workbook (one for each of my two clients ;-)). Worksheets 2 through 10 are identical in both workbooks. Worksheet 1 has the "data" for the individual client. The formulas in worksheets 2 through 10 reference the data on worksheet 1, as well as varioious other cells on other worksheets. That is, some of the worksheets will not only refer to worksheet 1, but, for example, worksheet 5 might also pull some information from worksheet 2. So far, so good. But let's say that I make a whole bunch of updates to worksheet 2 (one of the worksheets that does "work", as opposed to worksheet 1 which is the "data input" worksheet) in the first of my two workbooks. I now want to make those same updates to worksheet 2 of my second workbook. Is there a "most efficient" way to do this? I have tried copying worksheet 2 from workbook 1 into workbook 2 and then deleting the old worksheet 2 from workbook2 and then renaming "worksheet 2 (2)" back to "worksheet 2". It doesn't work because it leaves all of the formulas in worksheets 3 through 10 in tatters, to the extent that those workbooks refer to "worksheet 2" as it is trying to refer to a worksheet that was deleted, thereby giving me heaps of "REF" errors. The same kind of problem takes place if I reverse my solution and attempt to make a new copy of workbook 1 (which would then be named workbook 3) and then copy the data worksheet from workbook 2 into workbook 3, deleting the old data worksheet and renaming the newly copied in data worksheet. This is actually worse, because every single one of the "work" worksheets gets platered with REF errors. I hope this explains my problem. Thanks for your time. Jim Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116684
  - 10
    - launch excel attachment from outlook - does not work problem: double clicking on excel attachments in outlook email results in an error message "the system cannot find the file specified" excel appears to start (new) - but file never opens file can be saved and subsequently opened from excel 2007. file cannot be launched by double clicking on it. thoughts? Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116678
  - 11
    - "Print This Page" Macro I had a spreadsheet template with columns as follows (and rows of about 110 items): Quantity Item Cost per Unit Hours per Unit Ext. Cost Ext. Hours Ground Mat/Grid 2.00 0.00 Ground Rod/Point 0.30 0.00 The Ext. Cost and Ext. Hours columns have formulas that will multiply the quantity by either the cost per unit or hours per unit (or both). There are also rows at the end of the list of items that do NOT contain item names, cost per unit amounts or hours per unit amounts. They only contain the extended cost and extend hours formulas. These rows are for estimators to add unique items on the fly. Estimators will go down the quantity column entering quantity numbers and, if necessary, unique items with quantities at the end of the list. Once they've filled in all their quantities I've set up a "Filter Quantity" button that will filter the quantity column so that only nonblanks show. >From here I need a "Print this Page" button with a macro that will print the spreadsheet down to the last row that has a quantity/item entered. I've tried several times to record a macro with varying results. Previously my macro would ignore (not print) any unique items that were added in rows below the standard list. My re-recorded macro now seems to be printing ALL the rows below the standard items even though they are technically blank - i.e. they have no quantity, item name, cost or hours filled in but they do have the cost ext. and hours ext. formulas. Help! It seems like it should be so simple. Thanks, Tina Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116676
  - 12
    - trouble with row height auto sizing When selecting a row or rows to auto resize, there are some cases when a row does not correctly resize. It may show just the top line in a cell or resize too tall. Any ideas about what may be causing this? Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116675
  - 13
    - check boxes + autofilter - is it possible? hello, I'd like to use Excel in automation mode to create a worksheet in which consecutive columns of a row contain 1) a checkbox and 2) a label. I am a beginner in this subject and all I have done so far is only filling "a label" column using a code similar to: oE = new ActiveXObject('Excel.Application'); oW = oE.Workbooks.Add(); oW.Activate(); for(i=1;i<n;i++) { .. oW.Worksheets(1).Cells(i,2).value = Label(i); .. } I have two questions: 1) Is it possible "to link" a check box with appropriate "label" cell within the same row, in a way which allows to use autofilter on "label" column, and which make "linked" check box to disappear as label is filtered out, and appear again when a label is not filtered? 2) Is it possible to do this with VBA or JScript, and how? I would greatly appreciate your help and suggestions. Any link/example/www resource/tutorial would be very helpful. thanks, DD. Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116671
  - 14
    - slide show of charts from excel I have an excel file which is a shared workbook and is updated many times a day by several users which has several charts included at a remote location on our wan, i would like to display these charts in a slide show format on a large screen at my local office and have them permanently run and update as linked cells are changed by users. any help much appreciated. Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116670
  - 15
    - Excel and C/C++ Hi all, For an Excel front-end GUI user interface, what's the best way to interface it with a back-end computational engine using C/C++? I guess there are numerous ways that can handle this -- I am looking for a method that is not too complicated(I am not very experienced in Excel/VBA programming), and most efficient ... Thanks a lot Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116661
  - 16
    - Matlab & Excel interface? Hi all, If the front GUI is in Excel, and the heavy-duty backend computational engine is in Matlab, what is the best way to handle this interfacing problem? Another headache problem is: for the users who only use Excel and who has no desire to purchase a Matlab licence and work with Matlab, what's the best way to package that Matlab computational engine along with the Excel? Thanks a lot! Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116660
  - 17
    - Macro required to copy -past data to first empty row Hi Expert, I am in need of such a macro or code by which I can copy & paste data from a particular row to first empty row of given row range. Kindly help. Regards, Vikky Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116657
  - 18
    - Help with Lists (tables), Filters, & Worksheets I'm hoping that I'm just missing something and somebody out there can help me. I'm using Excel 2007, but I'm trying to do something that would also be backwardly compatible. I've used the table (list) function to create a list of campers & associated data for my son's summer camp. What we want to do is have tabbed worksheets within the workbook that shows that data in different views. So, for instance, the first tab is the master list, but the second tab is filtered for the kids in the 1st session youngest class, the third tab is filtered for the kids in the 1st session middle class, etc. So the question is whether you can do 1 of 2 things: (1) create the filtered list in the new worksheet, or (2) keep an linked/updated copy of the master list on each new worksheet with different filters. Thus far, I've been unable to figure out either option. Yes, I know that Access would be better suited to doing this, but it's a little bit of overkill and I'm not sure that the school is licensed for access. Also, they've just been typing these lists into excel in the past, so I'm just trying to be more efficient with the tool they're already using. Thanks. -- Hugh Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116649
  - 19
    - Convert Cell Text to Matching Hyperlink????? We have an Excel spreadsheet that contains information to scanned images being populated into our engineering database. In the spreadsheet there is information as to the creation time and date, size, title and other related information. One of the columns is the name and location of the actual TIFF image file. This field is just text. What we would like to do is convert the cell from just text stating the location and filename to a hyperlink pointing to the specific file. For example, the cell contents state : G: \folder2007\subfolder0320\00001234.tif We would like to still have the text in the cell but add to the cell a hyperlink that actually points to the file at G: \folder2007\subfolder0320\00001234.tif so when we click on the cell it will launch our associated image viewing application and view the image. We tried creating a macro to do this one call at a time and copied the text in the cell to the clipboard and then pasting back in as a hyperlinlk. Somehow the cell address gets tagging into this and when you run the macro it always jumps back to the original cell the macro was written at. Any way to automate this process or any shortcuts to converting these cells to matching hyperlinks???? Any and all help is greatly appreciated, Scott Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116645
  - 20
    - Cell Referencing In Header Hi, I've been trying to use a cell reference in the header of my excel worksheets. The cell, B11, is in Worksheet1. I have the header updated when the worksheet is opened. The code looks like: Private Sub Worksheet_Activate() Worksheets("Worksheet1").PageSetup.LeftHeader = Range("Worksheet1'!B11").Value End Sub It works well for Worksheet1 But when I try to reference the same cell for my other worksheets with code looking like: Private Sub Worksheet_Activate() Worksheets("Worksheet2").PageSetup.LeftHeader = Range("Worksheet1'!B11").Value End Sub I get a runtime error '1004': Method 'Range' of object '_Worksheet' failed Does anyone know how to correct this? Also, is there some way I can make the cell reference italicized in the header? Thank you! Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116644
  - 21
    - INVENTORY CYCLE COUNT IN EXCEL This is a multi-part message in MIME format. ------=_NextPart_000_000A_01C76BA5.E704DD20 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello to all! I want to start an inventory cycle count program in my company. How can = Excel be used to do random selection? The only data I currently have is = a listing with the following headings: 1) Part Code 2) Part Description 3) Location Code 4) Quantity on hand 5) Unit Cost 6) Total Cost 7) Cateogry The accounting software does not provide, currently, a usage report that = I use to apply ABC method of selection items.=20 Any suggestions from Excel using inventory gurus would appreciated. Thanks SJ ------=_NextPart_000_000A_01C76BA5.E704DD20 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=3DContent-Type content=3D"text/html; = charset=3Diso-8859-1"> <META content=3D"MSHTML 6.00.2900.3059" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY bgColor=3D#ffffff> <DIV><FONT face=3D"Arial Narrow" size=3D2>Hello to all!</FONT></DIV> <DIV><FONT face=3D"Arial Narrow" size=3D2></FONT> </DIV> <DIV><FONT face=3D"Arial Narrow" size=3D2>I want to start an inventory = cycle count=20 program in my company. How can Excel be used to do random selection? The = only=20 data I currently have is a listing with the following = headings:</FONT></DIV> <DIV><FONT face=3D"Arial Narrow" size=3D2></FONT> </DIV> <DIV><FONT face=3D"Arial Narrow" size=3D2>1) Part Code</FONT></DIV> <DIV><FONT face=3D"Arial Narrow" size=3D2>2) Part = Description</FONT></DIV> <DIV><FONT face=3D"Arial Narrow" size=3D2>3) Location Code</FONT></DIV> <DIV><FONT face=3D"Arial Narrow" size=3D2>4) Quantity on = hand</FONT></DIV> <DIV><FONT face=3D"Arial Narrow" size=3D2>5) Unit Cost</FONT></DIV> <DIV><FONT face=3D"Arial Narrow" size=3D2>6) Total Cost</FONT></DIV> <DIV><FONT face=3D"Arial Narrow" size=3D2>7) Cateogry</FONT></DIV> <DIV><FONT face=3D"Arial Narrow" size=3D2></FONT> </DIV> <DIV><FONT face=3D"Arial Narrow" size=3D2>The accounting software does = not provide,=20 currently, a usage report that I use to apply ABC method of selection = items.=20 </FONT></DIV> <DIV><FONT face=3D"Arial Narrow" size=3D2></FONT> </DIV> <DIV><FONT face=3D"Arial Narrow" size=3D2>Any suggestions from Excel = using inventory=20 gurus would appreciated.</FONT></DIV> <DIV><FONT face=3D"Arial Narrow" size=3D2></FONT> </DIV> <DIV><FONT face=3D"Arial Narrow" size=3D2>Thanks</FONT></DIV> <DIV><FONT face=3D"Arial Narrow" = size=3D2>SJ</FONT> </DIV></BODY></HTML> ------=_NextPart_000_000A_01C76BA5.E704DD20-- Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116643
  - 22
    - Ranking Percent of Increase I'm trying to rank a percent of increase. In my worksheet (Worksheet 1), I have This Year's sales information in column A and Last Year's sales information in column B. I want to find the percent of increase of A over B and then rank the resulting percent against a row of percentages (Worksheet 2, Row 1, Columns A:AC) -- Any help? Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116641
  - 23
    - Should be Simple... Hi, I have the line of code If Range("C7").Value = True Then Basically I want the instruction after this code to happen if there is anything displayed in the cell C7. Now the above code doesn't work but I know that it is almost correct because if I type "yes" into cell C7 and then change the code to If Range("C7").Value = "yes" Then it works, but I can't use this as the cell will not always contain the word "yes" What is the correct syntax for saying "if the cell contains data"? Thank You... Craig... Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116629
  - 24
    - COUNTIF: seems easy but.... Hello, how can I use the countif function to count the number of star (*) in a range ? If I do =COUNTIF(B1:B8;"=*"), I have the number of cells because * is understood as a wild card... Thanks in advance Laurent Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116624
  - 25
    - Scan and remove column data query Hi, I have a spreadsheet (http://iedmont.blogspot.com/) and am trying to find a solution to scan column A for duplicates and if found, remove the cell contents of the active cell, column B, column, C and column D. So as an example in the example sheet, it would scan column A for data1, leave cell contents of A2, B2, C2, D2, E2 and F2 in place and remove cell contents A3 to D5. Then keep all cell contents in row 6 (only one entry found for data2). Then keep all row 7 cell contents and remove A8 to D13, leave row 14 and finally leave row 15 and remove A16 to D16. All data in columns E and F to remain. Can anyone help please? Many thanks. IE. Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116622
- Next
  - 1
    - Hyperlinks to '[workbook]Sheet!'Range(Cell) Dear fellows I'm struggling with my hyperlinks. Do anyone know the hyperlink-code/reference to a specified sheet and cellreference in another workbook? I want my hyperlink to open this other workbook somwhere in my filestructure, and activate a specified sheet/cell in this workbook. Is it possible, and how? I'll appreciate your kindly help Regards Nils Morten Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116620
  - 2
    - push rather than pull cell data. Is it possible with excel to push data from cells to another sheet in same excel file rather than pull it from the cells you want populated each time cell data is changed. Thanks for any help with this. Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116618
  - 3
    - A way to identify and delete duplicate entries? A couple times I week I run a report on accounts (account numbers are either 7 or 8 digits long) looking for 3 different issues, import the results into Excel, and distribute the results to be worked on. Since the same account may have been identified by two or more of the search parameters, the final report may contain anywhere from 5% to 15% duplicate (sometimes triplicate) entries. Since it's a bit of a time-waste to even take a look at an account that may already be in the process of being resolved, is there an easy way to identify and delete duplicate entries? Even just identification would be great, as I could manually delete. Thanks for your help. -- Alan Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116612
  - 4
    - Simple formula help please... This should be simple...but it's beyond my knowledge. I have column A (20 Drivers) Column B (position in race 1-20) I want column C to show points won based on the cell contents in adjacent cells in column B. Where first place (cell entry=1) gives 10 points, second (2) gives 8, third gives 6 down to 1pt for eigth place. 9th and below should give zero points. What formula should I be using in column C? Thanks for any help. Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116601
  - 5
    - Excel 07 + iSeries Excel Add-In crashing I am test bedding Vista and Office '07 with our legacy programs. As our main databases reside on an AS/400, we extract considerable data for reporting by way of the iSeries Access for Windows Excel Add-in. This has always worked fine in previous versions of Office. However, in Excel '07, when attempting to run the connection, the program crashes. The add-in works properly up until the point when the data is to be virtually transferred to the Excel sheet. (You can see the libraries and files, make field selections, and play with the data options etc.). Running the transfer outside of Excel works fine. Office diagnostics can provide no info or help. Program info as follows: Microsoft Office Excel 2007 (12.0.4518.1014) MSO (12.0.4518.1014) IBM iSeries Access for Windows V5R4M0 SI25949 Error Event as follows: Event 1000 - Application Error: Faulting application EXCEL.EXE, version 12.0.4518.1014, time stamp 0x45428263, faulting module EXCEL.EXE, version 12.0.4518.1014, time stamp 0x45428263, exception code 0xc0000005, fault offset 0x00a14457, process id 0x107c, application start time 0x01c7625a54ed8fd2. Anyone with any ideas? Thanks in advance for your time. Ron McIntyre Montreal, Canada (Cross posted from microsoft.public.office.misc ) Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116592
  - 6
    - Create a list and use drop down in other worksheets..possible? Hi, I've search on how to do this task and found things similar but nothing exact. What I would like to do is create a 'list' of entries in one worksheet and then in another using a dropdown box select any entry from the 'list'. For example. Worksheet 1 A1 A2 A3 A4 ....etc Orange 100 0.5 17 Apple 88 7 23 Worksheet 2 I would like to be able choose from a drop down (combo box?) Orange and have the other cells (A2,A3,A4) follow suit in their respectable columns. My goal is to input all the food data in one worksheet and be able to select what I eat on a daily basis from the list in another worksheet, calculating calories/fat,etc. This way I can enter info once and just select from it instead of C&Ping or re-entering the same information all the time. TIA Jeff Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116581
  - 7
    - Protecting data validation Drop DownColumn I made a column which displays only dropdown lists made from datavalidation. When i copy and paste cells over it the dropdowns are no longer available. Is there a way to protect the column ? thx Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116577
  - 8
    - looping through columns and rows Hello, Can someone show me the code which would allow me to search through a number of rows and columns, to identify a criteria. Each time the criteria is found, 1 is added to a variable. When the variable reaches a set number, the search moves to a different column. This would allow me to allocate people to jobs according to their first, second, third choice etc, without resourcing too many people to a job. can you help? I have very little experience, but am usually able to see what is happening in the code and expend on it to take care of more elements. kind regards, Matt Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116575
  - 9
    - weird behavior when checking check boxes Hello ! I have a really weird problem. I have a excel file with about 20 sheets. on one, I have check boxes. about 10 when I check one box, the others grow in size. when I click on one that is bigger, it becomes smaller and the others become bigger. Have you ever seen that behavior ? it is the text size and the small box that grows or become small thanks for your help. tiah. PS : Excel 2003. Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116571
  - 10
    - Leading Zeros are lost when importing spreadsheet data I am importing data from an Excel spreadsheet into a .Net DataTable using the following code: // conn string Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties="Excel 8.0" OleDbConnection conn = new OleDbConnection(String.Format(this.ExcelConnString, ofd.FileName)); OleDbCommand cmd = new OleDbCommand(this.SpreadsheetSelect, conn); OleDbDataAdapter da = new OleDbDataAdapter(cmd); DataTable dtAHMOrders = new DataTable(); da.Fill(dtAHMOrders); The data comes across fine except for zip codes with leading zeros in which case the leading zeros are lost. I have tried every exampe I've found with the following results: When formatting the column as Special>ZipCode the data displays fine in the spreadsheet but the leading zeros are stripped. When prepending the zip code with an apostrophe the data displays fine in the spreadsheet but the leading zeros are stripped. What is completely frustrating me is that I have a different spreadsheet which also has leading zeros on zip codes and they import perfectly!!! When inspecting the Cell Format of this spreadsheet the cells are formatted as simply General. When looking at all of the cells show the green wedge in the upper left corner of the cell and the error says Storing Number as Text. Uppon closer inspection of the data I find no apostrophes, or spaces, or carrats, or ANYTHING! If I open a blank spreadsheet and enter 00401 into a cell, which is formatted as General by default, the leading zeros are lost as soon as I leave the cell. Can ANYONE shed some light on this? Thank you. Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116563
  - 11
    - Subtotal Average excluding zero I found how to do an average excluding values that are zero For example: =AVERAGE(IF(A1:A10=0,"",A1:A10)) but I'm not sure how to do this when I need to use subtotal because the sheet has the autofilter. I use =subtotal(9,A1:A10) to get a subtotal that changes when the filter changes. And I know there is an average =subtotal(1, A1:A10), but how do I do a subtotal average excluding zeros? I'm using excel 2007 Thanks, Mark Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116559
  - 12
    - How do I Lookup and SUM multiple "finds?" How do I Lookup values from another sheet, knowing that there will be multiple occurances of those values, and sum the offset cells? For example: Lookup value is: BAKER Table is: SMITH BAKER JONES BROWN BAKER HODGES 4 5 6 3 8 10 7 5 9 56 3 1 I want a forumla that uses the Lookup "Baker" and SUMS the second row of 5+3 to yield 8? Can I do this w/out using VBA? (He asked hopefully...) Thanks for any insight. Russ Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116557
  - 13
    - Importing text from an Explorer window Howdy all. OS: XP, Excel 2002 SP3 I have my music stored in the My Music directory. I have Artist, Album Title, etc. attributes turned on. What I'd like to do is to take all of this information and input it into an Excel spreadsheet. When I "Select All" from the window, and "Copy", it wants to copy the actual songs, and not the text. Also, the "Paste" option in Excel is grayed out anyway. Is there a way that I can treat the info in a Browser window as just text, and then copy that info into a spreadsheet? TIA. -- Don Cox Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116555
  - 14
    - auto update of conditional macro Hi again, I have just noticed that when I open the spreadsheet the cell with the =today() command automatically updates but the macro that is dependant on the answer does not update. I have tried to use refresh F9 but that doesn't work. I can go into the cell and hit enter and that then refreshes the colour assigned to the day. Is there a way of doing this when the spreadsheet opens up? Or is that not advisable? Andrew Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116546
  - 15
    - Reducing data points? I have 4000 data points of an electrical device. I'd like to use every 20th point to run math on. How do I tell Excel to skip 19 data points then get the 20th and skip another 19 and get the 20th etc.? Thanks, Mike Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116544
  - 16
    - conditional formatting I only just discovered the possibility of conditional formatting. Apparently, only three formatting rules can be set. What is the way around this limitation if I want to make it more complex. Some formula I guess? Thanks. Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116543
  - 17
    - Excel Sum with Filter & Copy specific Data Hello, I have two questions regarding Microsoft excel. If you could help me I'd really appreciate it! QUESTION #1 I have 2 worksheets. One has a list of ID numbers (unique - one of each) and a column for totals (which is blank as of now) WORKSHEET1 A B ID #: Total: 1 2 3 4 5 The other has a list of ID numbers (many listed multiple times), has a number entry which I need to be summed and a date (which I filter by to get a range) WORKSHEET2 A B C 1 345 Jan 1 2 327 Feb 10 1 745 March 22 1 237 Dec 6 4 845 Jun 3 3 23 Aug 7 2 2222 Jan 20 What I'm wanting is the entries for each ID to be summed together, but only for the data that is visible after filtering. Thus, the sumif function won't work as it also adds the filtered-out data. I can't get the subtotal function to work either because I only want specific data corresponding to the individual ID to be totaled. What I'm hoping the result to look like: WORKSHEET1 A B ID #: Total: 1 345+745+237 2 327+2222 3 23 4 845 5 0 [or blank] ...except I want just the final resulting total...not all the xx plus xx written out ;) QUESTION #2 Have 2 Worksheets One looks something like: WORKSHEET2 ID Name Pattern Colour Number 1 Bob Stripe red 547 2 Jill Square red 322 3 Joe Circle blue 0 4 Jack Plaid red 0 5 Paul Curly blue 77 WORKSHEET1 Right now Column A and Column B are blank for now. In column C there are numbers that will be linked via vlookup to each name (don't worry I can do this ;) ) When complete it should look like: Name Colour VlookupNumber Bob red 12 Jill red 3 Paul blue 4 TOTAL =sum(xx) I need to input just the Name and Colour of each person into the second chart for those people who have a number greater than 0. I'd like the Name in column A and the Colour in column B for those entries. As someone else will be using this file I'm trying to make it as user-friendly as possible. Is there a way to make it automatically update when one of the numbers are changed, or a new ID/Name is changed/added? Finally, after all the entries I want a TOTAL row, that just totals up the vlookup values on the row after the last Name. While this seems like an easy task (=sum(C2:CXX) ) the number of Names in column A will be changing over time, so it will have to move. Is there any easy way to do this? If anyone could help me with one or both questions, I'd greatly appreciate it. Thanks!! Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116530
  - 18
    - inserting a hard return with replace in a cell Is there a way to insert a hard return or line break using the replace function in a cell? Thanks brent Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116526
  - 19
    - default template in Excel 2007 I know that I can save a template in 2003 with the name "Book.xlt" in the XLStart folder and it will become the default workbook when starting Excel. This does not seem to work in 2007. Does anyone know how to do this? Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116524
  - 20
    - Compile error in hidden module: distMon Every time I open my excel I get "Compile error in hidden module :distMon" Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116523
  - 21
    - How can I replace a value?? in a column the following formula Sum(R6:R7) Sum (R7:R8) Sum (R8:R9) goes to Sum(R19:R20) I need to replace the first R value with R6 all the way down. Is there an easy way to do this, other than changing each formula individually? Any help apprecited Skip Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116513
  - 22
    - Lotto Formula My apologies. I posted this question yesterday and the response didn't work (or I don't have the knowledge to make it work). Regardless, I will post again and I'm hoping someone can help me out. I am setting up a Lotto chart for work. The game we're playing is Keno and there's a daily draw. The initial investment is $250. Column A: Will have the daily investment. Column B: Will have the winnings. Column C: Will have the losses. Column D: Will have the actual amount that's left from the initial investment and it will take into consideration the win or loss for that day and also the amount from the day before. For example; If the day before we have $230 in column D. Then the current day will take the total of column D (the day before), subtract the daily investment and either add or subtract the win or loss respectively. May someone help me with this formula? I would greatly appreciate it. Thanks to Jan who sent me =$A$2+SOM($B$2:B2)-SOM($C$2:C2) but once again, I couldn't get this to work. Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116511
  - 23
    - hyperlink or what? I know I can create a bookmark in Word and then insert a hyperlink in Excel such that when you click on it, it launches Word and navigates to that location. Not bad. I've also found that I can copy the relevant Word section and embed the object in Excel. Pretty cool. Closer. What I want is the "look" of the embedded object (looks like the Word document) but I want it to pull directly from the document - not from the static thing that was embdedded. I want it to display "real time" so that any changes to the document are reflected each time the worksheet is opened. Can that be done? thanks Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116505
  - 24
    - Line break function? I have the following function ="A"&"B"&"C" which gives: ABC Is there a way to insert line breaks so that instead of displaying ABC, it will display: A B C Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116504
  - 25
    - Issues with 3-Column Sort I have a user that is having a problem with a spreadsheet when doing a 3 column sort. We are using Excel 2003. Below is a description of the problem and what we have tried so far. If anyone has any ideas, I would great appreciate them. Thank you, JW Problem: Can not do one particular 3-column sort. Selecting to sort by column A, then by B, then by C does not work. Sorting by A itself works. Sorting by B, then by C does. I have it narrowed down to if we include column A in a multiple column sort, it fails. What we have tried: 1) Copied the cells from one sheet to another. The problem follows to the new sheet. 2) Manually keyed the data into a new sheet. The multiple column sort works. At this point, we felt that there must be a control character hidden in Column A somewhere? 3) Copied the 3 columns from Excel and pasted into Notepad to remove the formatting. Imported the text file into a new sheet. The multi- column sort failed. Has anybody seen this type of problem before? Very strange. Let me know if more information is needed or if anyone has any ideas. Thanks! Tag: file lock with excel when file, close workbook instead of exiting Excel completly Tag: 116499

Ok we have some excel files that get locked. Consist on a Win2k3 share.

Basically a user will open them up in Excel and then use File, Close but
keep excel 2003 open. So the file is closed!

Now another user tries to open the file but gets file is open by user open
as read only.

The first user must completly exit excel and then the file becomes
available.

I have done searches and can't find anything this specific. Please anyhelp
would be great.

Jamie

Top

RE: file lock with excel when file, close workbook instead of exiting by zcanELMALI

zcanELMALI
Fri Mar 23 06:39:00 CDT 2007

Hi,
May be cache problem, i don't now. But if you want , more than one user at
the same time;

Open Excel File, Tools -- Share Workbook -- check the "Allow changes by
more...." click OK; Save and Close Workbook.

Ã?zcan ELMALI

"Jamie" wrote:

> Ok we have some excel files that get locked. Consist on a Win2k3 share.
>
> Basically a user will open them up in Excel and then use File, Close but
> keep excel 2003 open. So the file is closed!
>
> Now another user tries to open the file but gets file is open by user open
> as read only.
>
> The first user must completly exit excel and then the file becomes
> available.
>
> I have done searches and can't find anything this specific. Please anyhelp
> would be great.
>
> Jamie
>
>
>

Top