Search Results

Search found 7183 results on 288 pages for 'export to excel'.

Page 63/288 | < Previous Page | 59 60 61 62 63 64 65 66 67 68 69 70  | Next Page >

• MS SQL Server Dates Excel

  - by KillerSnail

  I have data this is linked from SQL Server into an excel document. The column format on the SQL Server is datetime2. When I get the data via an ODBC connection it comes across as a string? I tried using CAST(column AS DATE ) but that didn't work. I tried reformatting via CONVERT(VARCHAR(10), column, 103) as well but that didn't work. I tried retrieving the data via Microsoft query as well but that didn't work. At the moment I am using VBA code like: While (ActiveCell.Value <> "") ActiveCell.Value = DATEVALUE(ActiveCell.Value) ActiveCell.Offset(1,0).Activate Wend and looping through each column that needs this treatment but 100000 rows in multiple columns takes forever to loop through. Are there any alternatives?

  Read the article

• excel 2003 can`t edit comments (user)

  - by Dezigo

  I have a .xls file of excel 2003. There are a lot of comments. I can`t edit it. right click -edit comments for example: I have comment: Ludmila: comment goes here Then Ludmila: comment goes here Dezigo:new comment..! I tryed to do: Tools-options-general (change my name to Ludmila),but it`s not work.. Like it Ludmila: comment goes here Ludmila:new comment.. and comment goes here -can`t edit it. file is not protected.

  Read the article

• Count Clicks in excel

  - by rockbala

  Hi, Can some one recommend any free program which counts the number of clicks Clicked inside a cell. For Example Imagine something like Spreadsheet I click on A1 cell the value shows 1 Then I click A1 cell again the value shows 2 and so on If I click A3 cell somewhere in between the click count on Cell A3 shows 1 and so on If something like this can be achieved as a macro with in excel (2003 please) please suggest or any other free program that you might be aware about, please do let me know. I appreciate all your help and thank you in advance. rockbala

  Read the article

• Importing data from many excel workbooks and sheets into a single workbook/table

  - by Max Rusalen

  Hi, I have 54 excel files with three sheets each, each sheet has a different amount of data entries but they are set out in a identical format, and I need to import the data from those sheets into a single workbook using VBA. Is there any way I can program it so I can build the loops to import the data, but without having to write in each workbook name for each loop/sheet? I think I can use the call function, but I don't know how to make the loop codes independent of the workbook name they apply to. Thank you so much in advance, Millie

  Read the article

• VSTO 3.0 Get/Change an excel 2007 workbook connection

  - by RQueiroga

  Hi all, I've struggling to find a way to get and change and excel 2007 workbook connection (Menu Data - Existing Connections - Connections on this Workbook).It's a connection (several actually) to a SQL Server and used in a pivot table. I've tried using Application.ActiveWorkbook.Connections or Globals.ThisWorkbook.Connections but they both return always Null..I've tried in an sheet event as well as in a custom ribbon's button event as well. The only way left I can think of is use to code a VBA method that does the work and then invoque it in my VSTO code, but it's not very elegant is it... Thanks in advanced :-)

  Read the article

• Create xml file based on xPath from Excel with VBA

  - by user1626236

  I've found some items that seem to dance around what I'm looking to do. I'm not a full-fledged programmer, but have been creating my own macros in Excel. I'm trying to create one now that will help me create the structure of an XML file. I want to go down the list of XPaths, and for each one create any parents in the path as needed as well as the child and its value. I'll be adding another aspect to filter it to the fields I want, but the part I'm struggling with is the logic to check if each parent node exists, create it if needed, then add the child and its value. Bonus would be if the output file were formatted with each node on a new line, and more so if it child nodes were indented from parent, but just creating it is my primary concern. Any help would be much appreciated, this has to potential to save me a lot of time.

  Read the article

