Importing data from another workbook

[Billdub417]

Hello,

Looking for some guidance re the below code. Main issues seem to be:

1) It is very slow - is there anything that can be done to speed it up?
2) If a workbook has previously crashed, and therefore comes up with a "Do you want to recover as much as possible" message when reopening, it is coming up with an error on the "Set wb = Workbooks.open(Ret)" line?

thanks in advance

Sub importData()
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
    Dim ws As Worksheet
    Dim filter As String
    Dim targetWorkbook As Workbook, wb As Workbook
    Dim Ret As Variant

    Set targetWorkbook = Application.ActiveWorkbook

    filter = "Text files (*.xlsb),*.xlsb"
    Caption = "Please Select an input file "
    Ret = Application.GetOpenFilename(filter, , Caption)

    If Ret = False Then Exit Sub

    Set wb = Workbooks.Open(Ret)
        

    With wb.Sheets("sheet1").Range("d3:e7")
        ThisWorkbook.Sheets("sheet1").Range("D3").Resize(.Rows.Count, .Columns.Count).value = .value
    End With
    
    With wb.Sheets("sheet1").Range("d10:e11")
        ThisWorkbook.Sheets("sheet1").Range("D10").Resize(.Rows.Count, .Columns.Count).value = .value
    End With
    
    With wb.Sheets("sheet1").Range("c12")
        ThisWorkbook.Sheets("sheet1").Range("c12").Resize(.Rows.Count, .Columns.Count).value = .value
    End With
    
    With wb.Sheets("sheet2").Range("a2:c550")
        ThisWorkbook.Sheets("sheet2").Range("a2").Resize(.Rows.Count, .Columns.Count).value = .value
    End With
    
    With wb.Sheets("sheet3").Range("e24:e33")
        ThisWorkbook.Sheets("sheet3").Range("e24").Resize(.Rows.Count, .Columns.Count).value = .value
    End With
    
    With wb.Sheets("sheet3").Range("e108:e117")
        ThisWorkbook.Sheets("sheet3").Range("e108").Resize(.Rows.Count, .Columns.Count).value = .value
    End With

    wb.Close savechanges = False
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
          
End Sub

 

[rlv01]

Not tested.

Sub importData()
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    Dim filter As String
    Dim wb As Workbook
    Dim Ret As Variant, Caption, SA
    
    filter = "Text files (*.xlsb),*.xlsb"
    Caption = "Please Select an input file "
    Ret = Application.GetOpenFilename(filter, , Caption)
    
    If Ret = False Then Exit Sub
    
    Set wb = Workbooks.Open(Ret)
    
    With ThisWorkbook
        SA = wb.Worksheets("Sheet1").Range("d3:f7").Value
        .Worksheets("Sheet1").Range("D3").Resize(UBound(SA, 1), UBound(SA, 2)).Value = SA
        
        SA = wb.Worksheets("Sheet1").Range("d10:e11")
        .Worksheets("Sheet1").Range("D10").Resize(UBound(SA, 1), UBound(SA, 2)).Value = SA
        
        SA = wb.Worksheets("Sheet1").Range("c12")
        .Worksheets("Sheet1").Range("C12").Resize(UBound(SA, 1), UBound(SA, 2)).Value = SA
        
        SA = wb.Worksheets("Sheet2").Range("a2:c550")
        .Worksheets("Sheet2").Range("A2").Resize(UBound(SA, 1), UBound(SA, 2)).Value = SA
        
        SA = wb.Worksheets("Sheet3").Range("e24:e33")
        .Worksheets("Sheet3").Range("E24").Resize(UBound(SA, 1), UBound(SA, 2)).Value = SA
        
        SA = wb.Worksheets("Sheet3").Range("e108:e117")
        .Worksheets("Sheet3").Range("E108").Resize(UBound(SA, 1), UBound(SA, 2)).Value = SA
    End With
    
    wb.Close savechanges:=False
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub