We help IT Professionals succeed at work.

Microsoft Excel

137K

Solutions

38K

Contributors

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

I am running the following VBA code in Excel 2016.  Every time it runs, the results are populated on a new worksheet.  I want this code to clear anything on a single worksheet named ProjectResults and the results should always go to this same ProjectResults worksheet.

Option Explicit

 

Dim conn As ADODB.Connection

Dim rst As ADODB.Recordset

 

Sub Connect_To_SQLServer(ByVal Server_Name As String, ByVal Database_Name As String, ByVal SQL_Statement As String)

Dim strConn As String

Dim wsReport As Worksheet

Dim col As Integer

 

strConn = "Provider=SQLOLEDB;"

strConn = strConn & "Server=" & Server_Name & ";"

strConn = strConn & "Database=" & Database_Name & ";"

strConn = strConn & "Trusted_Connection=yes;"

 

Set conn = New ADODB.Connection

With conn

        .Open ConnectionString:=strConn

        .CursorLocation = adUseClient

End With

 

Set rst = New ADODB.Recordset

With rst

        .ActiveConnection = conn

        .Open Source:=SQL_Statement

       

End With

 

Set wsReport = ThisWorkbook.Worksheets.Add

With wsReport

               

        For col = 0 To rst.Fields.Count - 1

                .Cells(1, col + 1).Value = rst.Fields(col).Name

        Next col

       

        .Range("A2").CopyFromRecordset rst

       

End With

 

Set wsReport = Nothing

 

Call Close_Connections

 

End Sub

 

Private Sub Close_Connections()

 

If rst.State <> 0 Then rst.Close

If conn.State <> 0 Then conn.Close

 

'// Release Memory

Set rst = Nothing

Open in new window

0
I would like to avoid having to create duplicate copies of a custom ribbon control that I want to appear in multiple places in the Office UI and instead create it once and then use the qualified control id mechanism instead. I know how to use idQ to create a shared group across multiple add-ins but can it be used to share a control within the same add-in?

The example below has a custom namespace defined as mns. I then create a group containing the first instance of my control, a button with id ButtonTest and a second instance using idQ but it doesn't work.

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" xmlns:mns="MyNameSpace">
    <ribbon>
        <tabs>
            <tab id="TabTest" label="Test">
                <group id="GroupTest1" label="Test 1">
 			<button id="ButtonTest" label="Button 1" onAction="OnAction"/>
                </group>
                <group id="GroupTest2" label="Test 2">
 			<button idQ="mns:ButtonTest"/>
                </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>

Open in new window


