Excel VBA read and write the Word document (edit)

Microsoft Visual Basic for Applications (VBA)

The macros in this project are disabled. Please refer to the online help or documentation of the host application to determine how to enable macros.

Compile error: User-defined type not defined

VBA Tutorial

Word VBA Tutorial - Analyst Cave

VBA Cheat Sheet

Excel VBA Cheat Sheet - Ultimate VBA Reference and Code Snippets (analystcave.com)

Excel VBA

Excel VBA Macro: User Defined Type Not Defined - Stack Overflow

VB How to topics

Visual Basic how-to topics | Microsoft Docs

Video YouTube - Creating Word Documents

Excel VBA Introduction Part 27.1 - Creating Word Documents - YouTube

VBA Examples

VBA Examples (Microsoft Word) (tips.net)

Using Excel VBA to read and write Microsoft Word documents (HAY)

VBA Blog (itpscan.ca)

Read and Write to a Text File with VBA OpenTextFile

Read and Write to a Text File with VBA OpenTextFile - wellsr.com

VBA Code Opening Word in Read-Only Mode

VBA Code Opening Word in Read-Only Mode | MrExcel Message Board

Accessing a table in a Word doc using Excel VBA

Accessing a table in a Word doc using Excel VBA | MrExcel Message Board

Import tables from Word into Excel (HAY)

Import tables from Word into Excel | VBA (exceldome.com)

VBA code to read word document footer (HAY)

excel - VBA code to read word document footer - Stack Overflow

Copy data from Single or Multiple Tables from Word to Excel using VBA

Copy data from Single or Multiple Tables from Word to Excel using VBA (encodedna.com)

Import Data from Word Table to Excel sheet

Excel-VBA Solutions: Import Data from Word Table to Excel sheet (excelvbasolutions.com)

VBA read Word table

VBA Read Cell Value of MS Word Table (github.com)

