Search Results

Search found 4901 results on 197 pages for 'excel macros'.

Page 7/197 | < Previous Page | 3 4 5 6 7 8 9 10 11 12 13 14  | Next Page >

• .net- open excel file, format the file and save

  - by Lock

  I have an ASP web service that uses the Crystal Reports API to download an Excel report. Now, there is a few things I do not like about the Excel report that Crystal generates: - The column widths are static (as in they are not adjusted for the content). - I can't format the header row to be bold - If I suppress a data column in the report, it comes out in the Excel spreadsheet as a blank column. I currently use PHP to open the excel file, autosize the columns, bold the heading and remove blank columns, although using the PHPExcel class for this doesn't work well when the spreadsheet is only a few 100kb in size. I am thinking if I move this activity into the .NET web service, the performance will be much better. Does anyone know an efficient way of opening a Excel file and performing the operations listed above?

  Read the article

• Summing of total with dynamics rows coming external datasource

  - by Gainster

  I am using Excel 2010 and retrieving data from SQL analysis service. When I refresh the data from Excel, the rows all refresh as they are bound to an external datasource. I am adding a separate column with a formula to sum the totals. With an increment or decrement of these rows, the alignment of custom columns goes out. How can I resolve this problem that summing of values become dynamic with adding and removal of rows?

  Read the article

• Excel Conditional Formatting Multiple Data Bars and Data Icons in one cell

  - by wbeard52

  I am using Excel 2007 on a windows machine. I am attempting to place one data bar and one data icon into a cell under the conditional formatting. The issue is that I don't really want to have data icons or data bars for cells that have dates in the future and I only want to have data icons for dates in the at least one month in the past. This is what I have: This is what I want: I am using the EOMONTH function to determine the last day of the month for the conditional formatting calculations. For the data bar the formula is =EOMONTH(Now(), 4) and =EOMONTH(Now(), -1). The data icons formulas are =EOMONTH(Now(), -1) and =EOMONTH(Now(), -2) Is there a way in Excel 2007 to get rid of the data icons for all the dates in the future and lose the data bars when the date has past. Thanks

  Read the article

• Tie stock quote value to cell in Excel 2011 Mac

  - by vedantchandra

  I've been working on a mock stock portfolio in Excel, and I've been looking for ways to automatically update the data, eg. stock price and P/E ratio. I have tried using a web query to MSN Money, but that just brings up the whole stock quote across multiple cells, I want data to be updated in individual cells only. The only web query solution I can think of is if someone hosted a website where each value in the stock quote was saved on a different HTML file. I could then WebQuery to that file for each cell requiring that value. However, no website offers this. So in essence, is there any tool on Excel 2011 Mac that will let me pull individual values from a stock quote and assign them to a single cell?

  Read the article

• Create a form as a worksheet in Excel that adds records to another worksheet

  - by Holden Fenner

  I am trying to create a form in Excel for vehicle requests. What I want to happen is to create one worksheet that is a recreation of the current paper form, but have a clickable button that will enter all the data for the vehicle request as a record on a second worksheet. Conversely, if you know the record number, I want the form to auto-flll the information from that record number. I have the second part figured out, that should be a simple HLOOKUP, but I don't know how to generate the clickable button. Will I need to use scripting for this, or is there an pre-built way of doing this? (As an aside, I know there are other and better ways of accomplishing database work, but the nature of my work makes Excel the best choice)

  Read the article

• Advanced (?) Excel sorting

  - by Preston Grayskull

  First of all, I'd like to admit that I don't really know anything about Excel, but I have tried to look up a solution to this in Excel books and Googling. Here's what I'm trying to do: I have a really long spreadsheet There are 7 columns total, but only two columns that I'm most interested in. Here's an example CSV that is much more simple than my actual dataset, but the search/sort is analogous: John, Apple Dave, Apple Dave, Orange Steve, Apple Steve, Orange Steve, Kiwi Bob, Apple Bob, Banana I'm interested in extracting the entire rows (all of the columns) that meet the following criteria: ["Apple"] OR ["Apple" and "Orange"] NOT ["Apple" and "Orange" and Anything Else] NOT ["Apple" and Anything that isn't Orange] So with the above CSV, I would get the entire rows for John and Dave, but not Steve and not Bob. I started doing this manually, and will likely finish by the time this question has an answer, but I would like to know this for future reference. Thanks!

  Read the article

