Not knowing how long a program will run makes us restless. The program might have hung, or might finish in five minutes – or maybe in two hours. This article looks at keeping the anxious user informed in real-time via the Status Bar.

This article assumes the reader has the Developer ribbon displayed and is familiar with the VBA Editor. If not, please Google “Excel Developer Tab” or “Excel Code Window”.

The exercise will look like this, using the example file found here.

Display Progress On The Status Bar

The Process

The program will extract the data, where the Supplier has a country, to an array.

Once the data has been extracted, the program will write the array to the results sheet.

In each case the Status Bar will show the progress.

The Code

In this code, every time the nominator (row number) divided by 2000 = 0.00, the program reports progress.

Modify the denominator of 2000 as required.

Insert a VBA module and copy the following code into the code window:

Option Explicit
Dim arrSupplier(70000, 3) ‘a 2D array of 70,000 rows and three columns
Dim eRow As Long
Dim nProgress As Integer
Dim cRow As Long
Dim nMarker As Single
Dim nArrayRows As Long
Dim n As Long
Dim i As Long

Sub Main()
    Call LoadArray
    Call WriteResults
    MsgBox "The run is complete. The Status Bar will now be cleared."
    Application.StatusBar = False
    Application.Screenupdating = True
End Sub

Sub LoadArray()
    Application.ScreenUpdating = False 'prevent the screen from flickering
    Erase arrSupplier() 'ensure no residuals remain in the array
    Application.StatusBar = False 'clear the Status Bar
    Range("A3").Select
    Selection.End(xlDown).Select 'Find the last row, as denominator
    eRow = ActiveCell.Row
    n = -1
    Range("A3").Select
    Do While ActiveCell > "" 'Loop while data lasts
        If ActiveCell.Offset(0, 1) > "" Then 'If there is a country, record the supplier
            n = n + 1
            arrSupplier(n, 0) = ActiveCell
            arrSupplier(n, 1) = ActiveCell.Offset(0, 1)
            arrSupplier(n, 2) = ActiveCell.Offset(0, 2)
        End If
        cRow = ActiveCell.Row
        nMarker = cRow / 2000 'seeking zero
        If cRow > 0 And nMarker - Int(nMarker) = 0 Then 'same result as using a 'Mod' operator
            nProgress = (cRow / eRow) * 100
            Application.StatusBar = "Writing data to array " & nProgress & "% complete"
            DoEvents
        End If
        Range("A" & ActiveCell.Row + 1).Select
    Loop
    nProgress = (cRow / eRow) * 100 'update the results, after the last marker
    Application.StatusBar = "Printing results " & nProgress & "% complete"
    nArrayRows = n
End Sub

Sub WriteResults()
    Sheets("Results").Activate
    Range("A3:C100000").ClearContents 'clear the results area
    Range("A3").Select

    For i = 0 To n 'loop through the array…
        If arrSupplier(i, 0) = "" Then Exit For '…until it is empty
        ActiveCell = arrSupplier(i, 0)
        ActiveCell.Offset(0, 1) = arrSupplier(i, 1)
        ActiveCell.Offset(0, 2) = arrSupplier(i, 2)
        cRow = ActiveCell.Row
        nMarker = cRow / 2000
        If cRow > 0 And nMarker - Int(nMarker) = 0 Then
            nProgress = (cRow / nArrayRows) * 100
            Application.StatusBar = "Printing results " & nProgress & "% complete"
            DoEvents
        End If
        Range("A" & ActiveCell.Row + 1).Select
    Next i
    nProgress = (cRow / nArrayRows) * 100 'update the results
    Application.StatusBar = "Printing results " & nProgress & "% complete"
End Sub

Add a button to start sub procedure “Main”Button To Start Sub Procedure Main

The Result

The Excel Status Bar will display progress. If you have a very fast computer, you might need to double or triple the amount of data.

Recovering corrupted workbooks

