none
Control Excel from Word by VBA RRS feed

  • Question

  • Greetings.

    I have a *.dotm and I need to take the values of the cells to a *.xlsm. Sorry I don't know the names of this type of files in english. The problem I'm having is that the *.dotm has a lot of tables and I have to control which is the value of a single cell on each table and then paste on the *.xlsm automatically from a macro. I can use the *.xlsm I already have or generate another one from Word. I don't know which option could be easier. Sorry my english.

    Thank you in advise. I hope you can help me.

    See you!

    Friday, June 16, 2017 7:08 AM

Answers

  • Hi Lorenzo del Barrio,

    I think opening an existing .xlsm file and generating another one from Word are both easy to do. You could use WorkBooks.Open to open an exist *.xlsm file. You could also use WorkBooks.Add to generate another one workbook but remember to save the workbooks as a *.xlsm file via SaveAs method. You could iterate through Document.Tables and Range.Cells to know the value in each single cell. Here is the example.

    'remember add reference Microsoft Excel Library
    
    Dim xlApp As Excel.Application
    
    Dim wb As Excel.Workbook
    
    Set xlApp = New Excel.Application
    
    'Set wb = xlApp.Workbooks.Open("your .xlsm file full name(path+name)")
    
    Set wb = xlApp.Workbooks.Add
    
    Dim tb As Table
    
    Dim cel As Cell
    
    For Each tb In ThisDocument.Tables
    
    For Each cel In tb.Range.Cells
    
    'copy cel.Range.Text to the wb
    
    Next cel
    
    Next tb
    
    wb.SaveAs FileName:="C:\Users\Desktop\Test.xlsm", _
    
            FileFormat:=xlOpenXMLWorkbookMacroEnabled

    Best Regards,

    Terry

    Monday, June 19, 2017 6:42 AM

All replies

  • Hi Lorenzo del Barrio,

    I think opening an existing .xlsm file and generating another one from Word are both easy to do. You could use WorkBooks.Open to open an exist *.xlsm file. You could also use WorkBooks.Add to generate another one workbook but remember to save the workbooks as a *.xlsm file via SaveAs method. You could iterate through Document.Tables and Range.Cells to know the value in each single cell. Here is the example.

    'remember add reference Microsoft Excel Library
    
    Dim xlApp As Excel.Application
    
    Dim wb As Excel.Workbook
    
    Set xlApp = New Excel.Application
    
    'Set wb = xlApp.Workbooks.Open("your .xlsm file full name(path+name)")
    
    Set wb = xlApp.Workbooks.Add
    
    Dim tb As Table
    
    Dim cel As Cell
    
    For Each tb In ThisDocument.Tables
    
    For Each cel In tb.Range.Cells
    
    'copy cel.Range.Text to the wb
    
    Next cel
    
    Next tb
    
    wb.SaveAs FileName:="C:\Users\Desktop\Test.xlsm", _
    
            FileFormat:=xlOpenXMLWorkbookMacroEnabled

    Best Regards,

    Terry

    Monday, June 19, 2017 6:42 AM
  • Thank you so much!

    This worked perfectly for me. I've chosen the option of opening an existing xlsm, but the other option Works also fine.

    Can I give you some calification of expert? hahah

    Thank you.

    Wednesday, June 21, 2017 10:58 AM