We help IT Professionals succeed at work.

Microsoft Office

70K

Solutions

42K

Contributors

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.

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
Its a petty thing but I cannot get the printer icons to match on a few of the office desktop computers.  See attached for a screen shot of two different Control Panels where the same printer is installed but the icon's don't match.
Any suggestions?

Printer_Icon_Issue.pdf
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
Hello Experts,

I have one docx document with some styles that I want to transfer to my Normal.dotm file.
Do you know if there is a Word VBA procedure that allows me to specify the style that I want to transfer to my Normal.dotm.

Example: SourceFile="C:\Doc" StyleName="CuztomizedStyle" TargetFile="C:\Normal.dotm.

The aim is to have CuztomizedStyle at Normal.dotm.

It would be great if I can specify multiple StyleNames, if not I will used as function and call it based on Styles number to transfer.
Example:
Sub TransferStyle(SourceFile,StyleName,TargetFile)

The final aim is to add it to my personal add-in and used whenever I want.

Thank you for your help.
0
Hi

I want to create PowerApps solutions for customers of mine who have asked for them. Should I get Office 365to create them and do they need Office 365 to run them?
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
I am looking for a web designer who can create a website for a medical office (Primary Care Physician). The site will need to be fully interactive, allowing patients to schedule their own appointment and also cancel them as needed. The site should also allow patients to send direct text messages to the practice via the web portal etc....
Any feedback would be appreciated.

Thanks.
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
I have Office 365 installed on several computers.   All are running Windows 10 Pro 64-bit.   Suddenly, I get a send/receive error on one of them.   I did AVG antivirus, quick repair, then more advanced repair, then uninstalled and reinstalled Office.   Outlook works fine on the other computers.   Still get the error message.
0
Hi,

I have Microsoft Outlook for Office 365 (16.0.12624.20348) 64-bit.

Sometimes, The Microsoft Outlook was disconnected.

When using the earlier version (I am not sure which version), I can just CLICK “One tab .. I am not what is it… maybe “Work of line”… then, it would be connected again to the Microsoft Exchange.

Someone knows how to connect (or which button should I click) to connect again this Microsoft Outlook?

Thanks,
tjie
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
We have several tenants using office 365.  One of these was locked down by an employee who is no longer with the company.   I need to connect to it using PowerShell to make some changes.  

I am using an account which is a global admin and an Exchange admin.  I have tried from my computer and from a computer on the local network and whatever I have tried I get the following error message:


New-PSSession : [outlook.office365.com] Connecting to remote server outlook.office365.com failed with the following error message : Access is denied. For more
information, see the about_Remote_Troubleshooting Help topic.
At line:4 char:12
+ $Session = New-PSSession -ConfigurationName Microsoft.Exchange -Conne ...
+            ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OpenError: (System.Manageme....RemoteRunspace:RemoteRunspace) [New-PSSession], PSRemotingTransportException
    + FullyQualifiedErrorId : AccessDenied,PSSessionOpenFailed
Import-PSSession : Cannot validate argument on parameter 'Session'. The argument is null. Provide a valid value for the argument, and then try running the command again.
At line:5 char:18
+ Import-PSSession $Session -DisableNameChecking
+                  ~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Import-PSSession], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationError,Microsoft.PowerShell.Commands.ImportPSSessionCommand


Here are …
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
Hello experts,

I use add-in for PowerPoint and Excel.
I proceed like this:

For ppt:
Save my add-in (.pptm file) with .ppam extension at %UserProfile%\AppData\Roaming\Microsoft\AddIns and load it through Files=>Option

For Excel:
Save my add-in (.xlsm file) with xlam extension at %UserProfile%\AppData\Roaming\Microsoft\AddIns and load it through Files=>Option

I don't know how to proceed for Word:

I have my add-in  (.docm) but I don't know which extension should I use to save it at: %UserProfile%\AppData\Roaming\Microsoft\AddIns.

Thank you for your help.
0
So, I have Office 2016 with Access.  My app front end has always been around 21 megs.  
Over the past week the fornt end has grown to 213 megs.  I have tried to compact it and it doesn't change.  Whenever it is opened it gets bigger even with compact on close as an option.  

I have never seen this before.  Could this had been affected in a patch for Office. Does anyone know how to fix this?

All my tables are linked to the backend.  I have no local tables in the front end

Thanks
John
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

Microsoft Office

70K

Solutions

42K

Contributors

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.