CODEWITHSUNDEEP
vbaexcelexcel-2007
Manivannan KG
Manivannan KG

Reputation: 371

Comparing Array versus Table

My aim is to load the Worksheets(ws1...wsn) in the WorkbookA to WorkbookB with specific condition. I have formed an array of worksheets present in WorkbookA.

Condition:
I have a table(tblList) in WorkbookB which lists down the worksheet names that has to be ignored loading (Say I do not want to load ws2,ws4).

Set SourceDataWorkbook = Workbooks.Open(vSrcFileName)
validationProd = SourceDataWorkbook.Sheets.Count

ReDim arrsNames(validationProd)
For i = 1 To validationProd
sName = ""

If fCheckSheet(SourceDataWorkbook, SourceDataWorkbook.Sheets(i).Name) Then
    sName = SourceDataWorkbook.Sheets(i).Name
    If Len(Trim(sName)) > 30 Then
    sName = Mid(sName, 1, 29)
End If
    arrsNames(i - 1) = sName
    outputWorkbook.Sheets.Add(After:=outputWorkbook.Worksheets(i + 3)).Name = _
        sName + "_P"
    SourceDataWorkbook.Sheets(i).Activate
Else
    ErrorStatus = "Source Sheet not found "
    msgBoxReturn = MsgBox(ErrorStatus & SourceDataWorkbook.FullName, _
        vbExclamation + vbOKCancel)
    GoTo TheExit:
End If

Please help me in achieving the requirement.

Upvotes: 0

Views: 62

Answers (2)

Manivannan KG
Manivannan KG

Reputation: 371

Alternate Solution to L42. The abpve question can be implemented by using Dictionary.

Public Function ExclutionDict(Table As String) As Dictionary

Dim rngTable As Range
Dim arr As Variant
Dim Dict As Dictionary
Dim Count As Long
Dim tblIgnoreLoad As String
Dim lo As Excel.ListObject
Dim test As String

If Table = "tblIgnoreLoad" Then
Set lo = ShControl.ListObjects("tblIgnoreLoad")
Set rngTable = lo.DataBodyRange

    arr = rngTable.Value
    Set Dict = New Dictionary

    For Count = LBound(arr, 1) To UBound(arr, 1)
     If Len(arr(Count, 1)) <> 0 Then
         Dict.Add arr(Count, 1), Count
     End If
    Next Count
End If
Set ExclutionDict = Dict
End Function

The above function can be executed to match the requirement

Dim temp As Dictionary
    Set temp = ShControl.ExclutionDict("tblIgnoreLoad")

     If temp.Exists(SourceDataWorkbook.Sheets(i).name) Then
      'Do Nothing

     Else 
  ' Do you usual copy/load
end if

The table is converted into an dictionary and the list is taken as array. A condition is validated to check if the worksheet is present in the dictionary, do nothing; else copy.

Note: Please do not forget to add the reference of Microsoft Scripting Runtime to enable dictionary property.

Thanks.

Upvotes: 1

L42
L42

Reputation: 19727

Your code have a lot of hanging statements (For Loop and If Statements).
I don't know what you mean by load but if what you want is to copy Worksheets from WorkbookA that is not in the list (table in sheet in WorkbookB), you can try below:

Code in WorksheetB:

Dim sourcewb As Workbook, destwb As Workbook
Dim ws As Worksheet
Set sourcewb = Workbooks("workbookname") 'or using Open method
Set destwb = ThisWorkbook 'contains your table

For Each ws In sourcewb.Sheets
    If IsError(Application.Match(ws.Name, Sheet1.Range("tblList"), 0)) Then
        ws.Copy , destwb.Sheets(destwb.Sheets.Count) 'copy after last sheet
    End If
Next

This is assuming you have a table with only 1 column like below:

enter image description here

If not, you should include the header like below:

If IsError(Application.Match(ws.Name, Sheet1.Range("tblList[List]"), 0)) Then

Note that Sheet1 is the worksheet codename that contains your table.
It can be replaced with destwb.Sheets("Sheet1") or whatever name your sheet have.
Is this what you're trying? HTH.

Upvotes: 1

Related Questions