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
Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Expert of the Year award recognizes an expert who helped improve Experts Exchange in the past year through high levels of contributions and participation on site. This award is given to the expert who has achieved the highest levels of participation, while maintaining quality contributions and professionalism.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.