• How to turn off Excel "Header Row" without losing data in it?

  - by Ken

  I've been sent an Excel spreadsheet with a weird first row. Some of the cells say "Column1", "Column2", etc., but I can't delete their contents. If I select the cell and hit backspace, it goes blank, but when I press return, it goes right back to saying "Column1". I found another answer here that suggested this could be caused by "Cell validation", but the validation window says "Any value", and also "show alert" (and I'm not seeing an alert), so I don't think that's it. The first row is white text on a blue background, if that means anything. The spreadsheet was sent to me in XLSX format, but I tried resaving as XLS and opening that, and it seems to make no difference. This is with the "ribbon" version of Excel (they got rid of the Help menu so I don't know how to see what version number it is!). Thanks! Update: The Excel online help says to use ribbon Home tab - Cells - Delete - ... to delete cells. When I select anything on the first row, this pop-up menu is dimmed. So maybe Excel doesn't think row 1 consists of "cells"? Though I don't know what else it would call them. Update 2: I found it, kind of. If I click the "Design" tab in the ribbon, then uncheck "Header Row", then first row becomes a normal row of cells again. Unfortunately, the contents disappear entirely. I want to delete a few cells, not all 50+! And if I copy the first row before turning off "Header Row", it disappears from the clipboard when I uncheck that. So I kind of know what mode it's stuck in, but not a good way out of it.

  Read the article

• Putting together CSV Cells in Excel with a macro

  - by Eric Kinch

  So, I have a macro to export data into CSV format and it's working great (Code at the bottom). The problem is the data I am putting into it. When I put the data in question in it comes out Firstname,Lastname,username,password,description I'd like to change it so I get Firstname Lastname,Firstname,Lastname,username,password,description What I'd like to do is manipulate my existing macro so to accomplish this. I'm not so good at VBS so any input or a shove in the right direction would be fantastic. Thanks! Sub Make_CSV() Dim sFile As String Dim sPath As String Dim sLine As String Dim r As Integer Dim c As Integer r = 1 'Starting row of data sPath = "C:\CSVout\" sFile = "MyText_" & Format(Now, "YYYYMMDD_HHMMSS") & ".CSV" Close #1 Open sPath & sFile For Output As #1 Do Until IsEmpty(Range("A" & r)) 'You can also Do Until r = 17 (get the first 16 cells) sLine = "" c = 1 Do Until IsEmpty(Cells(1, c)) 'Number of Columns - You could use a FOR / NEXT loop instead sLine = sLine & """" & Replace(Cells(r, c), ";", ":") & """" & "," c = c + 1 Loop Print #1, Left(sLine, Len(sLine) - 1) 'Remove the trailing comma r = r + 1 Loop Close #1 End Sub

  Read the article

• Excel 2007 - "The macro may not be available in this workbook" Error

  - by Psycho Bob

  We use an Excel sheet that has been protected to prevent modification of it from end users. All in all they are only able to edit certain tabs to add information that will then be used to generate information on other tabs using equations and such. On the tab with the equations, a button is present called "Prep for Internal Hard Copy Print." This button runs a macro that selects the information on the tab, unprotects it, then sends a print job to the user's default printer that contains the unprotected content. Normally this works like a champ. This time around, however, the macro is throwing the following error: Cannot run the macro "FILENAME.xlsx'!MacroName'. The macro may not be available in this workbook or all macros may be disabled. As far as I can tell, the macros are still present within the workbook. This sheet is normally a .xlsm though the user saved it with a different filename as a .xlsx. Also, the macros appear only as MacroName in the .xlsm file and not "FILENAME.xlsx'!MacroName' as it does in the .xlsx. Finally, when I open the .xlsm it asks if I want to enable the macro content while the .xlsx does not prompt for this. Can anyone tell me what's going on with this sheet or know of a way that I can get the macros working in the .xlsx without having to start over with a different sheet?

  Read the article

