• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 33
  • Last Modified:

help needed on VBA modification on earlier solution by Bill

I had this question after viewing help needed on VBA modification on earlier solution by Bill.

Bill helped me on this code below, i just realized that i need the last modification on this.

this works perfectly, i need to modify this that check on the headers match should not happen if the worksheet Worksheets("Maria Hospital")  is completely blank.  if the target sheet Maria Hospital is blank then paste the data without any message box.  but if there is existing data then the check should happen and if headers match then paste if not then pop up messagebox as it is now.

thanks.



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
Dim HeadersMatch

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
    
    ' Select source worksheet
    For zl = 1 To SourceWB.Sheets.Count
        Set SourceWs = SourceWB.Sheets(zl)
        If SourceWs.Visible Then Exit For
    Next
    
    '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
    
    ' Make sure headers match
    SourceLR = SourceWs.Range("A1").SpecialCells(xlCellTypeLastCell).Row
    SourceLC = SourceWs.Range("A1").SpecialCells(xlCellTypeLastCell).Column
    HeadersMatch = ""
    If TargetLC = SourceLC Then
        For i = 1 To TargetLC
            If TargetWs.Cells(1, i).Value <> SourceWs.Cells(1, i).Value Then
                HeadersMatch = "Header column mismatch at column = " & i & "exiting = " & TargetWs.Cells(1, i).Value & ", import = " & SourceWs.Cells(1, i).Value
                Exit For
            End If
        Next
    Else
        HeadersMatch = "Header column count differs, existing count = " & SourceLC & ", import count = " & TargetLC
    End If
    If HeadersMatch <> "" Then
        MsgBox "Headers in import file do not match existing data, can not import." & vbCrLf & cbCrLf & HeadersMatch
        SourceWB.Close SaveChanges:=False
        With Application
            .DisplayAlerts = False
            .ScreenUpdating = True
            .DisplayStatusBar = True
            .StatusBar = False
            .EnableEvents = True
            .Calculation = xlAutomatic
        End With
        Exit Sub
    End If
    
    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
  
    On Error Resume Next
    Set lo = SourceWs.ListObjects(1)
    On Error GoTo 0
    If Not lo Is Nothing Then
        Set CopyRng = lo.Range
    Else
        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

Open in new window

0
Flora
Asked:
Flora
  • 2
1 Solution
 
Rgonzo1971Commented:
Hi,

ply try
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
Dim HeadersMatch

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
    
    ' Select source worksheet
    For zl = 1 To SourceWB.Sheets.count
        Set SourceWs = SourceWB.Sheets(zl)
        If SourceWs.Visible Then Exit For
    Next
    
    '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
    
    ' Make sure headers match
    SourceLR = SourceWs.Range("A1").SpecialCells(xlCellTypeLastCell).Row
    SourceLC = SourceWs.Range("A1").SpecialCells(xlCellTypeLastCell).Column
    HeadersMatch = ""
    If WorksheetFunction.CountA(TargetWs.Cells) > 0 Then
        If TargetLC = SourceLC Then
            For i = 1 To TargetLC
                If TargetWs.Cells(1, i).Value <> SourceWs.Cells(1, i).Value Then
                    HeadersMatch = "Header column mismatch at column = " & i & "exiting = " & TargetWs.Cells(1, i).Value & ", import = " & SourceWs.Cells(1, i).Value
                    Exit For
                End If
            Next
        Else
            HeadersMatch = "Header column count differs, existing count = " & SourceLC & ", import count = " & TargetLC
        End If
    End If
    If HeadersMatch <> "" Then
        MsgBox "Headers in import file do not match existing data, can not import." & vbCrLf & cbCrLf & HeadersMatch
        SourceWB.Close SaveChanges:=False
        With Application
            .DisplayAlerts = False
            .ScreenUpdating = True
            .DisplayStatusBar = True
            .StatusBar = False
            .EnableEvents = True
            .Calculation = xlAutomatic
        End With
        Exit Sub
    End If
    
    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
  
    On Error Resume Next
    Set lo = SourceWs.ListObjects(1)
    On Error GoTo 0
    If Not lo Is Nothing Then
        Set CopyRng = lo.Range
    Else
        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

Open in new window

0
 
Bill PrewCommented:
Give this a try, it should do what you need.

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
Dim HeadersMatch

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
    
    ' Select source worksheet
    For zl = 1 To SourceWB.Sheets.Count
        Set SourceWs = SourceWB.Sheets(zl)
        If SourceWs.Visible Then Exit For
    Next
    
    '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
        ' Make sure headers match
        SourceLR = SourceWs.Range("A1").SpecialCells(xlCellTypeLastCell).Row
        SourceLC = SourceWs.Range("A1").SpecialCells(xlCellTypeLastCell).Column
        HeadersMatch = ""
        If TargetLC = SourceLC Then
            For i = 1 To TargetLC
                If TargetWs.Cells(1, i).Value <> SourceWs.Cells(1, i).Value Then
                    HeadersMatch = "Header column mismatch at column = " & i & "exiting = " & TargetWs.Cells(1, i).Value & ", import = " & SourceWs.Cells(1, i).Value
                    Exit For
                End If
            Next
        Else
            HeadersMatch = "Header column count differs, existing count = " & SourceLC & ", import count = " & TargetLC
        End If

        If HeadersMatch <> "" Then
            MsgBox "Headers in import file do not match existing data, can not import." & vbCrLf & cbCrLf & HeadersMatch
            SourceWB.Close SaveChanges:=False
            With Application
                .DisplayAlerts = False
                .ScreenUpdating = True
                .DisplayStatusBar = True
                .StatusBar = False
                .EnableEvents = True
                .Calculation = xlAutomatic
            End With
            Exit Sub
        End If
    End If
    
    If Not IsEmpty(ClearRng) = True Then
        ClearRng.ClearContents
    End If

    'Copy Data From Source Workbook
    Set lo = Nothing
  
    On Error Resume Next
    Set lo = SourceWs.ListObjects(1)
    On Error GoTo 0
    If Not lo Is Nothing Then
        Set CopyRng = lo.Range
    Else
        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

Open in new window


»bp
0
 
FloraAuthor Commented:
Bill, thanks. but somehow your code still returned the msgbox even with the sheet Maria Hospital was completely empty.

attached are both file.  the target and and source.
Book2.xlsx
EE.xlsb
0
 
FloraAuthor Commented:
Rgonzo1971

your code worked!  thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now