SSIS Script task to delete 2nd row of xls (excel)

When exporting data from DB to excel all of my data became string(text) so I created excel(xls) with header and dummy row (2nd row) to follow the format of my second row when exporting the data. My problem now is how to delete the second row or the dummy row after exporting the date in […]

Converting text document into Excel Document

I have a text document full of internet speed tests like this from multiple days of recording speed tests: Download: 173.33 Mbit/s Upload: 194.69 Mbit/s Ping: 3.154 ms Download: 162.56 Mbit/s Upload: 192.74 Mbit/s Ping: 3.05 ms Download: 164.97 Mbit/s Upload: 148.16 Mbit/s Ping: 3.194 ms Download: 174.63 Mbit/s Upload: 155.12 Mbit/s Ping: 3.004 ms […]

Mystery Excel semicolon mode for data validation, stops recognizeing standard comma for unknown reason

In Excel VBA how can I set cell validation so that it works in all locales? This code works in my locale, semicolon separated Dim mySheet As Worksheet Set mySheet = ThisWorkbook.Worksheets(“Sheet 1”) Dim myRange As Range Dim validValues as String validValues = “a;b;c” Set myRange = mySheet.Cells(2, 2) myRange.Validation.Add _ Type:=xlValidateList, _ AlertStyle:=xlValidAlertStop, _ […]

Protecting Macro Enabled worksheet in a workbook

I have a 4 sheet workbook of employee over time. The first 3 sheets are where the data is added by others. The 4th sheet is where the data is added up. On this 4th sheet I have enabled an auto sort macro to constantly reorder the data I have on several different tables. Id […]

Excel XML dropdown list

So I have a XML Map and a form in Excel which i want to link to the XML map and then extact a XML file with the data that will be upload into a website . The thing is I also have some dropdown and checklists that i want to link. Is Excel capable […]

Excel Multicolumn combobox textcolumn search

I got a 3 column combobox on a userform. Combobox is filled from sql query by Do While Not rst.EOF userform.combobox1.AddItem userform.combobox1.List(i, 0) = rst.Fields(“some field1”).Value userform.combobox1.List(i, 1) = rst.Fields(“some field2”) userform.combobox1.List(i, 2) = rst.Fields(“Some field3”).Value i = i + 1 rst.MoveNext Loop Data in filled combobox is like this col1 col2 col3 1 text […]

Textbox.value as a variable

I need to copy an input in a textbox and transform this in a variable that will be used as a range. With the code described below i’m facing error 91 Object variable or With block variable not set. If you have a better way to do this, please let me know. Private Sub CommandButton1_Click() […]

Excel VBA ODBC Type Mismatch in expression

I have 2 excel spreadsheets: GUI.xlsm Database.xlsm Database.xlsm is used as a database and GUI.xslm contains forms etc and queries Database.xlsm using ADO/SQL to get data. I am executing the following SQL from GUI.xlsm to query the tables in Database.xlsm: SELECT * FROM (([Repair_Items$] ri INNER JOIN [RefData_Job_Groups$] jg ON ri.RI_JG_ID = jg.JG_ID) INNER JOIN […]

How to skip particular columns in apache.poi.xssf

I am using the below code in scala to convert xlsx file to csv. I can able read all the columns and rows, And i can able to convert the same in CSV. Here is the one question for me, While reading the column i don’t want write some of the columns in CSV(Example i […]

C# Conditional Formatting Pivot Table loses formula

I’m having problem with a Conditional Formatting with Pivot Table. I get the range of the pivot table, my code display the Traffic Light icons, but if i show the pivot table details (expand or collapse) in both ways, the icons dissapear. I saw that the Conditional formula changes when I do that, but i […]

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