• Pasting formatted Excel range into Outlook message

  - by Steph

  Hi everyone, I am using Office 2007 and I would like to use VBA to paste a range of formatted Excel cells into an Outlook message and then mail the message. In the following code (that I lifted from various sources), it runs without error and then sends an empty message... the paste does not work. Can anyone see the problem and better yet, help with a solution? Thanks, -Steph Sub SendMessage(SubjectText As String, Importance As OlImportance) Dim objOutlook As Outlook.Application Dim objOutlookMsg As Outlook.MailItem Dim objOutlookRecip As Outlook.Recipient Dim objOutlookAttach As Outlook.Attachment Dim iAddr As Integer, Col As Integer, SendLink As Boolean 'Dim Doc As Word.Document, wdRn As Word.Range Dim Doc As Object, wdRn As Object ' Create the Outlook session. Set objOutlook = CreateObject("Outlook.Application") ' Create the message. Set objOutlookMsg = objOutlook.CreateItem(olMailItem) Set Doc = objOutlookMsg.GetInspector.WordEditor 'Set Doc = objOutlookMsg.ActiveInspector.WordEditor Set wdRn = Doc.Range wdRn.Paste Set objOutlookRecip = objOutlookMsg.Recipients.Add("[email protected]") objOutlookRecip.Type = 1 objOutlookMsg.Subject = SubjectText objOutlookMsg.Importance = Importance With objOutlookMsg For Each objOutlookRecip In .Recipients objOutlookRecip.Resolve ' Set the Subject, Body, and Importance of the message. '.Subject = "Coverage Requests" 'objDrafts.GetFromClipboard Next .Send End With Set objOutlookMsg = Nothing Set objOutlook = Nothing End Sub

  Read the article

• Excel VBA to Update SQL Table

  - by user307655

  Hi All, I have a small excel program that is use to upload data to an SQL server. This has been working well for a while. My problem now is that I would like to offer to users a function to update an existing record in SQL. As each row on this table has a unique id columne. There is a column call UID which is the primary key. This is part of the code currently to upload new data: Set Cn = New ADODB.Connection Cn.Open "Driver={SQL Server};Server=" & ServerName & ";Database=" & DatabaseName & _ ";Uid=" & UserID & ";Pwd=" & Password & ";" rs.Open TableName, Cn, adOpenKeyset, adLockOptimistic For RowCounter = StartRow To EndRow rs.AddNew For ColCounter = 1 To NoOfFields rs(ColCounter - 1) = shtSheetToWork.Cells(RowCounter, ColCounter) Next ColCounter Next RowCounter rs.UpdateBatch ' Tidy up rs.Close Set rs = Nothing Cn.Close Set Cn = Nothing Is there anyway i can modify this code to update a particular UID rather than importing new records? Thanks again for your help

  Read the article

• Apache POI Comment Excel

  - by Marquinio

  I need to add a comment to an HSSF Cell in Excel. Everything works fine the very first time but if I open the same file and run the code again it corrupts the file. I've also noticed that I need to create a Drawing object on a Sheet only once: _sheet.createDrawingPatriarch(); If the line above gets executed more than once comments will not work. So has anyone tried adding comments to Cells, closing the file, opening the file again and trying to add more comments to different cells? The below code works but if I open the file again then comments are not added, plus the file gets corrupted!!! Is there a way to get the existing Drawing object from a Sheet? Any ideas appreciated. Thanks!! _drawing = (HSSFPatriarch) _sheet.createDrawingPatriarch(); Row row = _sheet.getRow(rowIndex_); Cell cell = row.getCell(0); CreationHelper factory = _workbook.getCreationHelper(); HSSFAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short)4, 2, (short)6, 5); org.apache.poi.ss.usermodel.Comment comment = _drawing.createComment(anchor); RichTextString str = factory.createRichTextString("Hello, World "+rowIndex_); comment.setString(str); cell.setCellComment(comment);

  Read the article

• Excel VBA Select Case Loop Sub

  - by Zack

  In my excel file, I have a table setup with formulas. with Cells from Range("B2:B12"), Range ("D2:D12"), and etc every other row containing the answers to these formulas. for these cells (with the formula answers), I need to apply conditional formatting, but I have 7 conditions, so I've been using "select case" in VBA to change their interior background based on their number. I have the select case function currently set up within the sheet code, as opposed to it's own macro Private Sub Worksheet_Change(ByVal Target As Range) Dim iColor As Integer If Not Intersect(Target, Range("B2:L12")) Is Nothing Then Select Case Target Case 0 iColor = 2 Case 0.01 To 0.49 iColor = 36 Case 0.5 To 0.99 iColor = 6 Case 1 To 1.99 iColor = 44 Case 2 To 2.49 iColor = 45 Case 2.5 To 2.99 iColor = 46 Case 3 To 5 iColor = 3 End Select Target.Interior.ColorIndex = iColor End If End Sub but using this method, you must be actually entering the value into the cell for the formatting to work. which is why I want to write a subroutine to to do this as a macro. I can input my data, let the formulas work, and when everything is ready, I can run the macro and format those specific cells. I want an easy way to do this, obviously I could waste a load of time, typing out all the cases for every cell, but I figured it'd be easier with a loop. how would I go about writing a select case loop to change the formatting on a a specific range of cells every other row? thank you in advance.

  Read the article