As Excel has become more sophisticated, it does tend to crash from time to time for a variety of reasons, from background printer margin settings to faulty Add-ins or general Office Suite damage. When it happens, it’s too late to take preventative measures; the work done since the last save might be irrevocably lost, unless you have an Excel repair tool to fix the corrupted workbook.

Author Introduction:

Felix Hooker is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including rar repair and sql recovery software products. For more information visit www.datanumen.com

Be the first to comment
             

In this article, we are delighted to provide you with the method to clear a specific table style in your Word document.

Normally, in a document, there can be lots of document, applied with different styles. And we have explained ways to clear all table styles in one of our previous article. For detailed information, you can refer to this link: 3 Useful Methods to Batch Clear All Table Styles in Your Word Document

Now let’s see how to clear a particular table style from your Word document.Clear a Specific Table Style in Your Word Document

Method 1: Clear a Specific Table Style in One Document

  1. First of all, press “Alt+ F11” to trigger the VBA editor in Word.
  2. Then on the left column of the editor, click the “Normal” project.
  3. Next click the “Insert” on menu bar.
  4. On the drop-down menu, choose “Module”.Click "Normal"->Click "Insert"->Click "Module"
  5. Now you have inserted a module. Double click it to open its coding area and paste these codes there:
Sub ClearASpecificTableStyle()
  Dim objTable As Table
  Dim objDoc As Document
  Dim strStyleName As String
 
  Application.ScreenUpdating = False
 
  Set objDoc = ActiveDocument
  strStyleName = InputBox("Enter the name of table style which you want to clear: ", "Specify Table Style")
 
  For Each objTable In objDoc.Tables
    If objTable.Style = strStyleName Then
      objTable.Style = "Table Normal"
      objTable.Borders.Enable = True
    End If
  Next objTable
 
  Application.ScreenUpdating = True
 
  Set objDoc = Nothing
End Sub
  1. Then either click “Run” or hit “F5” to execute codes.Paste Macro->Click "Run"
  2. Now in the “Specify Table Style” box, enter the name of the style which you want to remove from the document.
  3. And click “OK” to move on.Enter Table Style Name->Click "OK"

Here is the possible result:Clear of a Specific Table Style

Method 2: Clear a Specific Table Style in Multiple Documents

Now in case there is a batch of files to process, you can utilize the following macro.

  1. To begin with, put all target documents in one same folder.
  2. Then repeat steps in method 1 to install and run a macro.
  3. But remember to replace macro with this one:
Sub ClearASpecificTableStyleInMultiDoc()
  Dim objTable As Table
  Dim objDoc As Document
  Dim StrFolder As String
  Dim strFile As String
  Dim strStyleName As String
  Dim dlgFile As FileDialog
 
  Set dlgFile = Application.FileDialog(msoFileDialogFolderPicker)
 
  With dlgFile
    If .Show = -1 Then
      StrFolder = .SelectedItems(1) & "\"
    Else
      MsgBox "Select a folder first."
      Exit Sub
    End If
  End With
 
  strStyleName = InputBox("Enter the name of table style which you want to clear: ", "Specify Table Style")
 
  strFile = Dir(StrFolder & "*.docx", vbNormal)
 
  While strFile <> ""
    Set objDoc = Documents.Open(FileName:=StrFolder & strFile)
    Set objDoc = ActiveDocument
 
    For Each objTable In objDoc.Tables
      If objTable.Style = strStyleName Then
        objTable.Style = "Table Normal"
        objTable.Borders.Enable = True
      End If
    Next objTable
 
    objDoc.Save
    objDoc.Close
    strFile = Dir()
  Wend
End Sub
  1. Next, there is the “Browse” window open. Just pick the folder you keep target documents and click “OK” to move on.Choose the Folder->Click "OK"
  2. Then there is the “Specify Table Style” box. Input style name and click “OK” to proceed.

Deal with Document Errors

There is always the possibility of getting Word broken. And the most devastating thing could be losing you valuable documents. Therefore, you must seize time to get a tool to fix Word in no time once the disaster happens.

Author Introduction:

Vera Chen is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including xls recovery and pdf repair software products. For more information visit www.datanumen.com

