FAQ Database Discussion Community


Cannot Retrieve Data from Excel File Created using Aspose.Cells

excel,excel-2007,aspose,aspose-cells
I create an Excel file (.xlsx) using the Aspose.Cells library. But I'm not able to read the data (retrieve rows) using OleDb commands after that, until I open the file and save it manually. I'm running something as simple as this one: new OleDbDataAdapter("select * from [Sheet1$]", conn); // etc......

Replacing the existing sheet in excel 2007 using VBA

excel,vba,excel-vba,excel-2007
Sub testmac() ' ' testmac Macro ' ' ActiveWorkbook.Names.Add Name:="items", RefersToR1C1:= _ "=Report!R7C8:R486C11" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _ "ODBC;DSN=Excel Files;DBQ=path ), Array("P;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;")), Destination:= _ Range("$A$1")).QueryTable .CommandText = Array( _ "TRANSFORM first(`Dimension Value Code`)" & Chr(13) & "" & Chr(10) & "SELECT items.`No#`, items.`Description`" & Chr(13) & "" & Chr(10) &...

Code to Change Live Hyperlinks into Text URLs not working

excel-vba,excel-2007
I need to Change Live Hyperlinks into Text URLs I have the following Code but after running it the Hyperlinks are still live I have Internet and networkpaths with hyperlink un-checked thanks Sub RemoveHyperLink() Dim ws As Worksheet Dim Rng As Range, Cell As Range Dim LC As Long, LR...

Excel lookup by text

excel,excel-2010,excel-2007
I am trying to make lookup for excel table using the text in the columns For example if i have A1 | A2 BMW-X6 | 1 BMW-X5 | 1 Mazda-1 | 2 Mazda-2 | 2 Mazda-3 | 2 Mercedes-Benz | 3 Mercedes- cls | 3 I want where there is...

Writing formula into an Excel Range with Option Strict On

vb.net,excel-2007
Is it possible to write formulas across a range in Excel from VB.Net? I'm using a String array to hold a list of formulas that I would like to apply to an Excel range, instead of looping through and writing them one at a time. This line is what I...

VBA Save worksheets with array

vba,excel-vba,excel-2007
I have a code that will save the specified worksheets into a new workbook. The issue I'm having is it ignores a chart I would like to save. If I put "Figure 1" (the name of the chart) to the array list I get an error: "Subscript out of range"....

VBA-get values with known cell address

vba,excel-vba,excel-2007
I am trying to achive the values of a row holding specific text. Say Liquidity Coverage Ratio is the text that is available in B57. I need the value of C57 and D57. I have achieved until retreiving the address of the cell holding text. Kindly help me progressing further....

Two is not Two - Select case condition never matches

excel,vba,excel-formula,excel-2007
I'd like to know if a certain behaviour of my function in VBA is a bug or if there is something wrong with my code. I also would appreciate suggestions if it is a bug, how I could solve the problem. Option Explicit Public Function someFunction(dblWeight As Double) As Integer...

Application.WorksheetFunction.SumIfs with list objects

excel,vba,excel-vba,excel-2007
I have a worksheet function with list objects on it and it does not give the proper value. The output is always 0. Sub volsumif() Application.ScreenUpdating = False Set wb2 = ThisWorkbook Set ws1 = wb2.Sheets("Dist_Summary") 'Set a = wb2.Sheets("SALES").ListObjects("SALES").Range Set ws2 = wb2.Sheets("Dist_Perf Total Brands") Set ws3 = wb2.Sheets("SALES")...

De-Stacking columns in Excel using VBA

excel,vba,excel-vba,excel-2010,excel-2007
I have a dataset in three columns composed of a repeating set of UUIDs in the first column, string responses for each UUID in the second, and a code for each response in the third. I need to break this out into multiple sets of columns, one for each repeating...

Excel conditional formatting based on cell content

excel-formula,excel-2007
I am trying to write a conditional formatting formula for the rule "if the first cell of this row is NOT empty AND the current cell IS empty". Basically I am trying to bring attention when a user has forgotten to enter a value in a cell. The formula I...

Excel - when I click on any cell from the group (set by me), I'd like to highlight all of the 'connected' cells instantly

