Help needed on vba modification on earlier solution

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

i need help with modification of code below.

that if the target worksheet is not empty.

right on this line   If WorksheetFunction.CountA(TargetWs.Cells) > 0 Then

before doing anything else. search for a column named "Hospitals"  if this column found then delete just this column and then run the rest of the code which is the matching column headers etc.

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

LVL 6
FloraAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls 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
        Set rngFind = Nothing
        Set rngFind = TargetWs.Range("1:1").Find("Hospitals")
        If Not rngFind Is Nothing Then rngFind.EntireColumn.Delet

        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

Regards
1
FloraAuthor Commented:
thanks Rgonzo1971.

it does delete that new column, but then it gives me the attached error, even though the number of columns are in both sheets 83 but somehow counts it double

WW.png
i have also attached both source file and target file.
Target-file.xlsb
source-file.xlsx
0
Rgonzo1971Commented:
Source file has 136 cols
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

FloraAuthor Commented:
there is something wrong with the code.  for the first attempt it works. but on the next one. it duplicates the data by columns.

i am trying to figure out what is causing this.
0
FloraAuthor Commented:
Rgonzo1971.

with a smaller data set. i recorded a video.
Recorded.mp4
Target-file.xlsb
source.xlsx
0
FloraAuthor Commented:
i think , we are getting close.

i put this into the code after Debug.Print TargetLC  and for the first time, it counts 4 and the next run it counts 5

i think the line TargetLC = TargetWs.Range("A1").SpecialCells(xlCellTypeLastCell).Column    counts columns based on usedrange and even after the deletion of the column created as Hospitals the usedrange does not get reset.

how can i reset the used range here?
0
Rgonzo1971Commented:
Hi,

pls 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").End(xlDown).Row
    TargetLC = TargetWs.Range("A1").End(xlToRight).Column
    
    ' Make sure headers match
    SourceLR = SourceWs.Range("A1").End(xlDown).Row
    SourceLC = SourceWs.Range("A1").End(xlToRight).Column
    HeadersMatch = ""
    If WorksheetFunction.CountA(TargetWs.Cells) > 0 Then
        Set rngFind = Nothing
        Set rngFind = TargetWs.Range("1:1").Find("Hospitals")
        If Not rngFind Is Nothing Then
            rngFind.EntireColumn.Delete
            TargetLC = TargetLC - 1
        End If
        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
    
    Call macro1
    '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

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FloraAuthor Commented:
Thank you very much
0
FloraAuthor Commented:
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.

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.