Be the first to comment
             

In this article, we will focus on demonstrating you 3 useful methods to batch clear all table styles in your Word document.

Every once in a while, if a Word table is applied with an undesired style or it reduces the visual effect of the entire document, you need to clear its style. Now let’s show you how to accomplish such a task.Batch Clear All Table Styles in Your Word Document

Method 1: Clear the Style of One Word Table

  1. First and foremost, move cursor over the target table until you see the cross sign on the upper left corner of the table.
  2. Next click on the cross sign to select the entire table.
  3. Now you have triggered the “Table Tools”. Click “Design” tab.
  4. Then click on the “More” button in “Table Styles” group to show the drop-down menu.
  5. Choose the “Clear” option.Select Table->Click "Design"->Click "Clear"
  6. Now the table style is removed. So are all table borders. To add borders to table, make sure the table is selected. And click on the drop-down button next to “Borders” command.
  7. And choose “All Borders”.Click on Drop-down Button Next to "Borders" Command->Choose "All Borders"

Here is the outcome:Effect of Clearing Table Style and Adding Borders

Method 2: Batch Clear All Table Styles in One Document

Once we have done the single table, the topic of batch processing cannot be missed. First, let’s see how to clear all table styles in a document via VBA.

  1. To start with, press “Alt+ F11” to show the VBA editor in Word.
  2. Next click on the “Normal” project on the left column.
  3. Then click the “Insert” on the menu bar.
  4. And choose “Module” on the drop-down menu.Click "Normal"->Click "Insert"->Click "Module"
  5. Open the module’s coding space by double clicking on it.
  6. Now paste the following macro there:
Sub ClearTableStylesInADoc()
  Dim objTable As Table
  Dim objDoc As Document
 
  Application.ScreenUpdating = False
  Set objDoc = ActiveDocument
 
  For Each objTable In objDoc.Tables
    objTable.Style = "Table Normal"
    objTable.Borders.Enable = True
  Next objTable
 
  Application.ScreenUpdating = True
 
  Set objDoc = Nothing
End Sub
  1. Finally click “Run”.Paste Macro->Click "Run"

This macro clears all table styles in a document, applies “Table Normal” style to them, and adds table borders as well.

Method 3: Batch Clear All Table Styles in Multiple Documents

Now in case you have a couple of document to deal with, here is the macro to clear all table styles in multiple documents.

  1. Firstly, arrange all documents in one folder.
  2. The repeat steps in method 2 to install and run a macro.
  3. Only this time, remember to replace with this macro:
Sub ClearTableStylesInMultiDoc()
  Dim objTable As Table
  Dim objDoc As Document
  Dim StrFolder As String
  Dim strFile As String
  Dim dlgFile As FileDialog
 
  Set dlgFile = Application.FileDialog(msoFileDialogFolderPicker)
 
  With dlgFile
    If .Show = -1 Then
      StrFolder = .SelectedItems(1) & "\"
    Else
      MsgBox "Select a folder first."
      Exit Sub
    End If
  End With
 
  strFile = Dir(StrFolder & "*.docx", vbNormal)
 
  While strFile <> ""
    Set objDoc = Documents.Open(FileName:=StrFolder & strFile)
    Set objDoc = ActiveDocument
 
    For Each objTable In objDoc.Tables
      objTable.Style = "Table Normal"
      objTable.Borders.Enable = True
    Next objTable
 
    objDoc.Save
    objDoc.Close
    strFile = Dir()
  Wend
End Sub
  1. Running the macro, there will be the “Browse” window. Select the folder you store document in step 1 and click “OK”.Choose the Folder->Click "OK"

Leave the rest of work to the macro then.

Ways to Regain Lost Documents

By lost documents, we mean the documents get compromised due to Word corruption or human errors. While dealing with such documents, you should always keep in mind that they can be retrieved with the help of some advanced repairing tool.

Author Introduction:

Vera Chen is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair xls and pdf repair software products. For more information visit www.datanumen.com

Be the first to comment