"Subscript out of range" when used in Loop

[Halkyon]

Hi folks,

I did look at the other threads about this but couldn't find an answer within.

My situation is as follows. I have a sheet that generates a list of file names to be imported into different sheets, I'm writing code to loop through the sheets and import them all into each (each list of files is in a different column named after the sheet the files will be imported into). I think I've got my head around most of it, but when my macro gets to the import stage it says "subscript out of range" on the array that holds all the file names (DataFiles).

This part of my code that highlights when I hit debug.

For DFCounter = 0 To NoOfProducts

[COLOR=SeaGreen]'File Import/Data Connection[/COLOR]
With ActiveSheet.QueryTables.Add(Connection:= _
   "TEXT;Macintosh HD:Users:user:path:" & DataFiles(DFCounter) & ".csv", _
   Destination:=ImportTarget)
  .LotsOfProperties = Variables
End With

This is how the array is populated:

With Sheets("Inputs_DataIDs").Range(DataColumns(SetCounter) & 2, DataColumns(SetCounter) & NoOfProducts + 1)
    ReDim DataFiles(0 To NoOfProducts)
    DataFiles = Range(DataColumns(SetCounter) & 2, DataColumns(SetCounter) & NoOfProducts + 1)
End With

My code worked before I used the With... function to populate the array, I was doing that manually before.

 

[Norie]

Can you post the current code?

 

[Halkyon]

Sub Refresh_Import_Structure()

[COLOR=SeaGreen]' TOGGLES[/COLOR]
Application.ScreenUpdating = False

[COLOR=SeaGreen]' DECLARATIONS[/COLOR]
Dim DataSetNames(0 To 7) As Variant
Dim DataColumns(0 To 7) As Variant
Dim DSCounter As Long
Dim NoOfProducts As Integer
Dim DataFiles As Variant
Dim DFCounter As Long
Dim ImportTarget As Range

[COLOR=SeaGreen]' FILL ARRAYS & SET BASIC VARIABLES
' Populate DataSetNames & DataColumns[/COLOR]
DataSetNames(0) = "Set0"
DataSetNames(1) = "Set1"
DataSetNames(2) = "Set2"
DataSetNames(3) = "Set3"
DataSetNames(4) = "Set4"
DataSetNames(5) = "Set5"
DataSetNames(6) = "Set6"
DataSetNames(7) = "Set7"
DataColumns(0) = "H"
DataColumns(1) = "I"
DataColumns(2) = "J"
DataColumns(3) = "K"
DataColumns(4) = "L"
DataColumns(5) = "M"
DataColumns(6) = "N"
DataColumns(7) = "O"

[COLOR=SeaGreen]' Set the counters[/COLOR]
DSCounter = 0
DFCounter = 0

[COLOR=SeaGreen]' Find number of products[/COLOR]
NoOfProducts = Worksheets("In_DataSetIDs").Range("A1").Offset(Rows.Count - 1, 0).End(xlUp).Row - 1 [COLOR=SeaGreen]'Minus one for header[/COLOR]

[COLOR=SeaGreen]' DATASET LOOP[/COLOR]
For DSCounter = 0 To 7

    With Sheets("In_DataSetIDs").Range(DataColumns(DSCounter) & 1, DataColumns(DSCounter) & (NoOfProducts + 1))
    ReDim DataFiles(0 To NoOfProducts)
    DataFiles = .Range(DataColumns(DSCounter) & 2, DataColumns(DSCounter) & (NoOfProducts + 1))
    End With
    
[COLOR=SeaGreen]    ' Select and Clear Sheet for Import[/COLOR]
    Sheets("In_Fl_" & DataSetNames(DSCounter)).Activate
    ActiveSheet.Cells.Delete

    [COLOR=SeaGreen]' PRODUCTS LOOP[/COLOR]
    Set ImportTarget = Range("A2")
        
    For DFCounter = 1 To NoOfProducts

