VBA Import frm TBL does not work. earlier helped by Norie and Rgonzo1971

I had this question after viewing VBA modification help needed from earlier solution (VBA to modification to take it from table instead of sheet).

both of the codes from the earlier post, when i embedded it into my actual file does not work.

it cannot import from the table.  the culpirt is either the line SourceWs.ListObjects(1) or somehow the table which i could not figure out the problem.  attached is the file with code and the sample data file which has the table to be imported in the main file.

please run the macro on the MAIN file, when prompted select the file "Data-in Table in SHeet1" then you will see that the data from the table is not imported.  

Sub UploadQuery()
Dim TargetWs As Worksheet, SourceWs As Worksheet
Dim TargetLR As Long, TargetLC As Long, SourceLR As Long, SourceLC 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
        .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("MySHEET")
    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
    Set SourceWs = SourceWB.Sheets(1)
    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
    
    'Patient Number
    CopyRng.Copy
    TargetWs.Range("A1").PasteSpecial xlPasteAll
    Application.CutCopyMode = False
    
    ' Close Source Workbook
    Application.DisplayAlerts = False
    SourceWB.Close SaveChanges:=False
    Application.DisplayAlerts = True
    
    TargetWs.Activate
    TargetWs.Columns.AutoFit
    TargetWs.Range("A4").Select
    
    
    'Enable Events
    With Application
        .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

Data-in-Table-in-Sheet1.xlsx
MAIN.xlsb
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.

BembiCEOCommented:
Hello Flora,

the reason is very simple...
If you step through the macro, you find the line

Set SourceWs = SourceWB.Sheets(1)

if you show the result of that line...
i.e. put
debug.print SourceWB.Sheets(1).Name

you will see that the result is "_options" and not "RRAP" as expected...

That means, that the macro writes into this "_options" table....

The code works, if your change the line
Set SourceWs = SourceWB.Sheets(1)

tp either
Set SourceWs = SourceWB.Sheets(2)

or maybe better
Set SourceWs = SourceWB.Sheets("RRAP")
0
FloraAuthor Commented:
Thanks very much.

how can i modify the code. so that if the sheet is hidden then the sheets(1) should be the visible one and hidden sheet should be ignored.
0
BembiCEOCommented:
As I said...
use
Set SourceWs = SourceWB.Sheets("RRAP")

This point to  the sheet by name rather than to an item is the Sheets collection.

The problem is not, that the sheet is hidden, the problem is that Sheets(1) points to the wrong sheet.
An in the source file you have two sheets, were one in hidden...
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

FloraAuthor Commented:
thanks Bembi.

my sheet name is not always PRAP. it can change.
0
BembiCEOCommented:
Have a look at the attachement....
You see here the
_options sheet (hidden) with the original name Sheet1
RRAP sheet (not hidden) with the original name sheet2
The second file shows the hidden content...

You may decide, if you need this hidden sheet...
To get a stable solution even with different sheet names, you have to make just sure, that the files are constructed in the same way....

a.) If you do not need this hidden sheet, you may just delete it and leave the visible sheet over. Than the code will work with
Set SourceWs = SourceWB.Sheets(1)
(as there is only one sheet, it always has the index = 1)

b.) You decide to leave the hidden file as it is, and you can make sure that it is always sheet 1 and the data sheet is always sheet2, the correct index for the data sheet is 2, that means
Set SourceWs = SourceWB.Sheets(2)
This works with the files you published here...

c.) If you can not make sure, that the structure is always the same in all source files, we have to think about a way, to safely identify the correct sheet. For example: If you can make sure, that there is only on visible sheet and you are not sure about the invisible one, you can replace the line
   
Set SourceWs = SourceWB.Sheets(1)

by

   For zl = 1 To SourceWB.Sheets.Count
        Set SourceWs = SourceWB.Sheets(zl)
        If SourceWs.Visible Then Exit For
    Next


This change takes the first visible sheet...
This version attached...
PRAP_Makro.PNG
PRAP_Makro2.PNG
MAIN.xlsb
0

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.  very much appreciated.
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.