Macro missing Microsoft Windows Common Controls-2 6.0 (sp4) Excel 365

lapot

New Member
Joined
Jul 25, 2014
Messages
30
Hi

I have an excel macro which is running perfectly on Microsoft Office Standard 2016 but when our IT department updated Excel to Office 365 then my macro started giving "Missing: Microsoft Windows Common Controls-2 6.0 (sp4)" error,

This is the line causing error
VBA Code:
TempFile = Environ("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

What is the best way to fix the bellow full code code and stop getting this error on Office 365? My IT wouldn't add "Windows Common Controls-2 6.0" on to system.

Here is the full code

Code:
Function RangetoHTML(rng As Range)
   ' Changed by Ron de Bruin 28-Oct-2006
   ' Working in Office 2000-2013
   Dim fso          As Object
   Dim ts           As Object
   Dim TempFile     As String
   Dim TempWB       As Workbook

   TempFile = Environ("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
   'Copy the range and create a new workbook to past the data in
   rng.Copy
   Set TempWB = Workbooks.Add(1)
   With TempWB.Sheets(1)
      .Cells(1).PasteSpecial Paste:=8
      .Cells(1).PasteSpecial xlPasteValues, , False, False
      .Cells(1).PasteSpecial xlPasteFormats, , False, False
      .Cells(1).Select
      Application.CutCopyMode = False
      On Error Resume Next
      .DrawingObjects.Visible = True
      .DrawingObjects.Delete

      On Error GoTo 0
   End With

   'Publish the sheet to a htm file
   With TempWB.PublishObjects.Add( _
        SourceType:=xlSourceRange, _
        Filename:=TempFile, _
        Sheet:=TempWB.Sheets(1).Name, _
        Source:=TempWB.Sheets(1).UsedRange.Address, _
        HtmlType:=xlHtmlStatic)
      .Publish (True)
   End With
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Dan_W

Well-known Member
Joined
Jul 11, 2018
Messages
1,451
Office Version
  1. 365
Platform
  1. Windows
I'm sure the code is fine. What you need to do is remove the reference to the missing control from the References settings. When you select the Tools menu option from the VBA Editor window, then select References, you'll that there is one reference there prefaced with MISSING in all caps. Deselect that one, click ok, and then the code above should work.

I suspect what has happened is that your company has upgraded Office from the 32bit version to the 64bit version. Some of your other existing controls now may no longer work either.
 

lapot

New Member
Joined
Jul 25, 2014
Messages
30
I'm sure the code is fine. What you need to do is remove the reference to the missing control from the References settings. When you select the Tools menu option from the VBA Editor window, then select References, you'll that there is one reference there prefaced with MISSING in all caps. Deselect that one, click ok, and then the code above should work.

I suspect what has happened is that your company has upgraded Office from the 32bit version to the 64bit version. Some of your other existing controls now may no longer work either.


Hi thanks but I cant remove it , I get " Cant remove control or reference ; in use" message
 

Dan_W

Well-known Member
Joined
Jul 11, 2018
Messages
1,451
Office Version
  1. 365
Platform
  1. Windows
Just to clarify. You say you're getting an error message reporting that the control is missing, but when you go to deselect it from your project references, it says it's in use?
 

lapot

New Member
Joined
Jul 25, 2014
Messages
30
Just to clarify. You say you're getting an error message reporting that the control is missing, but when you go to deselect it from your project references, it says it's in use?
that is correct, when I try to deselect missing control under tools References then " Cant remove control or reference ; in use" message
 

Dan_W

Well-known Member
Joined
Jul 11, 2018
Messages
1,451
Office Version
  1. 365
Platform
  1. Windows
Well, it seems like an inherent contradiction in terms. I'm sorry - I've never heard of that problem before.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
39,745
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
It sounds like your file is using one or more of the controls - perhaps on a userform? If so, you'll need to delete it.
 

amsaini15

New Member
Joined
Aug 31, 2014
Messages
14
I have exactly same issue with my new laptop from work with Office 365 excel. I have lot of code in my macro. If I remove all controls, I will lose the functionality provided by those Macros.
Isn't there compatible library control in replacement to - Missing: Microsoft Windows Common Controls-2 6.0 (sp4)? Thanks
 

Dan_W

Well-known Member
Joined
Jul 11, 2018
Messages
1,451
Office Version
  1. 365
Platform
  1. Windows
When you select the MISSING item in the references window, does it show the path of the where it expects the file to be? Could you possibly provide a screen capture of the references window?
 

amsaini15

New Member
Joined
Aug 31, 2014
Messages
14
Thanks Dan. I have tried unchecking MISSING: Microsoft .. item but I get error "Cant remove control or reference; in use". While I do see path and can check mscomct2.ocx file is missing in Office 365, I cannot simply add the file as it is a corporate laptop.
 

Attachments

  • 2022-09-23_130514.png
    2022-09-23_130514.png
    12.2 KB · Views: 9

Forum statistics

Threads
1,181,182
Messages
5,928,581
Members
436,608
Latest member
nevers0220

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top