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

[lapot]

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

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

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

 

[Dan_W]

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]

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]

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]

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, it seems like an inherent contradiction in terms. I'm sorry - I've never heard of that problem before.

 

[RoryA]

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]

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]

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]

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.