Excel VBA function with Recordset (Performance issue)

I have a database in SQL Server that I’m using to feed some financial reports in Excel. I’m using Recordsets through a custom Excel Function that uses arguments from Cells to build the SQL queries. Here is how the code looks: Public Function Test(arg1 As String, arg2 As String, arg3 As Integer, arg4 As Integer, […]

Copied Excel Files losing Web query

I’m using Python and the module openpyxl. I have been able to: CreateFile = openpyxl.load_workbook(filename= ‘OldWorkbook.xlsm’, keep_vba = True) Sheet = CreateFile.active Sheet[‘A1’]=(“What Ever You Want”) CreateFile.save(‘NewWorkbook.xlsm’) But when I open the NewWorkbook the web query I defined in OldWorkbook is not there in NewWorkbook, any suggestions?

Run macro on Excel Graph when I select new data

Like the title says, I want to be able to automatically run my macro whenever I select a series for the graph. I need the last bar to be colored red, and I can do that with my macro, but when I select a new series of data, the red bar is the last bar […]

Excel – Table Issue

I’ve got a number of table on different worksheets that have items sorted into categories which can be found on yet another worksheet. I’ve then created pivot tables of these tables, and would like to combine them, which is where my problem is. To combine them all of the categories must be listed, but sometimes […]

Inserting rows for specific missing values

I know how to insert rows for sequential missing values, but how can I do this for unique values that I store in a range? For example: Range of all needed values New list with missing values 2 2 3 5 5 7 6 15 7 10 15 The code below adds rows in a […]

Code to search a list with a VBA Userform

I’m trying to create a userform in VBA that will search a list in another sheet and display all matching results, is it also possible to have that data displayed by default to then be narrowed down by the search box value? There are three columns in the list it will search, but if it […]

Excel data validation not showing in a list

I have an Excel COM add-in where I apply data validation to some cells. The data validation is configured as a list with values “value1,value2,value3”, which means that when I click on the drop down, I will be seeing 3 rows of values. However, I have 1-2 users who are seeing only one row in […]

Increment with text value

I am making a log file in Excel for some laboratory samples. In this log I record the date, the sample ID, the box where the sample is stored, and the box-position. For example: SampleID Study Date Box Position 12345 SomeStudy date 1 A3 12346 SomeStudy date 1 A4 The positions always start from position […]

How to find the last 8 quarters and thier years from the current quarter and year or a given date

I am trying to find the last 7 quarters from the given quarter and year in MS Excel. For eg. if I have Q1 2016 then I should be able to find Q4 2015 in one cell and Q3 2015 in other cell and so on till 7 last quarters. This should be dynamic i.e […]

Need to get bullets from word tables to excel using interop

So I’ve been trying to learn how to use Interop (and programming in general for that matter). Basically I have these tables in my Word docs that I need to export to Excel. I’ve gotten the basic text to export to Excel, but my tables in Word have a fair amount of formatting, mainly bulleted […]

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