excel,excel-formula,excel-2007,highlighting,cells
Example of what I want. We have an excel spreadsheet. I want to 'group' i.e. E3, E6, E10 (NOT E4, E5, E7 etc... that's the point) cells so if I click of any of them, all of them will be highlighted. So let's say, I click on E3 so E3,...

Comparing Array versus Table

vba,excel-vba,excel-2007
My aim is to load the Worksheets(ws1...wsn) in the WorkbookA to WorkbookB with specific condition. I have formed an array of worksheets present in WorkbookA. Condition: I have a table(tblList) in WorkbookB which lists down the worksheet names that has to be ignored loading (Say I do not want to...

Convert multiple columns to a single row based on Column 1 & 2 Values Excel

vba,excel-vba,excel-2007
I have an excel sheet A AAA 1 A AAA 2 A AAA 3 A ABC 1 A ABC 2 B AAA 1 B AAA 2 B AAA 3 B ABC 1 B ABC 2 I need it to look like A AAA 1 2 3 A ABC 1 2...

Difference between two date columns formatted as General

excel,excel-formula,excel-2010,excel-2007
I have two columns in Excel, formatted as General - with this data: Column A Column B 11/2/2014 9:12:27 AM 12/3/2014 2:00:00 AM How can I find the difference in hours between them, if they are formatted as "General" and not "Date"? Thank you for any advice, Mark...

R loading multiple excel files and merge

r,loops,merge,excel-2007,lapply
I have many excel files that I need to load and merge into a single data frame. The script below works! However, before merging all files I want to paste each file name in a new column. library(gdata) library(tools) filelist <- list.files(pattern = "*\\.xlsx$") files = lapply(filelist, read.xls, header=TRUE) new...

Excel: Count duplicate cells in a column based on another column.

excel,excel-formula,excel-2007
Sample data: Event Client 400 1 400 1 401 1 402 2 402 2 402 2 400 3 401 3 402 3 403 3 This table represents 1 ticket to an event per row. I am trying to determine how many events a client attended (but I don't care how...

Excel: Sum if the value is numeric and meet a relative criteria

excel-formula,excel-2007,formula
A B C 1 N1 4 3 2 N2 7 Blocked 3 N3 5 5 4 N4 12 0 I want to SUM the total of column "C" if it is numeric if it is less than column B I want it to be something like this, but obviously...

Data Filter Shows Nothing To Filter On

excel,excel-2007,filtering
I have imported a query into Excel, very straight-forward process. The issue I am having is if I click the header row and say filter, if I select any of the filter arrows, their are no values to filter on? What should I change so that I am able to...

Delete the string after the delimiter in excel

excel,excel-formula,excel-2010,excel-2007
I have simple problem. I was analyzing some data and came up with this problem. Below is my value in a colomn: www.mysite.come/api/Customer?id=12333&name=jack www.mysite.come/api/Department?id=52365&name=COP www.mysite.come/api/Customer?id=13333&name=mathew etc I want to filter this data something like this www.mysite.come/api/Customer www.mysite.come/api/Department www.mysite.come/api/Customer Please help me with this....

How do I get specific rows once prioritized from multiple tabs to copy onto the 1st tab/worksheet?

excel,tabs,excel-2007
I have created an excel workbook with 11 tabs/sheets. The first sheet is a combined prioritization preceding the 10 individual department prioritizations each on their own sheet. I used the same column headers on all of the 11 sheets, with column A being "Division Priority". What i'm trying to do...

Strange number-of-character-in-a-cell limitation when programmaticaly (VBA, C#) set array string to a range

excel,vba,excel-vba,excel-2007,excel-addins
I have run into an issue in Excel 2007 which is very similar to a known problem of Excel 2003 described by Microsoft Support here ("You may receive a "Run-time error 1004" error message when you programmatically set a large array string to a range in Excel 2003"). The problem...

How do you lookup data in Excel matching multiple columns (i.e. date & product, date & region)?

excel,excel-vba,excel-formula,excel-2010,excel-2007
http://imgur.com/BYejKRb (please use the sceenshot linked above for reference - this is my first post so I can't post images) As you can see in the picture above, I want to take Table 1 and, using formulas that can auto-populate, turn it into Table 2. I tried using vlookup and...

Creating a “color scale” using vba (avoiding conditional formatting)

excel,vba,excel-vba,excel-2007,excel-2013
I'm looking for a way to apply a color scale to a set of cells via VBA code but not by applying some conditional formatting... I want to apply them as static colors (InteriorColor) I've searched plenty of excel sites, google and stackoverflow and found nothing :( For my situation...

Object required error '424' when iterating over Collection

excel,vba,excel-vba,excel-2010,excel-2007
When I run my function, I get the following error: Run-Time error '424': Object Required And it highlights the following line of code: For Each n In DataSet Here is my full code: '==============================|Find_Occurrences FUNCTION|==============================' ' Given a collection and a search value, return the number of occurrences of the...

VBA- Formatting email Body from excel contents

vba,excel-vba,excel-2007
I have a worksheet with given data, I need to email the data using Micorosft outlook in the required format for a specific date(Say if the date is 05 Jan 2015).Refer below This is how the email should look, I am an amateur developer in formatting the email through VBA....

SIMPLE Delete line from cell?

excel,excel-vba,excel-formula,excel-2010,excel-2007
Hello I'm trying to delete all the rows where in column B the members value is over 1000. I tried this step by step and tried first getting rid of all the unecessary data from B cells and leave just the line with the members. I noticed there are 5...

Changing the active cell

excel,vba,excel-vba,excel-2007
I was looking to create a program that examined a column of text in excel and extracted the first line that contained currency. The currency was in Canadian dollars and is formatted in "C $##.##" with no known upper bound but unlikely to reach 10,000 dollars. I was hoping to...

is there a shortcut to open filter>contains box in excel?

excel,excel-formula,excel-2007
I use filters a lot. Filter > contains option But it is slow because everytime i need to do several clicks. So, my question is, is there a shortcut to open filter, drop down, contains window?...

SSIS error - value violated the schema's constraint for the column

ssis,excel-2007
Exporting from sql server to excel with ssis. export gets around 80% through writing to the excel file then fails with this error. It stops at the same row each time. The value in the field being written is the well within the length parameter and contains no funky characters....

Transform and Pivot in Excel - MS Query

excel-2007,transform,ms-query
I have 4 field in my table : NO. | Specification | Specification Value | Description 1 Color Blue a 1 Size 6" a 2 Color Red b 2 Size 8" b I am trying to Pivot it and its giving me an error. TRANSFORM FIRST(Specification Value) SELECT items.'No.' FROM...

Always Return the Previous Saturday's Date Using NOW Function in MS Excel 2007

excel-2007
I have a workbook that I am using the NOW function to return today's date and then I have another cell to convert to WEEKNUM that I use to MATCH on with a different tab. I need to be able to create a formula to read today's date and always...

I want to create a setup that formats rows based on a specific value

excel,excel-2007,conditional-formatting
I have a running sheet for my expenses. I would like to setup conditional formatting/Macro/ect. that looks in cells of THE CURRENT MONTH, then any cell that has the text "paid" FOR THE CURRENT MONTH it will apply a gray fill for THE ROW that the cell is in. EDIT:...

VBA Resizing 2 Identical Images to the same size (width and height)

excel,vba,excel-vba,excel-2010,excel-2007
Step 1) Open Up Excel, Copy "stackoverflow's logo into sheet: http://blog.stackoverflow.com/wp-content/uploads/stackoverflow-logo-300.png Step 2) Copy and paste that logo twice Step 3) Resize one of the logos manually to some ridiculous size: http://i.imgur.com/87lIB8o.png Now how can that nasty logo be resized via vba to the same size as original logo I...

set password for one sheet of workbook (MS Excel 2007)

excel,excel-vba,excel-2007
I need to set a password for only one sheet in xls file, so when the file is open the sheet protected with password isn't visible, and the other sheets could be seen. I use MS Excel 2007, thank you....

VBA Copy Cells + Formatting without using the clipboard

excel,vba,excel-vba,excel-2010,excel-2007
I'm trying to copy cells from one sheet to another without copying stuff to the clipboard but it must copy the formatting across. Here are the ways I've tried at the moment, any ideas how to accomplish my needs? LastRow = ThisWorkbook.Sheets("Nas").Range("A65536").End(xlUp).Row 'Option 1, works but it's using the clipboard...

Multiply values based on numerous criteria

excel,if-statement,excel-formula,excel-2007
I've been having a lot of trouble lately with this and haven't been able to find a solution yet. Here's my problem: I have two sheets: http://s4.postimg.org/aryzvl13v/Capture.png < image of the two tables I have to find the "sell price" in the first table. In order to do this I...

calculate consecute streak in excel row

excel,excel-formula,excel-2010,excel-2007
I am trying to calculate 2 values. Current Streak and Long Streak. each record is on 1 row and contains a name and values each of those columns has a value from 1 to 200. Example: John Doe 14 16 25 18 40 65 101 85 14 19 18 9...

How to sort row values in excel and display corresponding column names elsewhere?

excel,sorting,excel-formula,excel-2007
Essentially, I have the excel sheet like this - A B C D 16 14 13 12 14 14 13 14 16 14 15 12 14 14 15 14 I want the excel sheet to display this - A B C D 16 14 13 12 A B C D...

Returning just the date portion of =NOW()-(WEEKDAY(NOW(),1)) in Excel 2007

excel-2007
I need to return just the date portion of the following formula: =NOW()-(WEEKDAY(NOW(),1)). Any ideas? I am using this formula to get the current date and then return the previous Saturday's date, but I am also getting the time component and I need to drop that portion.

Missing Microsoft Excel as option in SQL Server data import tool

sql-server-2012,64bit,excel-2007,import-from-excel,windows-server-2012-r2
I'm running SQL Server 2012 on a 64bit Windows Server 2012 R2. I don't have MS Office installed. I now notice that when I start Start->Program Files->Microsoft SQL Server 2012->Import and Export Data (64-bit) the MS Excel file option is missing from the datasource dropdown. How can I get that...

VBA Copy Destination but as values

excel,vba,excel-vba,excel-2007,excel-2013
I'm trying to transfer data from one document to a different document using "Copy Destination" as I want to avoid using the clipboard but I want it to stop taking the formatting with it... Dim Sheet As Worksheet Dim FoundLocationSheet As Boolean Dim n As Long Dim AllSheet As Worksheet...

Get code runtimes via GetTickCount and format it in specific ways

excel,vba,excel-vba,excel-2007,excel-2013
I'm trying to make VBA's GetTickCount work so that I can see the runtime of code, but it doesn't have to be super accurate. The following bit of code works good but I need a few changes and can't work out how to achieve this. #If Win64 Then Public Declare...

Search columns and return number of cells containing “5” against colleague name

excel,excel-formula,excel-2007,formula
What I'm looking for is a formula that will do the following: (I'll be using the first row for this example): Firstly check cells E:J for cells containing a 5. If all cells contain a 5 it will check the person's name and then add this to the box in...

How to call a value of a variable whose name is passed as text to another variable?

excel,vba,variables,excel-2007
I need to call a value of a variable as another variable. E.g. I assign the FirstVariable = "One" and then I asssign the Name as Text to SecondVaribale = "FirstVariable" (Note here it is the "TEXT") So now can I call or assign the SecondVariable to return the value...

Change the date to fiscal year

excel,date,if-statement,excel-2007
I want to write a function in Excel to change the date. The logic is like this: if the month is (Jan, Feb or March) the result show me one year past (-1 year) and if the month is (April to -December) the result show the current year (which year...

Shuffle values to a new row two cells at a time

excel,excel-vba,excel-formula,excel-2007
To explain it in the easiest way possible: | 1 | 2 | 3 | 4 | | 5 | 6 | 7 | 8 | ... needs to look like: | 1 | 2 | | 3 | 4 | | 5 | 6 | | 7 | 8...

VBA - test if a value is a valid selection for a PivotField

excel-vba,excel-2007,pivot-table
For a pivot table (pt1) on Sheet1, I use VBA to change the value of a filter field (filterfield) using the code below. Let's say values for field can be A, B or C Sheets("Sheet1").PivotTables("pt1").PivotFields("filterfield").CurrentPage = "A" Occasionally, and let's say randomonly for the purposes of this question, A, B...