Is it possible to use idQ/xmlns in this way to duplicate copies of controls in the same customUI definition?
0
Excel worksheet as a board game.
Problem: The worksheet is much larger than the screen size.
Objective: to show a live inset of the total chart area on the screen at all times.
Example file shows inset as pic.
EE-YachtRacingStKilda2020flagsrevis.xlsm
0
Excel worksheet has been set up as board game.
Objective:  Move the 43 inserted pics (where bottom pic is at  H1 to M7) to a stack at H1 to M7 and allow click on top pic to place it at bottom of stack.
The pics are named flag1.png to flag43.png.
EE-YachtRacingStKilda2020flags.xlsm
0
I have an excel file (xlsx) where I want the to`p row to repeat at the top of each printed page - normally thats not a big issue, but on this the data is exported from Access into this excel file, and the appears to clear that particular setting. I have tried to put the Access query result in a separate sheet, but with the same result. It looks like the area defined gets cleared when one of the sheets gets updated.

I could just do this in code in the excel file, on opening, but would prefer not to have an xlsm file. Any ideas on how to circumvent this annoying "feature" :) ?
0
In the following example, which is manually referenced and returns the correct data, Column E contains these formulas:
  • Rows 2 - 7: =SourceData!$A1.
  • Rows 8 - 13: =SourceData!$A2.
  • Rows 14 - 19: =SourceData!$A3
and so on...

The Column E data is used to drive VLookup formulas in Column G.
ReferencingHelp.PNG
When I try to use the auto-fill dot in the lower right of a group of selected cells to fill down, I get the following incorrect references:
  • Rows 2 - 7: =SourceData!$A1.
  • Rows 8 - 13: =SourceData!$A7.
  • Rows 14 - 19: =SourceData!$A13


In this particular case, I need to fill-down over 2000 of these groups (12,000+ total rows), so manually adjusting the Column E references is not a good option.
0
Folks,
I am using Excel 2016 in PivotTable. I created my PivotTable through Power Pivot. None of my data tables are OLAP type. In following a suggestion to "Save Source Data with File" that option is greyed out and I do not understand why:
Save Source data
0
Excel worksheet is a board game on screen
Objective 1 is to move all boat pics back to the original position using a Reset Game button. This will need a warning window so that it is not reset by accident.

The boat pics that are scattered across the board need to be re positioned back in the spaces near the boat pics on the dark blue square.

Objective 2 Protect the sheet while allowing the boat pics to be moved, the Roll dice to be clicked, the cards to be licked and the  Reset Game button to be clicked.
ee-example---YachtRacingStKilda2020.xlsm
0
Background:
I tried this at home and at work, same result, this formula just shows up as text (2nd row)
=IF(F4="",0,1)
Excel formula not working
When typing the formula, Excel gives the hint info for the formula but it just comes back as text (2nd row)

The problem is with the file itself, which is exported from Qualtrics. All fields are text, even if blank. So if I COUNTA 200 rows, 100 of which appear blank, the result is "200"

If I insert a column in the middle of the file, it's still all text, the formula doesn't work

If I go beyond the last column of data, the formula works!

NOW FOR MY QUESTIONS.
1. How can I convert a column to be "clean", such that the formula works?
2. Why does it export this way (from Qualtrics)?

This would be easier than going to beyond the last column, copy back, etc.

I do have a workable solution, but would like to understand.

It's a big thing because I'm always downloading from Qualtrics and then doing data analysis in Excel.
0
Excel worksheet is being used for an on screen board game.
Has a set of cards with instructions that have been inserted and are arrayed.
Objective. Cards need to be stacked in top left corner. And the top card placed at the bottom of the stack when clicked.
ee-example---YachtRacingStKilda2020.xlsm
0
What 'NON array' formula do I need to validate all cells in a range are the same. (Null, space and any value can go in this range.)

Example1: Formula should say: "Values the same"

A1:
B1: 20
C1:
D1:
E1: 20
F1:

Example2: Formula should say: "Values not the same"

A1:
B1: 20
C1:
D1:
E1: 20
F1: 5
0
Entering this formula in column F
=IF(AND(SEARCH("reciprocal",C1361),E1361=0),D1361,E1361)
returns the desired results when the row contains "reciprocal" in column C.
But returns #VALUE! in rows that do not contain "reciprocal" in Column C.
What needs to be changed, or added to get the value in E1361 into F1361 when there is no "reciprocal" in column C?
0
How sort date and time in excel.

I have column where dates like below..

ColumnA
10-11-2009 16:10.19
10-11-2009  01:10:19
10-11-2009  19:12:14

I want output from newest to oldest, live below

10-11-2009 19:12:14
10-11-2009 16:10:19
10-11-2009 01:10:19

Can advice what is the correct way to sort in excel?
0
Excel worksheet has 4 columns and 22 rows with special size cells.
Each cell contains different content.
Objective is to be able to run a macro that makes a jpg file from each cell.
EE-Sailing-Instructions---Game.xls
0
Have a list of numbers (column A) and want to cycle through the list and add the number (from column A) against the number of rows down the worksheet that appear in column B using VBA... any ideas?

In the attached file i've described what the output should be for the macro in VBA.

Sheet1 is the setup of how many records should be listed against each
Sheet2 is the output with how it should look like at the end.

Sample.xlsm
0
QoQ.xlsx

I have some formulas comparing entire quarters (every 13 weeks) of a year to entire quarters of the previous year, but how can I compare the mid points (weeks) of the same? So week 14-16 of 2020 vs week 14-16 of 2019, and then later week 14-17 of 2020 to week 14-17 of 2019.  And after week 26, start from week 27 (week 27-28 vs week 27-18) etc....
0
Hello experts,

I am looking for a procedure to cover the following need:

Remove text of selected objects.

The idea is to have a procedure for Excel, Word and PowerPoint. If you have questions, please contact me.

Thank you for your help.
0
how to capitalize the first letter of a word in an excel cell.

the formula proper() is not good. it uncapitlizes abbreviations. so i need something that just literally only takes first character and turns it into a capital letter
0
i need to do a vlookup on 2 columns

the data needs to come from sheet 2 into sheet1/

i attached the file. i did the first row to show you what i need.
vlookup-test.xlsx
0
Hi

What Excel VBA code would I use to check if a column exists. For instance "ÄB" would return true
and "78A" would return false
Thanks
0
I need to extract all instances of numbers in a given string. I have posted a few sample strings in the attached excel file with the expected results for the first string next to a command button.

Can someone provide VBA code which will extract the numbers as listed and also provide the location of each number within the string.
NumericExtractor.xlsm
0
Hello,

I just posted a question but the solution I got does work work 100% so I am re-posting this.

********************************************************************************************************************************

I have the attached spreadsheet which I want to find the OS of each computer in column A using column D and column E.  

For example, computer 1 in column A appears in column D, I want the OS in column E being copied to column B.  

Please advise a formula I can use in column B.  

Please note that I have about 3000 rows in the spreadsheet.  

Many thanks.
OS-searchup.xlsx
0
Hello,

I have the attached spreadsheet which I want to find the OS of each computer in column A using column D and column E.  

For example, computer 1 in column A appears in column D, I want the OS in column E being copied to column B.  

Please advise a formula I can use in column B.  

Many thanks.
OS-searchup.xlsx
0
I have a Powershell script that does a query on an SQL Server table and exports the data to a CSV file.  While the Powershell script is exporting, I need to update one value for each record from a specific field.  For example, if it has RTG-11111 in the "ID" column, I need to replace the "RTG" part with three random letters.  Do you have any suggestions?

Thanks!
-Christian
0
Hi

What Excel VBA code would use to find the 4th instance of the word "CLOSING" using the FIND function and return the row address?
The instance changes so I need to sometimes find the 5th or 6th instance.

Thanks
0