Search Results

Search found 6495 results on 260 pages for 'excel newbie'.

Page 14/260 | < Previous Page | 10 11 12 13 14 15 16 17 18 19 20 21  | Next Page >

• How do I extract excel data from multiple worksheets and put into one sheet?

  - by user167210

  In a workbook I have 7 sheets(Totals and then Mon to Sat),I want to extract rows which have the word "CHEQ" in its cell (this is a dropdown list with two options-CHEQ/PAID)from all sheets. On my front sheet I used this formula: =IF(ROWS(A$13:A13)>$C$10,"",INDEX(Monday!A$3:A$62,SMALL(IF(Monday[Paid]=$A$10,ROW(Monday[Paid])-ROW(Monday!$I$3)+1),ROWS(A$13:A13)))) This formula works fine for one worksheet (eg. Monday) but is it possible to show the extracted rows from all 6 sheets on the front page? I only have Excel NOT Access. These are the 12 headers on row A12 Col Name Cod House Car Date Discount 2nd Paid Extra Letter Posted The exported data appears like this (this just an example): Col Name Cod House Car Date Discount 2nd Paid Extra Letter Posted 12 Robbs 1244 Ren 11/10 10% 5 CHEQ 0 0 No 15 Jones 7784 Ren 12/10 15% 1 CHEQ 0 0 No 18 Doese 1184 Ren 12/11 12% 1 CHEQ 0 0 No Any ideas on what to do to this formula? I am using Excel 2010.

  Read the article

• How to change x-axis min/max of Column chart in Excel?

  - by Ian Boyd

  Here i have a column chart of binomial distribution, showing how many times you can expect to roll a six in 235 dice rolls: Note: You could also call it a binomial mass distribution for p=1/6, n=235 Now that graph is kinda squooshed. i'd like to change the Minimum and Maximum on the horizontal axis. i'd like to change them to: Minimum: 22 Maximum: 57 Meaning i want to zoom in on this section of the graph: Bonus points to the reader who can say how the numbers 22 and 57 were arrived at If this were a Scatter graph in Excel, i could adjust the horizintal axis minimum and maximum as i desired: Unfortunately, this is a Column chart, where there are no options to adjust the minimum and maximum limits of the ordinate axis: i can do a pretty horrible thing to the graph in Photoshop, but it's not very useful afterwards: Question: how to a change the x-axis minimum and maximum of a Column chart in Excel (2007)?

  Read the article

• Table Formatting in Excel 2007: How do I remove it?

  - by RocketGoal

  I've used the new Table Formatting option in Excel 2007. Now I can't remove it. I've dragged the little blue square up to the last cell on the top left, but it just won't go any further. In fact it just won't go at all. Clear all doesn't remove it. What does? I want my table back! I'm not a beginner with Excel, but this little annoyance has made me feel like on. Surely there must be some way to remove table format without deleting something or clearing all! Thanks Mike

  Read the article

