Newest excel Questions

Q&A for professional and enthusiast programmers

Exporting SQL Server stored procedure results to an Excel file

I know you can export SQL Server results to Excel, however is there a way for the procedure to pop up a window and ask the user for a file name and location or does the file name and location have to ...

Handling millions of INSERT operations on Node.js

I have more than 7000 excel files that I need to process and extract to a few MySQL tables. I've built a fully functional Node.js script that works fantastically good except that it takes days to walk ...

VBA UDF to find multi column criteria match using dynamic arrays

I posted a question in regards to finding a match with multi-column criteria. The provided answer works great. But I'm trying to make it a universal solution for my project, in terms of how many ...

Excel Dynamic Formula/Reference Workbook

I'm trying to create a dynamic reference within a formula; meaning the referenced workbook and subsequent sheet name can be changed and the appropriate data loaded. I'm aware that a static formula ...

Copy data from SQL Server to Excel “NULL” instead of blank NUMBERS

I copy data from a SQL Server result set and paste it into an Excel spreadsheet. The NULL values need to appear as blanks in Excel, but the default behavior is to show the word, "NULL". For text ...

Copy rows based on cell value and paste on a new sheet

Check This I need a help. I want to copy whole cell from A sheet name "Components" only if value in Column C is > 0 to a new Sheet name "Load list" Can someone please give me the macro code for this?...

How can I convert a range to a string (VBA)?

What is the best way to convert a range of cells to a string? I have a function that only takes a string as input so I need to convert the range to a string, while retaining as much of the formatting ...

issue after writing workbook with Apache-POI

I've a problem after inserting the data into the sheet of an excel template with the .xlsm extension. The code works. It correctly copies the data in question, but when I try to run a specific macro, ...

Multiple instances of a single Property in a Class Object VBA

Say I create a class called Farm. And it has 3 properties: FarmName as String NumberOfStables as Long HasHorse as Boolean And I start the class Dim SmithFarm as Farm Set SmithFarm = New Farm ...

VBA to concatenate and then increment resulting value based on another value

I am trying to write a macro to loop through a spreadsheet, and on each row, concatenate three values (from different columns) into a single value (integer), and result that in another column on the ...

Easiest way to convert from string to number in nodejs so i can do $avg

What i have is 1000 data of different fields. I realised that i cant get the average in my mongodb if the datas which were imported from excel are string. What is the easiest way to get all my 1000 ...

Count incremental blank cells till next non empty cell (Formulas Only)

My data looks like below. I want to count consecutive blank cells till next non empty cell. My logic is to get output, when A2 contains data then it will return 0 but when A3 is blank then I expect 1 ...

Read string as integers? (Python 2.7)

I'm new to Python and I'm pretty much just learning things as I go. I'm trying to import data from an Excel file into my Python program. I need to be able to do simple calculations with these numbers ...

Formula for getting worksheet names based on pivot table results

I have an excel file with 30 time cards, each on their own worksheet, where the only identifier is the worksheet name (ie the employee name). Each worksheet has a first column of account numbers, ...

Can't open File Dialog Picker in VBA Excel Mac

Set objDialog = Application.FileDialog(msoFileDialogFilePicker) This fails on VBA on my MAC. I have the Microsoft Excel 14.0 Object Library selected in my references. Any ideas? I've searched the ...

vba , Autofill userform from another workbook cell selection

I have a workbook that used to be one with many sheets, I have now split the sheet to different workbooks. The problem I now have is userform population from cell selection. When all the sheets were ...

How to get Hive table output to multiple excel sheets

I am new in Hadoop. I have a table in Hive that have my all required data like this:- Table A. Id description 1. abc 1. Abc2 2. Cdf 2. Cdf2 2. Cdf3 3. Efg Now wanted output from ...

Update Links in for Excel Spreadsheet Using Python

I am running simulations in Python that generate output that need to be directly consumed by a modeler in their excel workbooks. I have generated code that will directly output my data into their ...

Background and Y-axis changed in adding series in Excel

I added new series to existing graph. The graph background changed. In addition the Y-axis values disappeared. How can I add it so it will be same as the source graph? Why did both change? Here is the ...

How to create a dynamic list in VBA?

I have an excel sheet. I want to create a list which will be in 5 cells. For simplicity, lets call the items in the list as (item1, item2, item3, item4, item5). If I select "list1" from a cell-1, the ...

I want to use values that are in macro in java program [on hold]

I want to use value of a function written in macro in my java function.Could you please help me out.

Excel table population, input from different tabs with output selected from max value of column

I have two sheets (Inputs and Spring Balance, Normal), one is a table of input values (dynamic, so the table grows) for the second to calculate. I would like to have the VBA program to sequentially ...

How can I send a range in the body of an email using a macro (mac)? (VBA)

I'm trying to send a range from a spreadsheet in the body of an email. Using excel 2011, OSX El Capitan and mac mail. Using http://www.rondebruin.nl/mac/macmail/macmail1.htm I can send the spreadsheet ...

Excel formula returns #REF when linking to external worksheets and copied

I have a spreadsheet October2016.xls with formulas for external links to other sheets. When I copy the sheets to a different directory, I will get a #REF error. For example, I need to link to Assembly ...

How to change row color base on the values in column 1?

I want to know how to change row color of a number of rows base on the value in column 1. Lets say in A1 to A5 I have the value "100" and A6 to A10 I have the value "150", i want to be able to change ...

