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