• Copy data from Access to the next row in Excel

  - by edmon

  I have a MS Access database for a small Hotel. On the main form I have Guest Information fields...(Name, Address, Phone#, etc). I also have an Excel file that keeps track of bookings for the Hotel. The following code takes the Guest information from my form in Access and populates the labeled cells in my Excel file. Dim objXLApp As Object Dim objXLBook As Object Set objXLApp = CreateObject("Excel.Application") Set objXLBook = objXLApp.Workbooks.Open("Y:\123files\E\Hotel Reservation.xls") objXLApp.Application.Visible = True objXLBook.ActiveSheet.Range("B2") = Me.GuestFirstName & " " & GuestLastName objXLBook.ActiveSheet.Range("C2") = Me.PhoneNumber objXLBook.ActiveSheet.Range("D2") = Me.cboCheckInDate objXLBook.ActiveSheet.Range("E2") = Me.cboCheckOutDate objXLBook.ActiveSheet.Range("G2") = Me.RoomType objXLBook.ActiveSheet.Range("H2") = Me.RoomNumber End Sub Is there a way to, move to the next row in my Excel file, for a new guests info? EX. I take my first guests info and it populates row 2 of my Excel file. For my next guest it will populate row 3 of my Excel file and so on....

  Read the article

• FileSystemWatcher.Changed fires immediately when Excel 2007 opens XLS file in compatibility mode

  - by Rick Mogstad

  We use a FileSystemWatcher to monitor documents opened from our Document Management system, and if the user saves the document, we ask if they would also like them updated in our system. We have a problem with XLS files in Excel 2007 (have not verified that the problem does not exist in 2003, but it only seems to be files that open in compatibility mode in 2007) where the Changed event fires immediately upon opening the file, and then once more upon closing the file, even if nothing has changed or the user chooses not to save upon closing. This same behavior does not exist when opening XLSX files. I wrote a test app to verify the behavior, which you can find at (http://www.just2guys.net/SOFiles/FSWExcel.zip). In the app, there is one FileSystemWatcher for each NotifyFilter type, so that it is apparent why the Changed event was fired. Any way you can think of to only prompt the user when the document is actually saved in some way by the user? I can start monitoring the file after Process.Start is called, which allows me to skip the message upon opening the document, but I still get one upon closing the document, even when nothing was changed.

  Read the article

• Excel vba -get ActiveX Control checkbox when event handler is triggered

  - by danoran

  I have an excel spreadsheet that is separated into different sections with named ranges. I want to hide a named range when a checkbox is clicked. I can do this for one checkbox, but I would like to have a single function that can hide the appropriate section based on the calling checkbox. I was planning on calling that function from the event_handlers for when the checkboxes are clicked, and to pass the checkbox as an argument. Is there a way to access the checkbox object that calls the event handler? This works: Sub chkDogsInContest_Click() ActiveSheet.Names("DogsInContest").RefersToRange.EntireRow.Hidden = Not chkMemberData.Value End Sub But this is what I would like to do: Sub chkDogsInContest_Click() Module1.Show_Hide_Section (<calling checkbox>) End Sub These functions are defined in a different module: 'The format for the the names of the checkbox controls is 'CHECKBOX_NAME_PREFIX + <name> 'where "name" is also the name of the associated Named Range Public Const CHECKBOX_NAME_PREFIX As String = "chk" 'The format for the the names of the checkbox controls is 'CHECKBOX_NAME_PREFIX + <name> 'where "name" is also the name of the associated Named Range Public Function CheckName_To_SectionName(ByRef strCheckName As String) CheckName_To_SectionName = Mid(strCheckName, CHECKBOX_NAME_PREFIX.Length() + 1) End Function Public Sub Show_Hide_Section(ByRef chkBox As CheckBox) ActiveSheet.Names(CheckName_To_SectionName(chkBox.Name())).RefersTo.EntireRow.Hidden = True End Sub

  Read the article

• Neophyte question about using Subtotal and CountIf in Excel

  - by Andrew

  Hi, I'm using Excel and having some problems with Countif and I don't understand how it works differently from SubTotal. I used the GUI to subtotal stuff and all the subtotals are right. Then I attempted to use the Countif to see how many requirements passed. That worked for the first subtotal only. It's easy to see why. When I look at the box for the subtotal, it says: =SUBTOTAL(3,C286:C292) When I look at my formula for passed requirements, I have: =IF(ISTEXT(A285),COUNTIF(C286:C338,"=Passed"),"") Notice that the last column is wrong. How did the Subtotal manage to keep this correct? I typed in the formula for passed requirements and dragged it down the page. Everything behaved as expected (even the bit about ISTEXT dutifully figured out which row was which), but it got the last row wrong. Any ideas? SRS Maintenance Count 7 44 SRS Maintenance Passed SRS Maintenance Passed SRS Maintenance Passed SRS Maintenance Passed SRS Maintenance Passed SRS Maintenance Passed SRS Maintenance Passed SRS Reports Count 12 43 SRS Reports Passed SRS Reports Passed SRS Reports Passed SRS Reports Passed SRS Reports Failed SRS Reports Passed SRS Reports Passed SRS Reports Failed SRS Reports Passed SRS Reports Passed SRS Reports Failed

  Read the article

• Changing Positions of the Chart When Creating Multiple Charts Automatically via Vbasic in Excel 2007

  - by McVey

  I am creating a new chart for each row of data in an Excel spreadsheet. I have the Vbasic working properly, but I want to change the position of the chart on the sheet that is added for each row. Below is my code, what do I need to do to change the position of the chart on the page automatically? Ideally, I would like it to be in the upper left hand corner of each sheet. Sub DrawCharts() Dim Ws As Worksheet Dim NewWs As Worksheet Dim cht As Chart Dim LastRow As Long Dim CurrRow As Long Set Ws = ThisWorkbook.Worksheets("Sheet1") LastRow = Ws.Range("A65536").End(xlUp).Row For CurrRow = 2 To LastRow Set NewWs = ThisWorkbook.Worksheets.Add NewWs.Name = Ws.Range("A" & CurrRow).Value Set cht = ThisWorkbook.Charts.Add With cht .ChartType = xl3DColumnClustered .SeriesCollection.NewSeries .SeriesCollection(1).Values = "=" & Ws.Name & "!R" & CurrRow & "C3:R" & CurrRow & "C8" .SeriesCollection(1).Name = "=" & Ws.Name & "!R" & CurrRow & "C2" .SeriesCollection(1).XValues = "Sheet1!R1C3:R1C8" .Axes(xlValue).MinimumScale = 0 .Axes(xlValue).MaximumScale = 1 .Axes(xlValue).MajorUnit = 0.2 .SetElement (msoElementDataLabelShow) .SetElement (msoElementLegendNone) .Location Where:=xlLocationAsObject, Name:=NewWs.Name End With Next CurrRow End Sub Any help is appreciated.

  Read the article

• VBA Macro to save an excel file to a different backup location

  - by Joe Taylor

  I am trying to create a Macro that either runs on close or on save to backup the file to a different location. At the moment the Macro I have used is: Private Sub Workbook_BeforeClose(Cancel As Boolean) 'Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 'Saves the current file to a backup folder and the default folder 'Note that any backup is overwritten Application.DisplayAlerts = False ActiveWorkbook.SaveCopyAs Filename:="T:\TEC_SERV\Backup file folder - DO NOT DELETE\" & _ ActiveWorkbook.Name ActiveWorkbook.Save Application.DisplayAlerts = True End Sub This creates a backup of the file ok the first time, however if this is tried again I get: Run-Time Error '1004'; Microsoft Office Excel cannot access the file 'T:\TEC_SERV\Backup file folder - DO NOT DELETE\Test Macro Sheet.xlsm. There are several possible reasons: The file name or path does not exist The file is being used by another program The workbook you are trying to save has the same name as a... I know the path is correct, I also know that the file is not open anywhere else. The workbook has the same name as the one I'm trying to save over but it should just overwrite. Any help would be much appreciated. Joe

  Read the article

• How do I delete duplicates between two excel sheets quickly vba

  - by MainTank

  I am using vba and I have two sheets one is named "Do Not Call" and has about 800,000 rows of data in column A. I want to use this data to check column I in the second sheet, named "Sheet1". If it finds a match I want it to delete the whole row in "Sheet1". I have tailored the code I have found from a similar question here: Excel formula to Cross reference 2 sheets, remove duplicates from one sheet and ran it but nothing happens. I am not getting any errors but it is not functioning. Here is the code I am currently trying and have no idea why it is not working Option Explicit Sub CleanDupes() Dim wsA As Worksheet Dim wsB As Worksheet Dim keyColA As String Dim keyColB As String Dim rngA As Range Dim rngB As Range Dim intRowCounterA As Integer Dim intRowCounterB As Integer Dim strValueA As String keyColA = "A" keyColB = "I" intRowCounterA = 1 intRowCounterB = 1 Set wsA = Worksheets("Do Not Call") Set wsB = Worksheets("Sheet1") Dim dict As Object Set dict = CreateObject("Scripting.Dictionary") Do While Not IsEmpty(wsA.Range(keyColA & intRowCounterA).Value) Set rngA = wsA.Range(keyColA & intRowCounterA) strValueA = rngA.Value If Not dict.Exists(strValueA) Then dict.Add strValueA, 1 End If intRowCounterA = intRowCounterA + 1 Loop intRowCounterB = 1 Do While Not IsEmpty(wsB.Range(keyColB & intRowCounterB).Value) Set rngB = wsB.Range(keyColB & intRowCounterB) If dict.Exists(rngB.Value) Then wsB.Rows(intRowCounterB).delete intRowCounterB = intRowCounterB - 1 End If intRowCounterB = intRowCounterB + 1 Loop End Sub I apologize if the above code is not in a code tag. This is my first time posting code online and I have no idea if I did it correctly.

  Read the article

• How to convert a column number (eg. 127) into an excel column (eg. AA)

  - by krolley

  How do you convert a numerical number to an Excel column name in C# without using automation getting the value directly from Excel. Excel 2007 has a possible range of 1 to 16384, which is the number of columns that it supports. The resulting values should be in the form of excel column names, e.g. A, AA, AAA etc.

  Read the article

• Introducing MySQL for Excel

  - by Javier Treviño

  As part of the new product initiatives of the MySQL on Windows group we released a tool that makes the task of getting data in and out of a MySQL Database very friendly and intuitive, and we paired it with one of the preferred applications for data analysis and manipulation in Windows platforms, MS Excel. Welcome to MySQL for Excel, an add-in that is installed and accessed from within the MS Excel’s Data tab offering a wizard-like interface arranged in an elegant yet simple way to help users browse MySQL Schemas, Tables, Views and Procedures and perform data operations against them using MS Excel as the vehicle to drive the data in and out MySQL Databases. One of the coolest features we had in mind designing MySQL for Excel is simplicity. MS Excel is simple and easy to work with, thus liked by many Windows users because they don’t have to be software gurus to use it.  We applied the same principle by targeting MySQL for Excel to any kind of user, so if you are already familiarized with Excel’s interface you will find yourself working with MySQL data in no time. MySQL for Excel is shipped within the MySQL Installer as one of the tools in the suite; if prerequisites are already installed (.NET Framework 4.0, Visual Studio Tools for Office 4.0 and of course MS Office), installing the add-in involves a very few clicks and no further setup to use it. Being an Excel Add-In there is no executable file involved after the installation, running MS Excel and opening the add-in from its Data tab is all that is required. MySQL for Excel automatically integrates with MySQL Workbench (if installed) to share the same connections to MySQL Server installations, that way connections are defined just once in either product saving time.  Opening the Add-In brings the Welcome Panel at the right side of the Excel main window from which connections to MySQL Servers are shown grouped by Local VS Remote connections; then users can open any of those connections by double-clicking it and entering the password of the used account.  Additionally a user can create a connection by clicking on the New Connection action label or edit connections through MySQL Workbench (if installed) by clicking on the Manage Connections action label. Once a connection is opened, the Schema Selection panel is shown, at the top of it the selected connection (connection name, hostname/IP and username). Just below, a list of schemas is displayed where User Schemas are grouped first followed by System Schemas; users can double-click any selected schema to go to the next panel or select a schema and clicking the Next > button. Users can alternatively click on the < Back button to go back to the Welcome Panel to close the current connection and open a new one; also by clicking the Create New Schema action label they can create an empty new schema. Once a schema is opened the DB Object Selection panel is shown, this is actually the place where the fun stuff happens; from here users are able to perform actions against MySQL Tables, Views and Procedures. ">The actions available here are about importing data from a MySQL Table, View or Procedure to Excel, exporting Excel data to a new MySQL Table, appending Excel data to an existing MySQL Table or editing a MySQL Table’s data by using an Excel Worksheet as a user interface to update data in any row/column, insert new rows or delete existing rows in a very easy and friendly way. More blog posts will follow describing all of these actions, so stay tuned! Remember that your feedback is very important for us, so drop us a message: · MySQL on Windows (this) Blog - https://blogs.oracle.com/MySqlOnWindows/ · Forum - http://forums.mysql.com/list.php?172 · Facebook - http://www.facebook.com/mysql Cheers!

  Read the article

• VBA Excel - Workbook_SheetChange

  - by user2947014

  Hopefully this question hasn't already been asked, I tried searching for an answer and couldn't find anything. This is probably a simple question, but I am writing my first macro in excel and am having a problem that I can't find out a solution to. I wrote a couple of macros that basically sum up columns dynamically (so that the number of rows can change and the formula moves down automatically) based on a value in another column of the same row, and I call those macros from the event Workbook_SheetChange. The problem I'm having is, I change a cell's value from my macro to display the result of the sum, and this then calls Workbook_SheetChange again, which I do not want. Right now it works, but I can trace it and see that Workbook_SheetChange is being called multiple times. This is preventing me from adding other cell changes to the macros, because then it results in an infinite loop. I want the macros to run every time a change is made to the sheet, but I don't see any way around allowing the macros to change a cell's value, so I don't know what to do. I will paste my code below, in case it is helpful. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim Row As Long Dim Col As Long Row = Target.Row Col = Target.Column If Col <> 7 Then Range("G" & Row).Select Selection.Formula = "=IF(F" & Row & "=""Win"",E" & Row & ",IF(F" & Row & "=""Loss"",-D" & Row & ",0))" Target.Select End If Call SumRiskColumn End Sub Private Sub Workbook_SheetCalculate(ByVal Sh As Object) Call SumOutcomeColumn End Sub Sub SumOutcomeColumn() Dim N As Long N = Cells(Rows.Count, "A").End(xlUp).Row Cells(N + 1, "G").Formula = "=SUM(G2:G" & N & ")" End Sub Sub SumRiskColumn() Dim N As Long N = Cells(Rows.Count, "A").End(xlUp).Row Dim CurrTotalRisk As Long CurrTotalRisk = 0 For i = 2 To N If IsEmpty(ActiveSheet.Cells(i, 6)) And Not IsEmpty(ActiveSheet.Cells(i, 1)) And Not IsEmpty(ActiveSheet.Cells(i, 2)) And Not IsEmpty(ActiveSheet.Cells(i, 3)) Then CurrTotalRisk = CurrTotalRisk + ActiveSheet.Cells(i, 4).Value End If Next i Cells(N + 1, "D").Value = CurrTotalRisk End Sub Thank you for any help you can give me! I really appreciate it.

  Read the article

• How to fill in Different String Values in Different Cells in Excel 2007 VBA marcos

  - by user325160

  Hello everyone, So I am trying to fill in Values "A-Z, 0-9" in a 2007 excel macro in four different locations (I am trying to put A-Z and 0-9 in cells: a1 to d9, e1 to h9, a10 to d18, and e10 to h18). So far I have the code: Sub TwoDArrays() Dim Matrix(9, 4) As Variant Dim Matrix2(9, 4) As Variant Dim Matrix3(9, 4) As Variant Dim Matrix4(9, 4) As Variant Matrix(1, 1) = "A" Matrix(1, 2) = "B" Matrix(1, 3) = "C" Matrix(1, 4) = "D" Matrix(2, 1) = "E" Matrix(2, 2) = "F" Matrix(2, 3) = "G" Matrix(2, 4) = "H" Matrix(3, 1) = "I" Matrix(3, 2) = "J" Matrix(3, 3) = "K" Matrix(3, 4) = "L" Matrix(4, 1) = "M" Matrix(4, 2) = "N" Matrix(4, 3) = "O" Matrix(4, 4) = "P" Matrix(5, 1) = "Q" Matrix(5, 2) = "R" Matrix(5, 3) = "S" Matrix(5, 4) = "T" Matrix(6, 1) = "U" Matrix(6, 2) = "V" Matrix(6, 3) = "W" Matrix(6, 4) = "X" Matrix(7, 1) = "Y" Matrix(7, 2) = "Z" Matrix(7, 3) = "0" Matrix(7, 4) = "1" Matrix(8, 1) = "2" Matrix(8, 2) = "3" Matrix(8, 3) = "4" Matrix(8, 4) = "5" Matrix(9, 1) = "6" Matrix(9, 2) = "7" Matrix(9, 3) = "8" Matrix(9, 4) = "9" Matrix2(1, 1) = "A" Matrix2(1, 2) = "B" Matrix2(1, 3) = "C" Matrix2(1, 4) = "D" Matrix2(2, 1) = "E" Matrix2(2, 2) = "F" Matrix2(2, 3) = "G" Matrix2(2, 4) = "H" Matrix2(3, 1) = "I" Matrix2(3, 2) = "J" Matrix2(3, 3) = "K" Matrix2(3, 4) = "L" Matrix2(4, 1) = "M" Matrix2(4, 2) = "N" Matrix2(4, 3) = "O" Matrix2(4, 4) = "P" Matrix2(5, 1) = "Q" Matrix2(5, 2) = "R" Matrix2(5, 3) = "S" Matrix2(5, 4) = "T" Matrix2(6, 1) = "U" Matrix2(6, 2) = "V" Matrix2(6, 3) = "W" Matrix2(6, 4) = "X" Matrix2(7, 1) = "Y" Matrix2(7, 2) = "Z" Matrix2(7, 3) = "0" Matrix2(7, 4) = "1" Matrix2(8, 1) = "2" Matrix2(8, 2) = "3" Matrix2(8, 3) = "4" Matrix2(8, 4) = "5" Matrix2(9, 1) = "6" Matrix2(9, 2) = "7" Matrix2(9, 3) = "8" Matrix2(9, 4) = "9" Matrix3(1, 1) = "A" Matrix3(1, 2) = "B" Matrix3(1, 3) = "C" Matrix3(1, 4) = "D" Matrix3(2, 1) = "E" Matrix3(2, 2) = "F" Matrix3(2, 3) = "G" Matrix3(2, 4) = "H" Matrix3(3, 1) = "I" Matrix3(3, 2) = "J" Matrix3(3, 3) = "K" Matrix3(3, 4) = "L" Matrix3(4, 1) = "M" Matrix3(4, 2) = "N" Matrix3(4, 3) = "O" Matrix3(4, 4) = "P" Matrix3(5, 1) = "Q" Matrix3(5, 2) = "R" Matrix3(5, 3) = "S" Matrix3(5, 4) = "T" Matrix3(6, 1) = "U" Matrix3(6, 2) = "V" Matrix3(6, 3) = "W" Matrix3(6, 4) = "X" Matrix3(7, 1) = "Y" Matrix3(7, 2) = "Z" Matrix3(7, 3) = "0" Matrix3(7, 4) = "1" Matrix3(8, 1) = "2" Matrix3(8, 2) = "3" Matrix3(8, 3) = "4" Matrix3(8, 4) = "5" Matrix3(9, 1) = "6" Matrix3(9, 2) = "7" Matrix3(9, 3) = "8" Matrix3(9, 4) = "9" Matrix4(1, 1) = "A" Matrix4(1, 2) = "B" Matrix4(1, 3) = "C" Matrix4(1, 4) = "D" Matrix4(2, 1) = "E" Matrix4(2, 2) = "F" Matrix4(2, 3) = "G" Matrix4(2, 4) = "H" Matrix4(3, 1) = "I" Matrix4(3, 2) = "J" Matrix4(3, 3) = "K" Matrix4(3, 4) = "L" Matrix4(4, 1) = "M" Matrix4(4, 2) = "N" Matrix4(4, 3) = "O" Matrix4(4, 4) = "P" Matrix4(5, 1) = "Q" Matrix4(5, 2) = "R" Matrix4(5, 3) = "S" Matrix4(5, 4) = "T" Matrix4(6, 1) = "U" Matrix4(6, 2) = "V" Matrix4(6, 3) = "W" Matrix4(6, 4) = "X" Matrix4(7, 1) = "Y" Matrix4(7, 2) = "Z" Matrix4(7, 3) = "0" Matrix4(7, 4) = "1" Matrix4(8, 1) = "2" Matrix4(8, 2) = "3" Matrix4(8, 3) = "4" Matrix4(8, 4) = "5" Matrix4(9, 1) = "6" Matrix4(9, 2) = "7" Matrix4(9, 3) = "8" Matrix4(9, 4) = "9" For i = 1 To 9 For j = 1 To 4 Cells(i, j) = Matrix(i, j) Next j Next i 'For i = 1 To 9 'For j = 1 To 4 ' Range("a1:d1", "a1:a10").Value = Matrix(i, j) 'Application.WorksheetFunction.Transpose (Matrix) 'Next j 'Next i End Sub However, at the top for loop where it does not use the Range function with the cells, I can only do this for cells a1:d9 (a1 to d9) and if I use the second for loop with the range, get the value 9 appearing in every cell from a1 to d9. So is there a way to make it so that I can get the values A-Z and 0-9 in the other cells I specified above? Thank you.

  Read the article

• Determine Excel Version/Culture via Microsoft.Office.Interop.Excel

  - by DotNetter

  How can I achieve that in .NET/C#?

  Read the article

• Interacting with IE using sendkeys from Excel

  - by Thomas Egan

  I'm trying to write an application which uses values from excel and then switches to a web application using sendkeys. The problem I am having is that I cannot used sendkeys ("{ENTER}") or sendkeys ("^o") as I don't have the access for that. I'm trying to automate a very trivial admin task. I've thought about using the mouse to interact with the links as well as pausing and waiting for the user to just hit return but so far have been unable to come up with a solution. Do you think there is anyway around this? I have some VBA knowledge (enough to get me this far) but not a great deal.

  Read the article

• poor performance while exporting data to excel from DB2

  - by Naga

  I am facing a performance issue while exporting data from DB2 to Excel 2003. Well the very first reason is file is about 10+ MB where it goes outofMemory Exception. I am using XLSTransformer and HSSFWorkbook classes to transform my xls file. I also have joins in my query( optional). But user most likely is going to choose these options. When they do so, of course, the data becomes huge and take lot of time and some times goes outOfMemory too. So Please advice me on this.

  Read the article

• How do you set the "global delimiter" in Excel using VBA?

  - by DanM

  I've noticed that if I use the text-to-columns feature with comma as the delimiter, any comma-delimited data I paste into Excel after that will be automatically split into columns. This makes me think Excel must have some kind of global delimiter. If this is true, how would I set this global delimiter using Excel VBA? Is it possible to do this directly, or do I need to "trick" Excel by doing a text-to-columns on some junk data, then delete the data? My ultimate goal is to be able to paste in a bunch of data from different files using a macro, and have Excel automatically split it into columns according to the delimiter I set.

  Read the article

• How do you set the "global delimiter" in Excel using VBA (or unicorns)?

  - by DanM

  I've noticed that if I use the text-to-columns feature with comma as the delimiter, any comma-delimited data I paste into Excel after that will be automatically split into columns. This makes me think Excel must have some kind of global delimiter. If this is true, how would I set this global delimiter using Excel VBA? Is it possible to do this directly, or do I need to "trick" Excel by doing a text-to-columns on some junk data, then delete the data? My ultimate goal is to be able to paste in a bunch of data from different files using a macro, and have Excel automatically split it into columns according to the delimiter I set.

  Read the article

• Creating a border around cells with the same value.

  - by Liam

  I have a table like the one below. How can I get Excel to put borders around groups with the same number in the 4th column so that there is a border around the groups. I was thinking conditional formatting could do it but I can't think how. So I think the only option is a macro. Could anybody help? 1 64436 549419 1 2 64437 549420 1 3 64438 549421 1 4 64439 549422 1 5 64440 549423 1 6 64441 549424 1 7 64442 549425 1 8 64443 549426 1 9 64444 549427 1 10 64445 549428 1 11 64446 549429 1 12 64447 549430 1 13 64448 549431 2 14 64449 549432 2 15 64450 549433 2 16 64451 549434 2 17 64452 549435 2 18 64453 549436 2 19 64454 549437 2 20 64455 549438 2 21 64456 549439 2 22 64457 549440 4 23 64458 549441 4 24 64459 549442 5 25 64460 549443 5 26 64461 549444 5 27 64462 549445 5 28 64463 549446 5 29 64464 549447 5 30 64465 549448 6 31 64466 549449 6 32 64467 549450 6 33 64468 549451 6 34 64469 549452 6 35 64470 549453 6 36 64471 549454 6 37 64472 549455 9 38 64473 549456 9 39 64474 549457 9

  Read the article

• Find first cell in a row that contains a number?

  - by Dexter

  I'm working in Excel with an exported table such as this: |-------------------------------------------------------------------------------| | | A | B | C | D | E | F | G | H | I | |---|-------------------|-----|-----|-----|-----|-----|-------|-----|-----------| | 1 | Domain | JAN | FEB | MAR | APR | MAY | Start | End | Change | |---|-------------------|-----|-----|-----|-----|-----|-------|-----|-----------| | 2 | www.mydomain1.com | | 1 | 4 | 3 | 1 | 1 | 1 | 0 | |---|-------------------|-----|-----|-----|-----|-----|-------|-----|-----------| | 3 | www.mydomain2.com | 2 | 4 | 12 | 18 | 23 | 2 | 23 | 21 | |---|-------------------|-----|-----|-----|-----|-----|-------|-----|-----------| | 4 | www.mydomain3.com | | | 14 | 12 | | 14 | xxx | NOT FOUND | |-------------------------------------------------------------------------------| I'm trying to compare the current state (last cell) to the original cell (first cell with a value). In column I, I have the formula =IF(G2 = "xxx", "NOT FOUND", IF(H2 = "xxx", "NOT FOUND", H2 - G2)) In column H, I have the formula =IF(F2 = "", "xxx", F2) In column G, I need to find the first cell with a number. If there isn't one in that range, I need G to be "xxx". I suppose I only need to check for the first cell in the range (B2 to F2) that contains a value, not just a number. I tried using an Index and Match combo, but I couldn't quite understand it.

  Read the article

• Excel - "send to Mail recipient" creates 2nd copy of EMail in Inbox

  - by ssollinger

  When sending Excel sheets using the menu item "File" - "Sent To" - "Mail recipient (as attachment)" I get additional copies of the email in the Inbox. When I press "send" then I get 2 copies of the email in Outlook - as expected one in the Outbox (which moves into the Sent folder as soon it is sent off) and an additional one in the Inbox. How do I stop the copy message appearing in the Inbox? System: Excel 2000, Outlook 2000, Windows XP. Antivirus is AVG Free 2011. I know this is a very old system, but it is not my PC and there isn't any chance to get it replaced in the near future yet. SOme further details: The copy in the inbox appears at the same time as the normal copy goes into the Outbox (i.e. as soon I press send). It doesn't contain anything in the From field (i.e. there is no sender, just the recipient of the mail). It has a different icon in Outlook to the other emails - the icon is the one for "saved or unsent message". I tried it with a few different spreadsheets and it happens with all of them. It happens with every recipient, and it only happens if sending from within Excel (i.e. using the Send to menu item). I can delete the attachment before sending and it will still create a copy. If I create a new message in Outlook and then add the Excel document as attachment then I don't get the copy of the message in the inbox, it only happens when using the send to item in Excel. It only started doing this recently. Around that time the Antivirus (AVG Free) was upgraded to the latest version (from the previous version - 2010? - to version 2011), but this might not be related. I thought I know Excel really well but have never seen this happening before, and I can't find any setting in Excel or Outlook that is causing this. Any ideas?

  Read the article

• How to re-arrange Excel database from 1 long row, into 3 short rows of unequal lengths and automatically repeat the process?

  - by user326884

  This question is an extension/continuation of my previous question at How to re-arrange Excel database from 1 long row, into 3 short rows and automatically repeat the process? which was answered by Jason Lewis of which I'm grateful. But being a dummy in "Indirect' Excel function, I need assistance again : For example :- In Sheet A, Row 1 has the following data in each cell (all together 72 cells occupied): A1 B1 C1 D1 E1 F1 G1 H1 I1 J1 K1 L1 M1 N1 O1 P1 Q1 R1 S1 T1 U1 V1 W1 X1 Y1 Z1 AA1 AB1 AC1 AD1 AE1 AF1 AG1 AH1 AI1 AJ1 AK1 AL1 AM1 AN1 AO1 AP1 AQ1 AR1 AS1 AT1 AU1 AV1 AW1 AX1 AY1 AZ1 BA1 BB1 BC1 BD1 BE1 BF1 BG1 BH1 BI1 BJ1 BK1 BL1 BM1 BN1 BO1 BP1 BQ1 BR1 BS1 BT1 To be re-arranged into Sheet B in the following format: Row 1 : A1 B1 C1 D1 E1 F1 G1 H1 I1 J1 K1 L1 M1 N1 O1 P1 Q1 R1 S1 T1 U1 V1 W1 X1 Y1 Z1 AA1 AB1 AC1 AD1 AE1 AF1 AG1 AH1 AI1 Row 2 : AJ1 AK1 AL1 AM1 AN1 AO1 AP1 AQ1 AR1 AS1 AT1 AU1 AV1 AW1 AX1 AY1 AZ1 BA1 BB1 BC1 BD1 BE1 BF1 BG1 BH1 BI1 BJ1 BK1 Row 3 : BL1 BM1 BN1 BO1 BP1 BQ1 BR1 BS1 BT1 The Sheet A (database sheet) has a lot of rows (example 3,000 rows, each rows has 72 cells occupied with data), hence the Sheet B (reformatted database) is estimated to have 9,000 rows (i.e. 3 x 3,000) of unequal lengths. Thanking you in anticipation of your speedy response.

  Read the article

• Using Excel as front end to Access database (with VBA)

  - by Alex

  I am building a small application for a friend and they'd like to be able to use Excel as the front end. (the UI will basically be userforms in Excel). They have a bunch of data in Excel that they would like to be able to query but I do not want to use excel as a database as I don't think it is fit for that purpose and am considering using Access. [BTW, I know Access has its shortcomings but there is zero budget available and Access already on friend's PC] To summarise, I am considering dumping a bunch of data into Access and then using Excel as a front end to query the database and display results in a userform style environment. Questions: How easy is it to link to Access from Excel using ADO / DAO? Is it quite limited in terms of functionality or can I get creative? Do I pay a performance penalty (vs.using forms in Access as the UI)? Assuming that the database will always be updated using ADO / DAO commands from within Excel VBA, does that mean I can have multiple Excel users using that one single Access database and not run into any concurrency issues etc.? Any other things I should be aware of? I have strong Excel VBA skills and think I can overcome Access VBA quite quickly but never really done Excel / Access link before. I could shoehorn the data into Excel and use as a quasi-database but that just seems more pain than it is worth (and not a robust long term solution) Any advice appreciated. Alex

  Read the article

• How to Transpose Rows and Columns in Excel 2013

  - by Lori Kaufman

  You’ve setup your worksheet with all your row and column headings and you’ve entered all your data. Then, you discover that it would look better if the rows were the columns and the columns were the rows. How do you accomplish this easily? There is an easy way to convert your rows to columns and vice versa using the Transpose feature in Excel. We’ll show you how. Select the cells containing the headings and data you want to transpose. Click Copy or press Ctrl + C. Click in a blank cell on the spreadsheet. This cell will be the top, left corner of the new table of data. Click the down arrow on the Paste button and select Paste Special from the drop-down menu. On the Paste Special dialog box, select the Transpose check box so there is a check mark in the box and click OK. The rows become the columns and the columns become the rows. The original set of data still exists. You can select those cells and delete the headings and data, if desired. Isn’t that a lot easier and faster than retyping all your data?     

  Read the article

< Previous Page | 10 11 12 13 14 15 16 17 18 19 20 21  | Next Page >