Export graph from webform to excel file using ASP.NET C#

Hi I have the codes to generate regression graph on the webform, but I want to download the regression graph into excel as well. How to enhance my codes? Below are my cs codes to generate graph on ...

Normalizing Data on scale of 0 to1 in Excel

I need data in excel to return with a normalized value between 0 and 1. Following another forum, I tried the formula =A1-MIN($A$1:$A$8)/(MAX($A$1:$A$8)-MIN($A$1:$A$8)) which returns with the same ...

Formulas wont update unless I go to another sheet and ask it to calculate even though all set to Auto recalc

And yes, everything is set to automatically recalculate. I change a value on one sheet and several cells return #VALUE until I go to a second sheet that it pulls those values from and hit any key ...

I have an Excel sheet that uses many form checkboxes. Is there a way to deselect all checkboxes at once?

I have an Excel document with 62 sheets. Each sheet has numerous form control checkboxes. Is there a way to clear these checkboxes all at once?

Pivot Table with Page Names as Column Headings

Each of my pages in Excel contains a year of country stats and the page name is the year in question. Each row is a country. Each column is a statistical metric. Each table on each page is named and ...

OLEDBConnection.Open() External table is not in the expected format

I've literally done everything I've searched for on here the past 2 days just to get my .xlsx Excel file to upload into a DataTable and be used as my RadGrid DataSource. Here is my code... private ...

Split text in cells at commas

Let's assume a simple Excel spreadsheet with two columns A and B, where in the B column there are comma separated values. I need a VBA function to split these values each one in new rows starting from ...

Excel Vlookup Fail Does not recognize letters

I'm trying to do a vlookup that looks at different letter ratings (AAA,+AA,AA,AA-,A+,A,A-,etc.) and give that letter rating a numeric value from 0 to 100 that is in an array in another worksheet. But ...

Category Weighting with Total Marks Override

I have a teacher gradebook that uses category weighting (and evaluation weighting within each category). The formula I use to calculate final grade is found in column E here: https://docs.google.com/...

VBA IE.document Click

The HTML code is as followed on a webpage. It's a link that I want to be able to click on from VBA. <a title="View Account Summary" class="unregLink" href="/Standard/AccountSummary/Pages/...

How to replace comma separated values to comma separated ids in the same/individual cell in excel?

I have a table where Products are mapped with ingredients and another table where Ingredients are mapped with IDs like this below : Below is what I need : Here I want to replace the comma separated ...

Row in Excel to array?

I have lots of data in an Excel spreadsheet that I need to import using Python. i need each row to be read as an array so I can call on the first data point in a specified row, the second, the third, ...

Sorting list by date then letter

How can I sort a list by date then letter (preferably without using VBA)? Every time I mess with the sort options it sorts by number first. Do I need to write some kind of sorter? Is there a Google ...

How to write a VBA function whose return value is recognized as a date by Excel?

I wrote the following test function in MS Excel 2002: Function someDate() As Date someDate = Now() End Function Then I use the function in a cell, like this =someDate(). But the value is shown ...

Angular 2 excel export not working

I have created sample project to export data in excel format using JExcelApi lib. Project has Angular 2 frontend and spring mvc(rest api) backend. If I deploy backend to tomcat and make request via ...

in Excel 2016, how do you select or filter rows containing various different words?

Please see picture. The sheet on the left is in stock items and the sheet on the right is all products including dims, weights, etc. I want only in stock items on the sheet on the right(with all the ...

certain elemets parsing from txt file using python

all. I am trying to get distance and time of two different locations. I have about 2500 combinations of locations and so far I could get them using python and google maps distance matrix. The ...

Open excel file for editing in asp.net MVC

I am working on asp.net mvc, my requirements are as follows Upload any excel file(s) Delete any excel file(s) Edit any excel file For this i followed a tutorial and i am able to perform steps 1 ...

Adding an external image as a full cover page for an excel PDF export, and solutions for horrible compressed image files

I've been creating a spreadsheet in excel to collate a number of statistics for a project, with the end goal of automating a report that can exported as a PDF document to share at board meetings etc ...

Excel Network days formula using multiple Holiday conditions

I am working on a vacation dashboard where I want I am trying to calculate the number of working days between two days using networkdays but I have different holidays list for different set of people. ...

Excel 2016 PowerPivot MDX query designer error

When open Excel 2016, go to the Power Pivot tab, click the Mange button to bring up Power Pivot window In Power Pivot, click Get External Data, choose From Other Sources Choose Microsoft Analysis ...

How can I store the row and column numbers of a range in vba, and why am I getting a 438 runtime error?

I'm trying to capture the row and column numbers of a ranger in variables. After a fair amount of googling, it seems I might have to store these as Longs and not Integers, but not sure. Either way I ...

Excel vba to search mail in outlook

I have below code to search a mail with specific subject in Outlook But Below code searched mail only in Inbox. But I want to search mail in all folder and subfolder. Also I wants to search latest ...

Freeze formula in one cell once date is posted in another cell

I want to count the number of days it takes to answer a question and then once the question is answered, the number of days is frozen. For example, B1 is the date the question is received. C1 is the ...

Excel: How to create a list without spaces

In Excel (2013), how do I copy a column list from one column to another, but filtering out blank cells? I have tried to filter the list, but it removes the data I require.
Translating... 0%