[COLOR=SeaGreen]        ' File Import/Data Connection[/COLOR]
        With ActiveSheet.QueryTables.Add(Connection:= _
            "TEXT;Macintosh HD:Users:username:Documents:path:reports:" & DataFiles(DFCounter, 1) & ".csv", _
            Destination:=ImportTarget)
                .Name = "Import_" & DataFiles(DFCounter, 1)
                .FieldNames = True
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .RefreshOnFileOpen = False
                .BackgroundQuery = False
                .RefreshStyle = xlInsertEntireRows
                .SavePassword = False
                .SaveData = True
                .AdjustColumnWidth = True
                .TextFilePromptOnRefresh = False
                .TextFilePlatform = xlMacintosh
                .TextFileStartRow = 1
                .TextFileParseType = xlDelimited
                .TextFileTextQualifier = xlTextQualifierDoubleQuote
                .TextFileConsecutiveDelimiter = False
                .TextFileTabDelimiter = False
                .TextFileSemicolonDelimiter = False
                .TextFileCommaDelimiter = True
                .TextFileSpaceDelimiter = False
                .TextFileColumnDataTypes = Array(1, 5)
                .Refresh BackgroundQuery:=False
                .UseListObject = False
                End With
    
            Set ImportTarget = ImportTarget.Offset(0, 2)
    
        Next

[COLOR=SeaGreen]        ' ADD TITLES ABOVE COLUMNS
        ' Reset position[/COLOR]
        Set ImportTarget = Range("A2")
[COLOR=SeaGreen]        ' Add text labels[/COLOR]
        For DFCounter = 0 To NoOfProducts
            ImportTarget.Offset(-1, 0) = DataFiles(DFCounter, 1)
[COLOR=SeaGreen]            ' Move selection[/COLOR]
            Set ImportTarget = ImportTarget.Offset(0, 2)
        Next
        
[COLOR=SeaGreen]    ' Erase AppList array[/COLOR]
    Erase DataFiles()

Next

Application.ScreenUpdating = True

End Sub

It works now that I'm referencing the array populated by the With in full, ie. DataFiles(NoOfProducts, 1).

However, I have a new error: "The destination range is not on the same worksheet that the Query table is being created on". Which I am unsure about as I do activate the sheet beforehand.

Also, to neaten things up, I'd really like to populate the other two arrays (DataColumns and SheetNames) from Ranges incase they change but can't work out how to get the values (DataSetNames) and then cell addresses (DataColumns) in the range H1:O1 transposed into those arrays.

 

[Norie]

Instead of using ActiveSheet (and Activate) why not create a reference to the worksheet?

Set ws =Sheets("In_Fl_" & DataSetNames(DSCounter))

Then replace ActiveSheet with ws.

    ws.Cells.Delete
 
 With ws.QueryTables.Add(Connection:= _
            "TEXT;Macintosh HD:Users:username:Documents:path:reports:" & DataFiles(DFCounter, 1) & ".csv", _
            Destination:=ImportTarget)

 

[Halkyon]

Thanks again Norie. It ran until...

"Cannot find file to import, check filename etc."

Upon checking, I can see that it actually imported DataSet 7 into the sheet for DataSet 0 then had an empty array upon the next cycle of the loop. It's cycling sheets correctly but started at 7 in the array, not 0.

I'm utterly mystified.

 

[Norie]

So it only did one import and it was no 7?

 

[Halkyon]

Yep.

 

[Norie]

Are you sure?

Is there any possibility that all the imports are being down, but they are overwriting each other?

eg import 1 is overwritten by import 2, which is overwritten by import 3 until finally import 7 overwrites import 6.

 

[Halkyon]

I see what you mean, that may also be the case. Although I don't see why it would be importing them all over eachother before moving to the next sheet.

 

[Norie]

Right, I've checked through the code and I think something like that is happening.

I think it's because there is no worksheet reference here.

Set ImportTarget = Range("A2")

Without a reference this refers to A2 on the active sheet, so on each loop the target is A2 on the active sheet.

If you've used my suggestion of creating a reference to the worksheet try this.

Set ImportTarget = ws.Range("A2")

I was actually looking for the something like this, because I know the destination was needed for the query table.

 

[Halkyon]

No luck I'm afraid, I am using you recommended method.