Run-time error '-2147418113 (8000ffff)': Automation error Catastrophic failur

[TTUK]

Hi All,

I am having the error shown in my subject line when trying to run the below VBA script.
Please can someone please help me to understand how and why this isn't working?! It was working fine the other day!!

Sub DAY1_8DM_Import()

Application.StatusBar = "Please wait ... IMPORTING DAY 1 8DM"


Dim wbCSV   As Workbook
Dim wsMstr  As Worksheet:   Set wsMstr = ThisWorkbook.Sheets("8DM Day 1 Import")
Dim fPath   As String:      fPath = "\\ttasuk-vsrv010\SHARED\05 Supply Chain\Supplier Schedules\Master Planning Document\8DM Day 1 Import\"    'path to CSV files, include the final \
Dim fCSV    As String
Dim NextCol As Long


Worksheets("8DM Day 1 Import").Range("A:Z").ClearContents


        wsMstr.UsedRange.Clear
        NextCol = 1


fCSV = Dir(fPath & "*.csv")         'start the CSV file listing


    Do While Len(fCSV) > 0
      'open a CSV file
        Set wbCSV = Workbooks.Open(fPath & fCSV)
      
      'copy date into master sheet and close source file
        ActiveSheet.UsedRange.Copy wsMstr.Cells(2, NextCol)
        wbCSV.Close False
      'ready next CSV
        fCSV = Dir
        NextCol = wsMstr.Cells(3, Columns.Count).End(xlToLeft).Column + 1
    
    Loop
        
Call Qty_Calculate




Sheets("8DM Data").Select
    ActiveWorkbook.RefreshAll


Sheets("BOM").Select
    ActiveWorkbook.RefreshAll


Sheets("UPDATE").Select
    ActiveWorkbook.RefreshAll


      
    Application.StatusBar = ""


MsgBox "Update of Day 1 8DM Completed"




End Sub

Thanks!!! :D

 

[Norie]

Where in the code does the error occur?

 

[TTUK]

Hi Norie,

It occurs at this point highlighted in bold and red.

Dim wbCSV   As Workbook
Dim wsMstr  As Worksheet[COLOR=#ff0000][B]:   Set wsMstr = ThisWorkbook.Sheets("Day 1 Import")[/B][/COLOR]
Dim fPath   As String:      fPath = "\\ttasuk-vsrv010\SHARED\05 Supply Chain\Supplier Schedules\Master Planning Document\8DM Day 1 Import\"    'path to CSV files, include the final \
Dim fCSV    As String
Dim NextCol As Long

The Sheet Name, is named "Day 1 Import"
And in the VB Window for Excel Objects it is as the name: Sheet22 (Day 1 Import)


Thanks,

 

[Norie]

Does it make any difference if you move the assignment of values/references to the variables outwith the declaration section?

Kind of like this.

Sub DAY1_8DM_Import()
Dim wbCSV As Workbook
Dim wsMstr As Worksheet
Dim fPath As String
Dim fCSV As String
Dim NextCol As Long

    Application.StatusBar = "Please wait ... IMPORTING DAY 1 8DM"

    fPath = "\\ttasuk-vsrv010\SHARED\05 Supply Chain\Supplier Schedules\Master Planning Document\8DM Day 1 Import\"    'path to CSV files, include the final \

    Set wsMstr = ThisWorkbook.Sheets("8DM Day 1 Import")

    Worksheets("8DM Day 1 Import").Range("A:Z").ClearContents

    wsMstr.UsedRange.Clear
    NextCol = 1

    fCSV = Dir(fPath & "*.csv")         'start the CSV file listing

    Do While Len(fCSV) > 0
        'open a CSV file
        Set wbCSV = Workbooks.Open(fPath & fCSV)

        'copy date into master sheet and close source file
        ActiveSheet.UsedRange.Copy wsMstr.Cells(2, NextCol)
        wbCSV.Close False
        'ready next CSV
        fCSV = Dir
        NextCol = wsMstr.Cells(3, Columns.Count).End(xlToLeft).Column + 1

    Loop

    Call Qty_Calculate

    Sheets("8DM Data").Select
    ActiveWorkbook.RefreshAll

    Sheets("BOM").Select
    ActiveWorkbook.RefreshAll

    Sheets("UPDATE").Select
    ActiveWorkbook.RefreshAll

    Application.StatusBar = ""

    MsgBox "Update of Day 1 8DM Completed"

End Sub

 

[TTUK]

Hi Norie,

No the error still appears. I have highlighted where it throws up the error...

Sub DAY1_8DM_Import()Dim wbCSV As Workbook
Dim wsMstr As Worksheet
Dim fPath As String
Dim fCSV As String
Dim NextCol As Long


    Application.StatusBar = "Please wait ... IMPORTING DAY 1 8DM"


    fPath = "\\ttasuk-vsrv010\SHARED\05 Supply Chain\Supplier Schedules\Master Planning Document\8DM Day 1 Import\"    'path to CSV files, include the final \


[COLOR=#ff0000][B]    Set wsMstr = ThisWorkbook.Sheets("8DM Day 1 Import")[/B][/COLOR]


    Worksheets("8DM Day 1 Import").Range("A:Z").ClearContents


    wsMstr.UsedRange.Clear
    NextCol = 1


    fCSV = Dir(fPath & "*.csv")         'start the CSV file listing


    Do While Len(fCSV) > 0
        'open a CSV file
        Set wbCSV = Workbooks.Open(fPath & fCSV)


        'copy date into master sheet and close source file
        ActiveSheet.UsedRange.Copy wsMstr.Cells(2, NextCol)
        wbCSV.Close False
        'ready next CSV
        fCSV = Dir
        NextCol = wsMstr.Cells(3, Columns.Count).End(xlToLeft).Column + 1


    Loop


    Call Qty_Calculate


    Sheets("8DM Data").Select
    ActiveWorkbook.RefreshAll


    Sheets("BOM").Select
    ActiveWorkbook.RefreshAll


    Sheets("UPDATE").Select
    ActiveWorkbook.RefreshAll


    Application.StatusBar = ""


    MsgBox "Update of Day 1 8DM Completed"


End Sub