If Not IsEmpty(ClearRng) = True Then ClearRng.ClearContents End If
Sub UpdatefromFileofOracle() Dim TargetWs As Worksheet, SourceWs As Worksheet Dim TargetLR As Long, TargetLC As Long, SourceLR As Long, SourceLC As Long, zl As Long Dim FolderPath As String, Filter As String, Caption As String, SourceFName As Variant Dim SourceWB As Workbook, TargetWB As Workbook Dim ClearRng As Range, CopyRng As Range FolderPath = Application.ThisWorkbook.Path ChDir FolderPath Filter = "Excel files (*.xl*),*.xl*" Caption = "Please Browse & Select the downloaded File " SourceFName = Application.GetOpenFilename(Filter, , Caption) If SourceFName = False Then MsgBox "You have CANCELLED selection of needed FILE", vbCritical, "- FOLLOW INSTRUCTION" Exit Sub Else Set SourceWB = Application.Workbooks.Open(SourceFName, Format:=xlDelimited, Local:=True) 'Disable Events With Application .DisplayAlerts = False .ScreenUpdating = False .DisplayStatusBar = True .StatusBar = "!!! Please Be Patient...Updating Records !!!" .EnableEvents = False .Calculation = xlManual End With 'Clear Old Data Set TargetWB = Application.ThisWorkbook Set TargetWs = TargetWB.Worksheets("Maria Hospital") TargetLR = TargetWs.Range("A1").SpecialCells(xlCellTypeLastCell).Row TargetLC = TargetWs.Range("A1").SpecialCells(xlCellTypeLastCell).Column Set ClearRng = TargetWs.Range(TargetWs.Range("A1"), TargetWs.Cells(TargetLR, TargetLC)) If Not IsEmpty(ClearRng) = True Then ClearRng.ClearContents End If 'Copy Data From Source Workbook Set lo = Nothing For zl = 1 To SourceWB.Sheets.Count Set SourceWs = SourceWB.Sheets(zl) If SourceWs.Visible Then Exit For Next On Error Resume Next Set lo = SourceWs.ListObjects(1) On Error GoTo 0 If Not lo Is Nothing Then Set CopyRng = lo.Range Else SourceLR = SourceWs.Range("A1").SpecialCells(xlCellTypeLastCell).Row SourceLC = SourceWs.Range("A1").SpecialCells(xlCellTypeLastCell).Column Set CopyRng = SourceWs.Range(SourceWs.Range("A1"), SourceWs.Cells(SourceLR, SourceLC)) End If CopyRng.Copy TargetWs.Range("A1").PasteSpecial xlPasteAll Application.CutCopyMode = False ' Close Source Workbook Application.DisplayAlerts = False SourceWB.Close SaveChanges:=False TargetWs.Activate TargetWs.Columns.AutoFit TargetWs.Rows.AutoFit TargetWs.Cells.WrapText = False On Error Resume Next TargetWs.ListObjects(1).Unlist On Error GoTo 0 TargetWs.Range("A1").Select 'Enable Events With Application .DisplayAlerts = False .ScreenUpdating = True .DisplayStatusBar = True .StatusBar = False .EnableEvents = True .Calculation = xlAutomatic End With MsgBox "!!! File Import Is Completed Now !!!" End If End Sub
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Connect with Certified Experts to gain insight and support on specific technology challenges including:
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE