To query an excel sheet via SQL, I used to use either: Dim excelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strPath + ";Extended Properties=""Excel 8.0;IMEX=1;HDR=YES;""" or Dim excelConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " + strPath + ";Extended Properties=""Ex...
In Excel 2007 it is possible to scale a pictureLink object (created with the Camera Tool) using the following VBA code. With ActiveWorkbook.Sheets(sht).Pictures(name) .ShapeRange.ScaleWidth scaleValue, msoTrue .ShapeRange.ScaleHeight scaleValue, msoTrue .top = top .left = left End With This code places the picture corr...
I have a simple class library that I use in Excel. Here is a simplification of my class... using System; using System.Runtime.InteropServices; namespace SimpleLibrary { [ComVisible(true)] public interface ISixGenerator { int Six(); } public class SixGenerator : ISixGenerator { public int Six() { return 6; } } } I...
Hi, I was playing with powerpivot to directly load 3 million rows from a sql database and performance is suprisingly good. I tried generating a simple oData service by using vs2010 and silverlight RIA services and access that from powerpivot. Which works with small numbers of rows but blows up on the server if a single method tries to ...
I've just upgraded to Excel 2010 (it was free as I bought 2009 recently). I now don't have another version of excel to test it on but I think I've discoved a SUMIF bug. Now I know the chances of me discovering a bug in excel are almost zero, so can someone tell me what I'm doing wrong, or alternatively tell me what their version of Excel...
Hi Guys, I have this macro Sub Search() Range("H2").Select ActiveCell.FormulaR1C1 = "=IF(ISNUMBER(SEARCH(R16C6,RC[4])),RC[2],"""")" ' Edit RXX value Range("H2").Select Selection.AutoFill Destination:=Range("H2:H385") Range("H2:H385").Select Range("G16").Select ' Edit GXX value ActiveCell.FormulaR1C1 = "=SpecialConcatenate(C[1])" Range...
Hi all, I have a third-party legacy software that uses Automation to control MS Excel (using MFC/C++). The code is straightforward: _ApplicationPtr xl; HRESULT hr = xl.CreateInstance("Excel.Application"); This worked perfectly since Excel 2000, 2002 (XP), 2003 and 2007; however, after installing Office 2010, the CreateInstance call ...
Hi, I am wanting to display an excel 2010 document in an asp.net web application. This document uses the new slicer functionality which I want to be available to the user. I know that excel 2010 docs can be used within sharepoint by inserting a web part which uses exceservices. But can it be achieved without sharepoint? Or can it be a...
Ok so i want to build an Excel plugin (custom task pane) that'll act on the selected range in the active worksheet. Can't find any examples or documentation on how. ...
In an Excel 2010 document with more than one page of records, where each row includes a sparkline, the sparklines do not print or appear in print preview unless one first scrolls through the entire document to view all lines. In my application that produces a series of Excel reports automatically, this is a very visible issue. Can anyo...
Hi, I have an Excel 2010 pivot table that has categories and a count measure as the data. Those categories then have a date dimension nested underneath, filtered to show only the last two months. When I sort the categories, I am sorting them by the total of the count measure across both June and July, in descending order. Can anyone ...
I like the Search And Replace Dialog of Excel 2010. I can enter data, see it replace in the grid behind AND THEN I can edit the data in grid without the Search/Replace Window disappearing/going into background. The SearchAndReplace Window is not a Model dialog. And it also has not settings like "stay at Top" = true because switching to ...
Microsoft has recently broken our longtime (and officially recommended by them) code to read the version of Excel and its current omacro security level. What used to work: // Get the program associated with workbooks, e.g. "C:\Program Files\...\Excel.exe" SHELLAPI.FindExecutable( 'OurWorkbook.xls', ...) // Get the version of the .exe...
I am trying to write some VBA code in Excel 2010 that would consume a web service. I am unable to find any related resources on the internet. Can someone please tell me how to do this. ...
I'm using VBA in Excel 2010 to make a simple birthday reminder program. I have a column of dates (Column D). How do I check if a cell is within one week of today? I'm not sure what the VBA syntax for the conditional statement would be: For i = 2 To MAX_ROW cell = ActiveSheet.Cells(i, 4) If (Date(cell) - Date(Now()) <= 7) Then ...
CSV file: #3GMACRO,,,,,,,,,,,,,, ,,,,,,,,,,,,,, IMSI,IMEI,Date,Time,UMTS MACRO-UARFCNDL,UMTS MACRO-PrimaryScramblingCode,UMTS MACRO-CPICHTxPower,UMTS MACRO-PLMNCellId,UMTS MACRO- RNCId,UMTS MACRO-MCC,UMTS MACRO-MNC,UMTS MACRO - LAC,UMTS MACRO - RAC,UMTS MACRO - MaxUETxPower,UMTS MACRO - MeasuredRSCP 2.6275E+14,3.57539E+14,20100107,16000...
Hi, How can I set or define a default value or selection in excel 2010 slicers? Say for example, if the slicer contains the list of all dates of date dimension, I want the last date of the date dimension get selected by default. If it is possible, please advice. ...
Hi, I am running excel 2010, i have a xls file streaming live price into a cell, how can i create a simple line graphic chart drawing this the date coming ? help would be appreciated. Thanks ...
How can I have multiple Pivot Table and Charts from the same source and use separate grouping and filtering for each one? I have two charts: one I want to show Weekly data and the other should show Monthly data. Whenever I change one, it updates the other. ...
I am connecting to an Analysis Services cube from an Excel Services spreadsheet. SharePoint and SQL Server are configured on separate servers. Am using Excel 2010 / SharePoint 2010 / SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64). Refreshing all connections (or clicking an item in the slicer) throws an error: "An error occurred during ...