• Excel chart won't update, based on calculated cells

  - by samJL

  I have an Excel document (2007) with a chart (Clustered Column) that gets its Data Series from cells containing calculated values The calculated values never change directly, but only as a result of other cells in the sheet changing When I change other cells in the sheet, the Data Series cells are recalculated, and show new values - but the Chart based on this Data Series refuses to update automatically I can get the Chart to update by saving/closing, or toggling one of the settings (such as reversing x/y axis and then putting it back), or by re-selecting the Data Series Every solution I have found online doesn't work - I have Calculation set to automatic - Ctrl+Alt+F9 updates everything fine, EXCEPT the chart - I have recreated the chart several times, and on different computers - I have tried VBA scripts like: Application.Calculate Application.CalculateFull Application.CalculateFullRebuild ActiveWorkbook.RefreshAll DoEvents None of these update or refresh the chart I do notice that if I type over my Data Series, actual numbers instead of calculations, it will update the chart - it's as if Excel doesn't want to recognize changes in the calculations Has anyone experienced this before or know what I might do to fix the problem? Thank you

  Read the article

• Excel chart won't update, based on calculated cells

  - by sam SJL

  I have an Excel document (2007) with a chart (Clustered Column) that gets its Data Series from cells containing calculated values The calculated values never change directly, but only as a result of other cells in the sheet changing When I change other cells in the sheet, the Data Series cells are recalculated, and show new values - but the Chart based on this Data Series refuses to update automatically I can get the Chart to update by saving/closing, or toggling one of the settings (such as reversing x/y axis and then putting it back), or by re-selecting the Data Series Every solution I have found online doesn't work - I have Calculation set to automatic - Ctrl+Alt+F9 updates everything fine, EXCEPT the chart - I have recreated the chart several times, and on different computers - I have tried VBA scripts like: Application.Calculate Application.CalculateFull Application.CalculateFullRebuild ActiveWorkbook.RefreshAll DoEvents None of these update or refresh the chart I do notice that if I type over my Data Series, actual numbers instead of calculations, it will update the chart - it's as if Excel doesn't want to recognize changes in the calculations Has anyone experienced this before or know what I might do to fix the problem? Thank you

  Read the article