• Excel and SQL, order by help

  - by perlnoob

  Im stuck in Excel 2007, running a query, it worked until I wanted to add a 2nd row containing "field 2". Select "Site Updates"."Posted By", "Site Uploaded"."Site Upload Date" From site_info.dbo."Site Updates" Where ("Site Updates"."Posted By") AND "Site Uploaded"."Site Upload Date">={ts '2010-05-01 00:00:00'}), ("Site Location"='Chicago') Union all Select "Site Updates"."Posted By", "Site Uploaded"."Site Upload Date" From site_info.dbo."Site Updates" Where ("Site Updates"."Posted By") AND "Site Uploaded"."Site Upload Date">={ts '2010-05-01 00:00:00'}), ("Site Location"='Denver') Order By "Site Location" ASC; Basically I want 2 different cells for the locations, example name - Chicago - denver user1 - 100 - 20 user2 - 34 - 1002 Right now for some odd reason, its combining it like: name - chicago user1 - 120 user2 - 1036 Please note updating to 2010 beta is not a viable option for me at this point. Any and all input that will help me is greatly apprecaited. I have read over http://www.techonthenet.com/sql/order_by.php however its not gotten me very far in this question. If you have another SQL resource you recomend for people trying to get their feet wet, I'd greatly apprecaite it. If it helps all the info is on the same table.

  Read the article