Option Explicit
Public Sub read_word()
    Dim wa As Word.Application
    Dim wd As Word.Document
    Dim wdtable As Word.Table
    
    Dim wdFileName    As Variant
    Dim TableNo       As Integer  'number of tables in Word doc
    Dim iTable        As Integer  'table number index
    Dim iRow          As Long     'row index in Excel
    Dim iCol          As Integer  'column index in Excel
   
    Dim strCellText As String
    Dim strCellTextLines As New Collection
    Dim rtext As Variant
    Dim vv As Variant
    
    wdFileName = Application.GetOpenFilename("Word files (*.doc*),*.doc*", , _
     "Browse for file containing table to be imported")
    
    If wdFileName = False Then Exit Sub
    Set wd = GetObject(wdFileName)
    
    With wd
      TableNo = wd.Tables.Count
      If TableNo = 0 Then
         MsgBox "This document contains no tables", vbExclamation, "Import Word Table"
      ElseIf TableNo > 1 Then
         TableNo = InputBox("This Word document contains " & TableNo & " tables." & vbCrLf & _
         "Enter table number of table to import", "Import Word Table", "1")
      End If
      
      Debug.Print "Test 1-------------------------------------"
      With .Tables(TableNo)
         'copy cell contents from Word table cells to Excel cells
         For iRow = 1 To .Rows.Count
            rtext = ""
            For iCol = 1 To .Columns.Count
               ''Cells(iRow, iCol) = WorksheetFunction.Clean(.Cell(iRow, iCol).Range.Text)
               rtext = rtext & WorksheetFunction.Clean(.Cell(iRow, iCol).Range.Text) & " "
            Next iCol
            Debug.Print rtext
         Next iRow
      End With
   End With
   
   ''Above function already run OK, but below is another test reading...
   Debug.Print "Test 2-------------------------------------"
   For Each wdtable In wd.Tables
        With wdtable
            Debug.Print "Table :" & wdtable.Title & ":" & wdtable.ID & ":" & wdtable.Rows.Count
            For iRow = 1 To .Rows.Count
                rtext = ""
                For iCol = 1 To .Columns.Count
                    strCellText = .Cell(iRow, iCol).Range.Text
                    Set strCellTextLines = ParseLines(strCellText)
                    ''''Debug.Print "Lines of text found = " & CStr(strCellTextLines.Count)
                    For Each vv In strCellTextLines
                        rtext = rtext & vv & " "
                    Next vv
                Next iCol
                Debug.Print rtext
            Next iRow
            
        End With
   Next
   
   Set strCellTextLines = Nothing
   Set wd = Nothing
    
End Sub
Private Function ParseLines(tStr As String) As Collection
    Dim tColl As New Collection, tptr As Integer, tlastptr As Integer, tCurrStr As String
    tlastptr = 1

    With tColl
        Do
            tptr = InStr(tlastptr, tStr, Chr(13))
            If tptr = 0 Then Exit Do

            tCurrStr = Mid(tStr, tlastptr, tptr - tlastptr)
            tColl.Add tCurrStr

            tlastptr = tptr + 1
        Loop
    End With

    Set ParseLines = tColl
End Function

Excel Interop (edit)

How to access Office interop objects - C# Programming Guide | Microsoft Docs

How to Create Word Document Using C# (c-sharpcorner.com)

using Excel = Microsoft.Office.Interop.Excel;
using Word = Microsoft.Office.Interop.Word;
using System.Runtime.InteropServices;
using Microsoft.Vbe.Interop;

• %ProgramFiles%\Microsoft Visual Studio\Shared\Visual Studio Tools for Office\PIA\

• %ProgramFiles(x86)%\Microsoft Visual Studio\Shared\Visual Studio Tools for Office\PIA\

How to create Excel file in C# (net-informations.com)

• using System.Runtime.InteropServices;
• using Microsoft.Office.Interop.Excel;
• using Microsoft.Office.Interop.Word;
• using Microsoft.Vbe.Interop; //C# How Do I Insert Macro Into Excel VBA?

            try
            {
                //create new instance
                excelApp = new Excel.Application();

                //suppress displaying alerts (such as prompting to overwrite existing file)
                excelApp.DisplayAlerts = false;

                //set Excel visability
                excelApp.Visible = true;
                 
                ...            
            }
            catch (Exception ex)
            {
                string errMsg = "Error (WriteToExcel) - " + ex.Message;
                System.Diagnostics.Debug.WriteLine(errMsg);

                if (ex.Message.StartsWith("Cannot access read-only document"))
                {
                    System.Windows.Forms.MessageBox.Show(ex.Message + "Please close the workbook, before trying again.", "Error - Unable To Write To Workbook", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
                }
            }
            finally
            {
                if (workbook != null)
                {
                    //close workbook
                    workbook.Close();

                    //release all resources
                    System.Runtime.InteropServices.Marshal.FinalReleaseComObject(workbook);
                }

                if (excelApp != null)
                {
                    //close Excel
                    excelApp.Quit();

                    //release all resources
                    System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excelApp);
                }
            }

C# COM Interop Excel: How to write to cells from C# using Interop Excel? - Stack Overflow (HAY HAY HAY)

Creating Excel File using Interop Services (c-sharpcorner.com) (HAY HAY HAY)

How to Read an Excel file in Windows Forms Application using C# and Vb.Net (encodedna.com) (HAY HAY HAY)

How to access Office Interop Objects in C# | CodeGuru.com (Word + Excel)

How to automate Microsoft Excel from Microsoft Visual C#.NET - Office | Microsoft Docs

1. Right click into Project > References > Add Reference..
2. On the COM tab, locate Microsoft Excel Object Library

How to access Office interop objects - C# Programming Guide | Microsoft Docs

using Microsoft.Office.Interop.Excel;

string path = "C:\\Projects\\ExcelInterop\\Test.xlsx ";

Application excelApp = new Application();
Workbook excelWorkbook = excelApp.Workbooks.Open(path);
Worksheet excelWorksheet = excelWorkbook.ActiveSheet;

You have two ways to get the value

using xl = Microsoft.Office.Interop.Excel;

// 1. Using Cells with numbered reference
string cellValue = (excelWorksheet.Cells[10, 2] as xl.Range).Text.ToString();

// 2. Using Range with address reference
string cellValue = excelWorksheet.Range["J2"].Text.ToString();

Excel's VBA

Excel VBA Object Model And Object References: The Essential Guide (powerspreadsheets.com)

If you want to really master Excel macros and Visual Basic for Applications, you must have a good understanding of the following 3 topics:

• Objects.
• How to manipulate VBA objects.
• Excel's VBA object model.

Chapters

excel-vba eBook (riptutorial.com)

• Chapter 1: Getting started with excel-vba
• Chapter 2: Application object
• Chapter 3: Arrays
• Chapter 4: autofilter ; Uses and best practices
• Chapter 5: Binding
• Chapter 6: Charts and Charting
• Chapter 7: Common Mistakes
• Chapter 8: Conditional formatting using VBA
• Chapter 9: Conditional statements
• Chapter 10: Creating a drop-down menu in the Active Worksheet with a Combo Box
• Chapter 11: CustomDocumentProperties in practice
• Chapter 12: Debugging and Troubleshooting
• Chapter 13: Excel VBA Tips and Tricks
• Chapter 14: Excel-VBA Optimization
• Chapter 15: File System Object
• Chapter 16: How to record a Macro
• Chapter 17: Locating duplicate values in a range
• Chapter 18: Loop through all Sheets in Active Workbook
• Chapter 19: Merged Cells / Ranges
• Chapter 20: Methods for Finding the Last Used Row or Column in a Worksheet
• Chapter 21: Named Ranges
• Chapter 22: Pivot Tables
• Chapter 23: PowerPoint Integration Through VBA
• Chapter 24: Ranges and Cells
• Chapter 25: SQL in Excel VBA - Best Practices
• Chapter 26: Use Worksheet object and not Sheet object
• Chapter 27: User Defined Functions (UDFs)
• Chapter 28: VBA Best Practices
• Chapter 29: VBA Security
• Chapter 30: Workbooks
• Chapter 31: Working with Excel Tables in VBA

What is Microsoft.Vbe.Interop.dll?

Office primary interop assemblies - Visual Studio (Windows) | Microsoft Docs

Microsoft.Vbe.Interop.dll is part of Microsoft Office 2013 and developed by Microsoft Corporation according to the Microsoft.Vbe.Interop.dll version information.

Microsoft.Vbe.Interop.dll's description is "Microsoft.Vbe.Interop"

Microsoft.Vbe.Interop.dll is digitally signed by Microsoft Corporation.

Microsoft.Vbe.Interop.dll is usually located in the 'c:\Program Files\WinZip\' folder.

None of the anti-virus scanners at VirusTotal reports anything malicious about Microsoft.Vbe.Interop.dll.

If you have additional information about the file, please share it with the FreeFixer users by posting a comment at the bottom of this page.

Vendor and version information

The following is the available information on Microsoft.Vbe.Interop.dll:

Here's a screenshot of the file properties when displayed by Windows Explorer:

VirusTotal report

None of the 48 anti-virus programs at VirusTotal detected the Microsoft.Vbe.Interop.dll file.

Excel VBA delete row if contains certain strings (edit)

https://excel.officetuts.net/en/vba/delete-a-row-if-cell-contains

https://analysistabs.com/vba/delete-row-if-cell-contains-string-excel-macro-example-code/

https://stackoverflow.com/questions/26683163/excel-vba-delete-row-if-contains-certain-strings-or-has-a-delete-flag-set

What is VBA?

The acronym VBA stands for Visual Basic for Applications. It is an integration of the Visual Basic with Microsoft Office applications (MS Excel, MS PowerPoint, MS Access, MS Word and MS Outlook). By running VBA within the Microsoft Office applications, you can automate repetitive tasks.

Getting Started With VBA

https://www.guru99.com/creating-your-first-visual-basic-for-applications-vba-in-excel.html

https://riptutorial.com/excel-vba

https://www.listendata.com/2014/05/excel-macro-beginner-tutorial-make-your.html

https://www.i-programmer.info/ebooks/automating-excel/1264-getting-started.html

Getting Started With VBA Automation

https://spreadsheet1.com/getting-started-with-vba.html

Open The Visual Basic Editor

https://www.thespreadsheetguru.com/

https://www.ozgrid.com/forum/

http://excelexperts.com/forum

http://excelexperts.com/Free-Excel-Training-Videos

What are excel macros?

https://www.listendata.com/2014/05/excel-macro-beginner-tutorial-make-your.html

5 Different Ways to Find The Last Row or Last Column Using VBA

https://www.thespreadsheetguru.com/blog/2014/7/7/5-different-ways-to-find-the-last-row-or-last-column-using-vba

Copy & Paste Multiple Excel Ranges To Separate PowerPoint Slides With VBA

https://www.thespreadsheetguru.com/blog/2014/6/30/copy-paste-multiple-excel-ranges-to-separate-powerpoint-slides-with-vba

Code Sample

Giả sử cột A có những Row chứa chuỗi cần Delete cả dòng đó, ta thực hiện như sau:

1. Press Alt + F11
2. Go to menu Insert > Module
3. Paste the following code
4. F5 to delete entire row

Sub DeleteEntireRow()

 Dim i As Long, searchString As String

 For i = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1

 searchString = LCase(Range("A" & i))

 If (InStr(1, searchString, ".exe") > 0) Or _
 (InStr(1, searchString, ".png") > 0) Or _
 (InStr(1, searchString, ".jpg") > 0) Then
 Rows(i).Delete
 End If

NextRow:
 Next i

End Sub