none
From Word to Excel - VBA Global Worksheet.cell method not working any more. RRS feed

  • Question

  • This code worked for along time and stopped working just recently.  The worksheet.cells method is no longer there.  How do I get around this?

    Module 1 - MSExcel Calls

    Option Explicit

    Option Private Module

    Public fd As Office.FileDialog

    Public appXL As Excel.Application
    Public xlWB As Excel.Workbook
    Public xlWS As Excel.Worksheet
    Public INDEX_WS As Excel.Worksheet

    Module 2 - uses public references from Module 1

    Call LaunchExcel
    Set xlWB = appXL.Workbooks.Add
    For Each oSwitch In colSwitches
        xlWB.Worksheets.Add After:=xlWB.Worksheets(xlWB.Worksheets.Count)
        Set xlWS = xlWB.Worksheets(xlWB.Worksheets.Count)
        xlWS.Name = oSwitch.SwitchTag & " - " & oSwitch.IP
       
        xlWS.Cells(1, 1).Value = "Switch Tag:"  - this code now throws a compile error "Method or Data member not found." 

    When I key in "xlWS." in the VBA editor and get the dropdown, "cells" is no longer in the list.

    Tuesday, December 3, 2019 3:25 PM

Answers

  • I worked with Microsoft support yesterday.

    For some reason "Option Private Module" where the excel objects (workbook, worksheet, range) were declared was a problem.  Modules referencing public xlWS had the problem.

    Option Explicit
    Option Private Module

    Public fd As Office.FileDialog

    Public appXL As Excel.Application
    Public xlWB As Excel.Workbook
    Public xlWS As Excel.Worksheet
    Public INDEX_WS As Excel.Worksheet

    The first remedy found was to comment out "Option Private Module", run the macros then re-enable "Option Private module.

    'Option Private Module

    (then run the macros, then remove the ' and save)

    Option Private Module

    The second remedy is exporting all of the modules, class modules and forms and then re-importing them into a completely new word document/template.

    • Marked as answer by Denis Backer Friday, January 3, 2020 2:43 PM
    Friday, January 3, 2020 2:43 PM

All replies

  • This is NOT running with excel Product Version 16.0.11929.20396

    It does run on a co-worker's computer that is running Excel Product Version 16.0.11328.20438

    Tuesday, December 3, 2019 4:58 PM
  • Hi,

    Can "xlWS.Range("A1").Value" be available?

    Regards,

    Ashidacchi -- http://hokusosha.com

    Tuesday, December 3, 2019 11:31 PM
  • Hi,

    Thank you for the suggestion.

    When I key in "xlWS." in the VBA editor and get the dropdown, "Range" is not in the list.

    Wednesday, December 4, 2019 2:53 PM
  • Hi Denis,

    According to your code, "xlWS" is defined as an Excel Worksheet.
    So, I suppose you could type .Range("A1"), instead of selecting from DropDown.
    Can't you do it?

    When you put your VBA code in your post, please use Insert Colde Block. That will help us to read your code.
        

    Regards,

    Ashidacchi -- http://hokusosha.com



    • Edited by Ashidacchi Thursday, December 5, 2019 2:58 AM
    Thursday, December 5, 2019 2:57 AM
  • Thank you for your help.

    The code is included as requested.  However, VBA was not in the dropdown list.

    The top "Option Explicit" is in a module called  "MSExcel_Calls" module and declares the public objects.

    The second "Option Explicit" is in a module called "aMain" that uses the declared objects.

    This code has worked for months and just recently stopped working.  This code still works on one of my co-workers computers.

    I posted the excel product versions earlier. This is NOT running with excel Product Version 16.0.11929.20396. It does run on a co-worker's computer that is running Excel Product Version 16.0.11328.20438.

    the line  xlWS.Cells(1, 1).Value = "Switch Tag:" is generating the compile error.

    Would it be helpful to post the product versions of Word and the Tools...Reference objects ?

    Again, Thank you for your help.

    Option Explicit
    Option Private Module
    
    Public fd As Office.FileDialog
    
    Public appXL As Excel.Application
    Public xlWB As Excel.Workbook
    Public xlWS As Excel.Worksheet
    Public INDEX_WS As Excel.Worksheet
    
    …
    
    
    
    Option Explicit
    Public strPath As String
    Public xlRange As Excel.Range
    Public sParams() As String
    
    ….
    Call LaunchExcel
    Set xlWB = appXL.Workbooks.Add
    For Each oSwitch In colSwitches
        xlWB.Worksheets.Add After:=xlWB.Worksheets(xlWB.Worksheets.Count)
        Set xlWS = xlWB.Worksheets(xlWB.Worksheets.Count)
        xlWS.Name = oSwitch.SwitchTag & " - " & oSwitch.IP
        
        xlWS.Cells(1, 1).Value = "Switch Tag:"
        xlWS.Cells(1, 2).Value = oSwitch.SwitchTag





    Thursday, December 5, 2019 4:29 PM
  • Hi Denis Backer,

    I'm afraid the provided code is insufficient to compile and execute.
    And I could not reproduce the error at 
    xlWS.Cells(1, 1).Value = "Switch Tag:"

    Could you share your file via cloud storage such as OneDrive, Dropbox, etc?
    (Remember to edit/modify your vital/private data before sharing.)

    My environment:
      Windows 10 version 1909 (build 18363.476) Pro
      Office Pro Plus (Word version 1911, build 12228.20332 Click-to-Run)

    Regards,

    Ashidacchi -- http://hokusosha.com

    Monday, December 9, 2019 1:57 AM
  • Please excuse the delay.  I am working with Microsoft.  Evidently the latest push of Office 365 has broken this functionality.  I've rolled back and I'm currently able to run the macros.  However, I'm waiting on word from Microsoft for the path forward for this issue.  I don't have any reason to believe Microsoft will "roll back" the code that broke these methods so I've asked Microsoft for the path forward work-around.

    Thank you.

    Tuesday, December 17, 2019 5:51 PM
  • Hi Denis Backer,

    I'm not sure how you made contact with Microsoft.
    I'd like to recommend to send a feed back to Word User Voice.

    Regards,

    Ashidacchi -- http://hokusosha.com

    Friday, December 20, 2019 1:05 AM
  • I worked with Microsoft support yesterday.

    For some reason "Option Private Module" where the excel objects (workbook, worksheet, range) were declared was a problem.  Modules referencing public xlWS had the problem.

    Option Explicit
    Option Private Module

    Public fd As Office.FileDialog

    Public appXL As Excel.Application
    Public xlWB As Excel.Workbook
    Public xlWS As Excel.Worksheet
    Public INDEX_WS As Excel.Worksheet

    The first remedy found was to comment out "Option Private Module", run the macros then re-enable "Option Private module.

    'Option Private Module

    (then run the macros, then remove the ' and save)

    Option Private Module

    The second remedy is exporting all of the modules, class modules and forms and then re-importing them into a completely new word document/template.

    • Marked as answer by Denis Backer Friday, January 3, 2020 2:43 PM
    Friday, January 3, 2020 2:43 PM