How to make a loop clear cache every 100,000 loops?

I have taken a code posted here somewhere and inserted an If statement that basically makes the sheet save every 10,000 loops. I read an advice somewhere that this stops excel from crashing completely. I am basically trying to match columns and finding duplicates by highlighting them/copying. The issues is both columns that I am […]

Fetch maximum number of columns for a specific set of rows

If I want to fetch the max number of columns for a set of rows in Excel, using Visual Studio, how would this be done? Here is my current attempt which yields the maximum number of columns across the whole sheet instead of the specified rows. here are my attempts at this. All the necessary […]

C# MVC Amount of Rows in Excel?

I have a piece of code below – which is looping through an Excel workbook adding the data to an SQL Database – However the forloop doesn’t stop when the data runs out. int rowCount = worksheet.Rows.Count -1; for (int i = 3; i < rowCount; i++ ) { Spreadsheet spreadsheetToSave = new Spreadsheet(); Estimate […]

Excel 2013 OLAP Tools DAX Tabular Data Connection

I have an Excel 2013 pivot table on a tabular model data connection. I essentially want to slice the facttable.sales field by a calculated field on a product dimension that doesn’t exist in the model. Using my TSQL background to explain the field I want, the only way I know how: Case when productdimension.category = […]

How to Automate the process of getting data from BigQuery into excel using BigQuery excel Connector?

I need help for automating my process for getting data into excel from BigQuery. I used this tutorial https://www.youtube.com/watch?v=udZNmb3QFQI and successfully got the data from BigQuery. Now for the automation process I recorded a macro in excel and got the code generated as follows: Sub Macro4() With ActiveSheet.QueryTables.Add(Connection:= _ “URL;https://bigquery-connector.appspot.com/data”, Destination:=Range(“$C$8”) _ ) .Name = […]

Excel Formula finding x and y percentage

I am struggling to figure out how to get a formula to figure out how many and what percentage of tickets we sold for a show. We sold tickets at $20 and $30. 764 tickets were sold for a total of $19,740. How do i write a formula to find the percentage a number of […]

Web Scraping Using Excel VBA – Help Needed

As a relatively new VBA coder, I’ve been given the task of web scraping pertinent ticketing information from a travel booking site (rather challenging 1st task). I have piecemealed some code together and have been able to get to the point of actually opening the target URL and searching for defined parameters. I am now […]

Selenium Webdriver: Getting Nullpointerexception while reading an .xls file and unable to read it

I am trying to get data from the .xls file but when it tries to read sheet from .xls file, it throws nullpointerexception. Exception in thread “main” java.lang.NullPointerException at lib.ExcelDataConfig.getData(ExcelDataConfig.java:31) at data.ReadData.main(ReadData.java:15) Following is my code: 1. ReadData.java public class ReadData { /** * @param args */ public static void main(String[] args) { String path=System.getProperty(“user.dir”); […]

Excel cannot insert the sheets into the destination workbook, because it contains fewer rows and

I am trying to merge 12 excel files (each having single excel-sheet) into single consolidated excel file that will have 12 excel-sheets using Microsoft.Office.Interop.Excel liabrary and following error: “Excel cannot insert the sheets into the destination workbook, because it contains fewer rows and columns than the source workbook. To move or copy the data to […]

vbscript check if file is open

i am trying to make a script that would be run every 5 minutes by windows task scheduler, the script is pretty much done, but with one flaw. Script runs the macro inside a exel file on local server storage but i dont know how to check if someonne has the file opened? it should […]

MS Excel Spreadsheet is the best Office Software, Excel VBA and Excel Formulas make Spreadsheet work faster.