• Excel process not ending in Cluster environment

  - by Vasanth

  When we try to close excel object, it fails to close to cluster environment. The same is working fine in QA and UAT environment. public bool KillExcelProcess() { try { object misValue = System.Reflection.Missing.Value; wbObj.Save(); wbObj.Close(true, misValue, misValue); appC.Workbooks.Close(); appC.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(objSheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(wbObj); System.Runtime.InteropServices.Marshal.ReleaseComObject(appC); wbObj = null; appC = null; } catch (Exception ex) { //throw ex; } finally { System.Threading.Thread.Sleep(5000); GC.Collect(); } return true; Calling function #endregion try { log.Info("CloseExcelService (MeasureSavingsComputeBO) Starts ..."); exConverter.KillExcelProcess(); while (true) { try { File.Delete(strFilename); break; } catch (Exception ex) { } }

  Read the article

• Excel automation: Close event missing

  - by chiccodoro

  Another hi all, I am doing Excel automation via Interop in C#, and I want to be informed when a workbook is closed. However, there is no Close event on the workbook nor a Quit event on the application. Has anybody done that before? How can I write a piece of code which reacts to the workbook being closed (which is only executed if the workbook is really closed)? Ideally that should happen after closing the workbook, so I can rely on the file to reflect all changes. Details about what I found so far: There is a BeforeClose() event, but if there are unsaved changes this event is raised before the user being asked whether to save them, so at the moment I can process the event, I don't have the final file and I cannot release the COM objects, both things that I need to have/do. I do not even know whether the workbook will actually be closed, since the user might choose to abort closing. Then there is a BeforeSave() event. So, if the user chooses "Yes" to save unsaved changes, then BeforeSave() is executed after BeforeClose(). However, if the user chooses to "Abort", then hits "file-save", the exact same order of events is executed. Further, if the user chooses "No", the BeforeSave() isn't executed at all. The same holds as long as the user doesn't click any of these options.

  Read the article

• Issue reading in a cell from Excel with Apache POI

  - by Nick

  I am trying to use Apache POI to read in old (pre-2007 and XLS) Excel files. My program goes to the end of the rows and iterates back up until it finds something that's not either null or empty. Then it iterates back up a few times and grabs those cells. This program works just fine reading in XLSX and XLS files made in Office 2010. I get the following error message: Exception in thread "main" java.lang.NumberFormatException: empty String at sun.misc.FloatingDecimal.readJavaFormatString(Unknown Source) at java.lang.Double.parseDouble(Unknown Source) at the line: num = Double.parseDouble(str); from the code: str = cell.toString(); if (str != "" || str != null) { System.out.println("Cell is a string"); num = Double.parseDouble(str); } else { System.out.println("Cell is numeric."); num = cell.getNumericCellValue(); } where the cell is the last cell in the document that's not empty or null. When I try to print the first cell that's not empty or null, it prints nothing, so I think I'm not accessing it correctly.

  Read the article

• Excel VBA creating a new column with formula

  - by Amatya

  I have an excel file with a column which has date data. I want the user to input a date of their choosing and then I want to create a new column that lists the difference in days between the two dates. The Macro that I have is working but I have a few questions and I would like to make it better. Link to MWE small data file is here. The user input date was 9/30/2013, which I stored in H20 Macro: Sub Date_play() Dim x As Date Dim x2 As Date Dim y As Variant x = InputBox(Prompt:="Please enter the Folder Report Date. The following formats are acceptable: 4 1 2013 or April 1 2013 or 4/1/2013") x2 = Range("E2") y = DateDiff("D", x2, x) MsgBox y 'Used DateDiff above and it works but I don't know how to use it to fill a column or indeed a cell. Range("H20").FormulaR1C1 = x Range("H1").FormulaR1C1 = "Diff" Range("H2").Formula = "=DATEDIF(E2,$H$20,""D"")" Range("H2").AutoFill Destination:=Range("H2:H17") Range("H2:H17").Select End Sub Now, could I have done this without storing the user input date in a particular cell? I would've preferred to use the variable "x" in the formula but it wasn't working for me. I had to store the user input in H20 and then use $H$20. What's the difference between the function Datedif and the procedure DateDiff? I am able to use the procedure DateDiff in my macro but I don't know how to use it to fill out my column. Is one method better than the other? Is there a better way to add columns to the existing sheet, where the columns include some calculations involving existing data on the sheet and some user inputs? There are tons of more complicated calculations I want to do next. Thanks

  Read the article

• Parsing a Multi-Index Excel File in Pandas

  - by rhaskett

  I have a time series excel file with a tri-level column MultiIndex that I would like to successfully parse if possible. There are some results on how to do this for an index on stack overflow but not the columns and the parse function has a header that does not seem to take a list of rows. The ExcelFile looks like is like the following: Column A is all the time series dates starting on A4 Column B has top_level1 (B1) mid_level1 (B2) low_level1 (B3) data (B4-B100+) Column C has null (C1) null (C2) low_level2 (C3) data (C4-C100+) Column D has null (D1) mid_level2 (D2) low_level1 (D3) data (D4-D100+) Column E has null (E1) null (E2) low_level2 (E3) data (E4-E100+) ... So there are two low_level values many mid_level values and a few top_level values but the trick is the top and mid level values are null and are assumed to be the values to the left. So, for instance all the columns above would have top_level1 as the top multi-index value. My best idea so far is to use transpose, but the it fills Unnamed: # everywhere and doesn't seem to work. In Pandas 0.13 read_csv seems to have a header parameter that can take a list, but this doesn't seem to work with parse.

  Read the article

• Excel: Automating the Selection of an Unknown Number of Cells

  - by user1905080

  I’m trying to automate the formatting of an excel file by a macro and am seeking a solution. I have two columns titled Last Name and First Name which I would like to concatenate into a separate column titled Last Name, First Name. This is simple enough when done by hand: create one cell which does this, then drag that cell to include all cells within the range. The problem appears when trying to automate this. Because I can’t know the number of names that need to be concatenated ahead of time, I can’t automate the selection of cells by dragging. Can you help me automate this? I’ve tried a process of copying the initial concatenated cell, highlighting the column, and then pasting. I’ve also tried to use a formula which returned the concatenation only if there is text in the “Last Name” and “First Name” columns. However, in both cases, I end up with some 100,000 rows, putting a serious cramp on my ability to manipulate the worksheet. The best solution I can think of is to create concatenations within a fixed range of cells. Although this would create useless cells, at least there wouldn’t be 99,900 of them.

  Read the article

• runtime error "out of memory"+excel macro

  - by user356180

  hi experts. i have one macro,which i called when cell change. this macro select images and delete them and insert another image depending on cell value using following code. i have same code for two sheets. Private Sub Worksheet_SelectionChange(ByVal Target As Range) ActiveSheet.Shapes.SelectAll Selection.Delete 'insert image code here. End Sub in one sheet its working perfectly fine and delete all images while in other sheet , it give me runtime error "out of memory" and highlight following line. ActiveSheet.Shapes.SelectAll can any one tell me why this is happening? it works perfectly fine in one and not in other. one other thing i want to tell you is. it was working fine when i gave this macro excel to my client,both sheets were working fine, suddenly after 2 days, he started getting error on one sheet on which he was working lot. dont know why this is happening. can anyone tell me whats reason and how to solved it?

  Read the article

• Runtime Error: "Out of Memory" From Excel Macro

  - by user356180

  I have one macro, which is called when a cell change occurs. This macro selects images, deletes them, and inserts another image depending on a cell value using the following code. I have the same code for two sheets. Private Sub Worksheet_SelectionChange(ByVal Target As Range) ActiveSheet.Shapes.SelectAll Selection.Delete 'insert image code here. End Sub In one sheet, it's working perfectly fine and deletes all images, while in the other sheet, it gives me the runtime error "Out of Memory" and highlights the following line: ActiveSheet.Shapes.SelectAll Can anyone tell me why this is happening? It works perfectly fine in one and not in the other. One other thing I want to tell you is it was working fine when I gave this Excel macro to my client; both sheets were working fine. Suddenly after 2 days, he started getting the error on one sheet on which he was working a lot. I don't know why this is happening. Can anyone tell me what's the reason for this and how I can solve it?

  Read the article

• excel:mysql: rs.Update not working

  - by every_answer_gets_a_point

  i am updating a table using an ODBC connection from excel to mysql unfortunately the only column that gets updated is this one: .Fields("instrument") = "NA" where i am assigning variables to .Fields, it is putting NULL values!! what is going on here? here's the code Option Explicit Dim oConn As ADODB.Connection Private Sub ConnectDB() Set oConn = New ADODB.Connection oConn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & _ "SERVER=localhost;" & _ "DATABASE=employees;" & _ "USER=root;" & _ "PASSWORD=pas;" & _ "Option=3" End Sub Function esc(txt As String) esc = Trim(Replace(txt, "'", "\'")) End Function Private Sub InsertData() Dim dpath, atime, rtime, lcalib, aname, rname, bstate, instrument As String Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset ConnectDB With wsBooks rs.Open "batchinfo", oConn, adOpenKeyset, adLockOptimistic, adCmdTable Worksheets.Item("Report 1").Select dpath = Trim(Range("B2").Text) atime = Trim(Range("B3").Text) rtime = Trim(Range("B4").Text) lcalib = Trim(Range("B5").Text) aname = Trim(Range("B6").Text) rname = Trim(Range("B7").Text) bstate = Trim(Range("B8").Text) ' instrument = GetInstrFromXML(wbBook.FullName) With rs .AddNew ' create a new record ' add values to each field in the record .Fields("datapath") = dpath .Fields("analysistime") = atime .Fields("reporttime") = rtime .Fields("lastcalib") = lcalib .Fields("analystname") = aname .Fields("reportname") = rname .Fields("batchstate") = bstate .Fields("instrument") = "NA" .Update ' stores the new record End With ' get the last id Set rs = oConn.Execute("SELECT @@identity", , adCmdText) 'MsgBox capture_id rs.Close Set rs = Nothing End With End Sub

  Read the article

• A Fully Featured Solution for Data Export

  For those who need to be able to automate the process of exporting databases with maximum speed, reliability and ease of use, FlySpeed Data Export offers the ultimate solution. The software is packed... [Author: William Potter - Computers and Internet - March 29, 2010]

  Read the article

• Database Export Wizard for ASP.net and SQL Server

  A step wizard for ASP.net to export database objects to CSV, TXT, HTML, XML, or SQL....Did you know that DotNetSlackers also publishes .net articles written by top known .net Authors? We already have over 80 articles in several categories including Silverlight. Take a look: here.

  Read the article

< Previous Page | 59 60 61 62 63 64 65 66 67 68 69 70  | Next Page >