Excel VBA Initialize Workbook by Clearing Tables

Question

I have some VBA code which I use in another workbook to resize a table to be 1 row and delete the contents of a data table to initialize a workbook. Then a file prompt opens asking the user to select the appropriate file for processing. For some reason, I am getting a

"Run-time error '91': Object variable or With block variable not set"

The code is a copy and paste from the other workbook and I have adjusted the names of the variables, workbooks, worksheets, and table names.

workbook is called "IMD Processing.xlsm" with 2 sheets titled "IMD" and "Raw". The "Raw" sheet has a table with the name "tbl_raw" and the "IMD" sheet has a table with the name "tbl_imd".

Any guidance would be greatly appreciated.

Option Explicit

Sub IMDAutomation()

Dim fileName As String 'Filename string

Dim wb_macro As Workbook 'Macro workbook
Dim ws_macro_imd As Worksheet 'Macro worksheet
Dim ws_macro_raw As Worksheet 'Macro raw worksheet

Dim wb_imd As Workbook 'IMD Workbook for processing
Dim ws_imd As Worksheet 'IMD Worksheet for processing

Dim objTable As ListObject 'Table of raw data

Dim tbl_raw As ListObject 'Raw table in macro workbook
Dim tbl_imd As ListObject 'IMD table in macro workbook

Dim vals As Variant 'Array to store values

Dim lrow As Long 'Variable used to determine number of rows in data table

Set wb_macro = ThisWorkbook
Set ws_macro_imd = Sheets("IMD")
Set ws_macro_raw = Sheets("Raw")

'============ Initialize macro workbook - clearing data ============'
'Clear the raw data in the macro workbook
Set tbl_raw = ws_macro_raw.ListObjects("tbl_raw")
    With tbl_raw.DataBodyRange
           If .Rows.Count > 1 Then
            .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Rows.Delete
        End If
    End With
tbl_raw.DataBodyRange.Rows(1).ClearContents

'Clear the IMD data in the macro workbook
Set tbl_imd = ws_macro_imd.ListObjects("tbl_imd")
    With tbl_imd.DataBodyRange
        If .Rows.Count > 1 Then
            .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Rows.Delete
        End If
    End With

'============ Locate Raw Data File ============'
'Open file dialog to locate the Workforce Review raw data workbook exported from system
With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = False
    .Title = "Select the IMD file"
    .Filters.Clear
    .Filters.Add "Custom Excel Files", "*.xlsx, *xls, *csv"
    .Show
    fileName = .SelectedItems.Item(1)
End With
If InStr(fileName, ".xlsx") = 0 Then
Exit Sub
End If
Workbooks.Open fileName
'Set the Workforce Review raw workbook
Set wb_imd = ActiveWorkbook
'Set the worksheet
Set ws_imd = wb_imd.ActiveSheet


lrow = ws_imd.Cells(ws_imd.Rows.Count, 2).End(xlUp).Row
vals = ws_imd.Range("A2:CU" & lrow)

Application.CutCopyMode = False
Application.CutCopyMode = True

End Sub

UDPATE WITH SOLUTION Thanks to @Variatus for the solution. I did not have a data row in my table so I created one and now it's working. This should work to handle cases where there is no row in the table. If tbl_raw.DataBodyRange Is Nothing Then InsertRowRange Else (Code to clear the table)

When I use the step-into, it happens when the yellow arrow is on If .Rows.Count > 1 Then. As soon as I hit step-into again, the error trips. I am not sure if that means it is on that line or the next line. — MonkeyMonkey
The DataBodyRange doesn't exist. There must be at least one row. I believe you can test for it with If Tbl_raw.DataBodyRange Is Nothing. Conversely, the table's InsertRowRange exists only while the DataBodyRange is Nothing. — Variatus
Ah, I thought I had one row of data in there! That does explain it. So would you say a solution would be: If tbl_raw.DataBodyRange Is Nothing Then InsertRowRange Else (Code to clear the table)? Also, can I use 'Is Nothing' or were you saying that as logic? — MonkeyMonkey

Ilia Gilmijarow Ilia Gilmijarow · Accepted Answer · 2019-03-15T19:02:02

Probably the object that is being searched for by Set tbl_raw = ws_macro_raw.ListObjects("tbl_raw") does not exist in the new workbook and hence referencing through With tbl_raw returns this error

Excel VBA Initialize Workbook by Clearing Tables

1 Answers