• How do I copy hyperlink only (and not text) to another cell?

  - by OfficeLackey

  I have a spreadsheet where column A displays names. There are a few hundred names and each has a different hyperlink (which links to that person's web page). I want to transfer those hyperlinks across to a different column which has different text in and no hyperlinks. Not every cell in column A has a hyperlink. There are groups of cells merged together, so A2:A7 has one link, A8:A13 the next, A9:10 the next (i.e. number of cells merged is not uniform). e.g. where A2:A7 reads "Bob" and links to www.bob.com, I want I2:I7, which reads, "Smith," and does not link to anything, to link to www.bob.com. I want to do this repeatedly, copying links from A2:A579 into I2:I579. The information is copied from a table within a web page, and that is where the hyperlinks come from.

  Read the article

• Showing name of row instead of excel cell name

  - by Kare

  I am having extremely long formulas over an extremely big sheet. At the moment I am tracking the formulas with the Formula Auditing Tool. However, my idea would be to just replace for example in a formula like this: =IF(AND(ROUND($GX19-SUM(0)/$M$12;2)<=0;$AK$7=1);0;$M$12*$M$22/$K$62 My idea would be to replace the excel cell names with the table row names they are in. Like: =IF(AND(ROUND( "Income" -SUM(0)/ "Debt" ;2)<=0; "Percentage" =1);0; "Investment" * "Debt of house" / "Investment costs" Is there any way to achive sth. like that in excel? I appreciate your inputs!!!

  Read the article

• Set an Excel cell's color based on multiple other cells' colors

  - by Lord Torgamus

  I have an Excel 2007 spreadsheet for a list of products and a bunch of factors to rate each one on, and I'm using Conditional Formatting to set the color of the cells in the individual attribute columns. It looks something like this: I want to fill in the rating column for each item with a color, based on the color ratings of its individual attributes. Examples of ways to determine this: the color of the category in which the item scored worst the statistical mode of the category colors the average of the category ratings, where each color is assigned a numerical value How can I implement any or all of the above rules? (I'm really just asking for a quick overview of the relevant Excel feature; I don't need step-by-step instructions for each rule.)

  Read the article

• Creating a separate excel using Macro

  - by shayam

  Hi, I am having a excel with one column that has got information regarding tender. Each cell will have a value like Column: Nokia([Mode1.Number],OLD) Column: Motorola([Mode1.Number],OLD) Column: Motorola([Mode2.Number],NEW) Column: Motorola([Mode3.Number],OLD) Column: Samsung([Mode2.Number],NEW) I need to create 2 excel out of this. One should 've all the information of the OLD and the second excel should've all the information of NEW. So my output excel should contain First Excel Nokia([Model1.Number]) Motorola([Mode1.Number]) Motorola([Mode3.Number]) Second Excel Motorola([Mode2.Number]) Samsung([Mode2.Number]) Kindly help me.. Thanks in advance..

  Read the article

• How to make Excel strip ALL quotes from CSV text fields

  - by Klay

  When importing a CSV file into Excel, it only strips the double-quotes from the FIRST field on the line, but leaves them on all other fields. How can I force Excel to strip the quotes from ALL strings? For instance, I have a CSV file: "text1", "text2", "numeric1", "numeric 2" "abc", "def", 123, 456 "abc", "def", 123, 456 "abc", "def", 123, 456 "abc", "def", 123, 456 I import it into Excel using Data Import External Data Import Data. I specify that the fields are delimited by commas, and that the text delimiter is the double-quote character. Both the data preview and the actual Excel spreadsheet columns only strip the double-quotes from the first text field. All other text fields still have quotes around them. What's really strange is that Access is able to import this data correctly (i.e. strips quotes from every text field. Note that this is NOT a matter of internal commas or quotes or escape characters. This happens in Excel 2003 and Excel 2007.

  Read the article

• Is there a macro or a way to conditionally copy rows from one or more worksheet to another in Excel 2007

  - by marison

  I'm pulling a list of data from two or more excel file into one with some specific condition. For Eg: File1 Date Project ID Engineer 8/2/2008 XYZ T0908-5555 JS 9/4/2008 ABC T0908-6666 DF 9/5/2008 ZZZ T0908-7777 TS 9/4/2008 ABC T0908-1111 DF 9/5/2008 POR T0908-7777 MS 9/4/2008 ABC T0908-2222 DD File 2 Date Project ID Engineer 8/2/2008 ABC T1908-5555 JS 9/4/2008 XYZ T1908-6666 DF 9/5/2008 ABC T1908-7777 TS 9/4/2008 ZZZ T1908-1111 DF 9/5/2008 POR T1908-7777 MS 9/4/2008 ABC T1908-2222 DD I want Data from both file1 and file2 in a new excel with only those rows whose Project ID= "ABC". And the path of file1 and file2 will be changed on daily basis. Kindly help.....

  Read the article

• Are C++ templates just a kind of glorified macros?

  - by Gulshan

  From different comparisons among C++ templates and C#/Java generics like this one- http://stackoverflow.com/questions/31693/what-are-the-differences-between-generics-in-c-and-java-and-templates-in-c/31929#31929 I have a got a perception that, C++ templates are implemented by some kind of preprocessing, not compiling. In other words, they are just a kind of C macros. Is this right? Then I felt that, if C++ templates are implemented by preprocessing, dynamic linking(.dll) cannot be used. A quick google search also supports this. And lastly, how can we use macros in C to deliver C++ templates like functionalities? Another thing came in my mind about templates similar to some kind of preprocessor that, there can be integers passed as arguments to some templates. And it even supports kind of recursion. But the recursion is not in the assembly/machine code. So, am I wrong calling templates glorified macros? Unlike normal macros, it has some superior abilities. But isn't it a kind of preprocessing?

  Read the article

• Microsoft.Office.Interop.Excel.ListObject vs Microsoft.Office.Tools.Excel.ListObject

  - by Kavita A

  I need to access the Selected Event of all the listobjects in all the worksheets of my workbook but when I access worksheet.listobject, that object apparently belongs to Microsoft.Office.Interop.Excel.ListObject and so doesn't have any events where as the table list object belongs to Microsoft.Office.Tools.Excel.ListObject. And I read that Microsoft.Office.Tools.Excel.ListObject.InnerObject = Microsoft.Office.Interop.Excel.ListObject but i don't know how to use it. Pls Help Thanks, Kavita

  Read the article

• Copy Excel Formatting the Easy Way with Format Painter

  - by DigitalGeekery

  The Format Painter in Excel makes it easy to copy the formatting of a cell and apply it to another. With just a few clicks you can reproduce formatting such as fonts, alignment, text size, border, and background color. On any Excel worksheet, click on the cell with the formatting you’d like to copy.  You will see dashed lines around the selected cell. Then select the Home tab and click on the Format Painter.   You’ll see your cursor now includes a paintbrush graphic. Move to the cell where you’d like to apply the formatting and click on it. Your target cell will now have the new formatting.   If you double-clicking on Format Painter you can then click on multiple individual files to which to apply the format. Or, you can click and drag across a group of cells. When you are finished applying formats, click on Format Painter again, or on the Esc key, to turn it off. The Format Painter is a very simple, but extremely useful and time saving tool when creating complex worksheets. Similar Articles Productive Geek Tips Use Conditional Formatting to Find Duplicate Data in Excel 2007Remove Text Formatting in Firefox the Easy WayMake Excel 2007 Always Save in Excel 2003 FormatUsing Conditional Cell Formatting in Excel 2007Make Word 2007 Always Save in Word 2003 Format TouchFreeze Alternative in AutoHotkey The Icy Undertow Desktop Windows Home Server – Backup to LAN The Clear & Clean Desktop Use This Bookmarklet to Easily Get Albums Use AutoHotkey to Assign a Hotkey to a Specific Window Latest Software Reviews Tinyhacker Random Tips DVDFab 6 Revo Uninstaller Pro Registry Mechanic 9 for Windows PC Tools Internet Security Suite 2010 New Firefox release 3.6.3 fixes 1 Critical bug Dark Side of the Moon (8-bit) Norwegian Life If Web Browsers Were Modes of Transportation Google Translate (for animals) Roadkill’s Scan Port scans for open ports

  Read the article

• excel vba to CRUD drupal nodes

  - by Kirk Hings

  We need to periodically migrate Excel reports data into Drupal nodes. We looked at replicating some Excel functionality in Drupal with slickgrid, but it wasn't up to snuff. The Excel reports people don't want to double-enter their data, but their data is important to be in this Drupal site. They have hundreds of Excel reports, and update a row in each weekly. We want a button at the row end to fire a VBA macro that submits the data to Drupal, where a new node is created from the info submitted. (Yes, we are experienced with both Drupal and VBA; all users and the site are behind our firewall.) We need the new node's nid or URL returned so we can then create a link in Excel directly to that node Site is D6, using Services 3.x module. I tried the REST server module, but we can't get it to retrieve data without session authentication on, which we can't do from Excel. (unless you can?) I also noticed the 'data' it was returning via browser url was 14 or 20 nodes' info, not the one nid requested (Example: http://mysite.com/services/rest/report/node/30161) When I attempt to create a simple node like this from VBA: Dim MyURL as String MyURL = "http://mysite.com/services/rest/report/node?node[type]=test&node[title]=testing123&node[field_test_one][0][value]=123" Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP") With objHTTP .Open "POST", MyURL, False .setRequestHeader "Content-Type", "application/x-www-form-urlencoded" .send (MyURL) End With I get HTTP Status: Unauthorized: Access denied for user 0 "anonymous" and HTTP Response: null Everything I search for has examples in php or java, nothing in VBA. Also tried switching to using an XMLRPC server but that's even more confusing. We would like json (used application/json, set formatter accordingly in REST server settings), but will use anything that works. Ideas? Thanks in advance!

  Read the article

• SQL SERVER – Excel Losing Decimal Values When Value Pasted from SSMS ResultSet

  - by pinaldave

  No! It is not a SQL Server Issue or SSMS issue. It is how things work. There is a simple trick to resolve this issue. It is very common when users are coping the resultset to Excel, the floating point or decimals are missed. The solution is very much simple and it requires a small adjustment in the Excel. By default Excel is very smart and when it detects the value which is getting pasted is numeric it changes the column format to accommodate that. Now as Zero which are training any digit after decimal points have no value, Excel automatically hides it. To prevent this to happen user has to convert columns to text format so it can preserve the formatting. Here is how you can do it. Select the corner between A and 1 and Right Click on it. It will select complete spreadsheet. If you want to change the format of any column you can select an individual column the same way. In the menu Click on Format Cells… It will bring up the following menu. Here by default the selected column will be General, change that to Text. It will change the format of all the cells to Text. Now once again paste the values from SSMS to the Excel. This time it will preserve the decimal values from SSMS. Solved! Any other trick you do you know to preserve the decimal values? Leave a comment please. Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, SQL Utility, T SQL, Technology Tagged: Excel

  Read the article

• Writing Macros to find a specific cell and paste the value from a control cell into it

  - by G-Edinburgh

  I am having some issues writing a Macro to do the following. I have a very long list of rooms with two columns one containing the room number i.e. B-CL102 and the other containing a varying integer.I am looking to create a new column that will contain another different integer for each of the rooms. Is there any way to write a Macro so that I can use two control cells at the top of the sheet, type the room number into one and the integer matching that room into another, then run the Macro and it will automatically populate the correct cell. Then I can change the two values in the control cells and run the Macro again and so on. Thanks for your help, I have a very minimal amount of experience with Macros essentially just the basics. Thanks G

  Read the article

• COM Add-in for Excel doesn't load when Excel is launched by opening file

  - by Nick Hebb

  Several users have reported that if they launch Excel by double-clicking an Excel file, the add-in will not load. But, if they open Excel via the Start menu (or Quick launch toolbar) the add-in loads fine. Some details, in case they help: It is a COM add-in, written in VB6. The problem has been reported on Windows XP/Excel 2003 and Vista/Excel 2007 systems. The add-in implements IDTExtensibility2. The start mode is set to "Load on Startup". Any thoughts on the cause or how to troubleshoot this would be greatly appreciated.

  Read the article

• Unable to diligently close the excel process running in memory

  - by NewAutoUser

  I have developed a VB.Net code for retrieving data from excel file .I load this data in one form and update it back in excel after making necessary modifications in data. This complete flow works fine but most of the times I have observed that even if I close the form; the already loaded excel process does not get closed properly. I tried all possible ways to close it but could not be able to resolve the issue. Find below code which I am using for connecting to excel and let me know if any other approach I may need to follow to resolve this issue. Note: I do not want to kill the excel process as it will kill other instances of the excel Dim connectionString As String connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & ExcelFilePath & "; Extended Properties=excel 8.0; Persist Security Info=False" excelSheetConnection = New ADODB.Connection If excelSheetConnection.State = 1 Then excelSheetConnection.Close() excelSheetConnection.Open(connectionString) objRsExcelSheet = New ADODB.Recordset If objRsExcelSheet.State = 1 Then objRsExcelSheet.Close() Try If TestID = "" Then objRsExcelSheet.Open("Select * from [" & ActiveSheet & "$]", excelSheetConnection, 1, 1) Else objRsExcelSheet.Open("Select Test_ID,Test_Description,Expected_Result,Type,UI_Element,Action,Data,Risk from [" & ActiveSheet & "$] WHERE TEST_Id LIKE '" & TestID & ".%'", excelSheetConnection, 1, 1) End If getExcelData = objRsExcelSheet Catch errObj As System.Runtime.InteropServices.COMException MsgBox(errObj.Message, , errObj.Source) Return Nothing End Try excelSheetConnection = Nothing objRsExcelSheet = Nothing

  Read the article

• Random Sampling in Excel

  - by bonsvr

  I have an Excel sheet as follows: NO NAME AMOUNT 1 A 50 1 B 50 2 A 100 2 C 100 3 D 70 3 B 70 4 A 30 4 F 30 5 C 150 5 G 150 . . . . There are let's say 10,000 rows. I want to get a random sample from rows. There are 2 conditions: 1. Sampling must be based on "NO" column. 2. Size of the sample is determined by the user: it can be %5, %10 or %20. For example, one decides to randomly choose %20 of total rows in the above example: The result is like: NO NAME AMOUNT 2 A 100 2 C 100 90 Z 500 90 E 500 . . . . There should be 2,000 rows. I don't know whether my question is too specific. I am new to Excel VBA, and I faced a situation like this. Above process is about getting a random sample from an account ledger for auditing purposes.

  Read the article

• Excel concatenate strings from cells listed in third cell

  - by Puddingfox

  I have an excel 2007 workbook that has five columns: A. A list of machines B. A list of service numbers for each machine C. A list of service names for each machine ...(nothing here) I. A list of Service Numbers J. A list of Service Names Each machine listed in column A has one or more services running on it from the list in column J. I would like to be able to add services to a machine (i.e. updating the cell in Column C) by simply adding another comma-separated number to Column B. For Example, The first row would look like this assuming Machine1 has the first three services: | A | B | C | Machine1 | 1,2,3 | HTTP,HTTPS,DNS Right now I have to manually update the formula in column c for each change I make. The current formula is: =CONCATENATE(J1,",",J2,",",J3) I would like to use something like this (please forgive my syntax; I'm a coder and I'm treating cell B1 as if it is an indexed array): =CONCATENATE(CELL("J"+B1[0] , "," , "J"+B1[1] , "," "J"+B1[2]) Although having variable numbers of services makes this even more difficult. Is there any way of doing this. For reference, this is columns I and J: | I | J | 1 |HTTP | 2 |HTTPS | 3 |DNS ..... | 16 |Service16 I don't know very much about Excel so any help is greatly appreciated.

  Read the article

• Import data in Excel that doesn't have a row delimiter, but number of columns is known

  - by Alex B

  So i have this text file that looks something like this: Header1 Header2 Header3 Header4 A1 B1 C1 D1 A2 B2 C2 D2 and so on. When imported, I'd want the data to format itself in 4 columns. I tried the Get External Data from Text, and it successfully imports it, but it doesn't wrap it around, so it just keeps making columns for every space. I'd want it to go on the next line after 4 (in this case) elements have been added. What's the simplest way to achieve this? EDIT: My answer follows, since I'm not yet allowed to answer my own questions yet. The Excel function I needed is called indirect(). Not sure how it actually works though, so hopefully someone can help out with that, but the function call that worked for me is =INDIRECT(ADDRESS((ROW(A1)-1)*4+COLUMN(A1),1)) which i found over here: http://www.ozgrid.com/forum/showthread.php?t=101584&p=456031#post456031 Note: this required me to add the text to excel where i'd get this row full of columns, and then flip it so that i'd have a column full of rows.

  Read the article

< Previous Page | 3 4 5 6 7 8 9 10 11 12 13 14  | Next Page >