Looping through sheets while setting up different data connection for each sheet

[sethjbr]

Greetings Outstanding Mr. Excel Community,

I have some experience with using Excel macros and VBA scripting but I don't know enough about loops and arrays to solve this little problem. I think I have just about all the code correct, so this should be an easy fix.

Basically I have a workbook with an instructions and a tmp (template) worksheet along with a bunch of other worksheets that are almost identical to the tmp worksheet.

The worksheet tabs look like this as an example:

Instructions

templatecopy1

templatecopy2

templatecopy3

templatecopy4

templatecopy5

tmp

<tbody>
</tbody>

For example, on the instructions sheet, in cells j3:j30, I have this information

list1.csv

list2.csv

list3.csv

list4.csv

list5.csv

<tbody>
</tbody>

Also on the Instructions Sheet, I have a list of all the duplicate worksheet names that are based upon the tmp worksheet in cells I3:I30.

With the exception of the instructions worksheet and the tmp worksheet, I am trying to setup a worksheet connection for each of the worksheets that are a copy of the tmp worksheet. So for templatecopy1 worksheet, I want to setup a connection to the list1.csv data file. For the templatecopy2 worksheet, I want to setup a connection to the list2.csv data file.

The following code will perform this task except that the data connection that is always setup for each worksheet is list1.csv. I know this is because the ImportFileName variable assigns an absolute value, but when I attempt setting it up with an array value based on the values in J3:J30, I get a type mismatach error.

'Select Instructions worksheet
Sheets("Instructions").Select


    'Specify memory space for necessary information
    Dim CurrentSheet As Worksheet
    Dim WorkSheetList As Variant
    Dim ImportCSVPath As String
    Dim ImportFileName As Variant


    'Assign values to the specified memory space variables
    WorkBookPath = ThisWorkbook.Path
    WorkSheetList = Sheets("Instructions").Range("I3:I30").Value
    ImportCSVPath = "\sites_CSV_files\"
    ImportFileName = "list1.csv"
    FullFileNamePath = WorkBookPath & ImportCSVPath & ImportFileName
    
        'Start counter at position 1
            x = 1
   
        'This for procedure loops to all sheets except Instructions and tmp sheets
            For Each CurrentSheet In Worksheets
        
                ' Avoid worksheets named "Instructions" and "tmp"
                If CurrentSheet.Name <> "Instructions" And CurrentSheet.Name <> "tmp" Then
            
                    ' Activate the Worksheet in the loop
                    CurrentSheet.Activate
                    
                        'Setup worksheet connection to the CSV file located at the full file name path
                        With ActiveSheet.QueryTables.Add(Connection:="TEXT;" + FullFileNamePath, Destination:=Range("$A$4"))
                            .Name = ImportFileName
                            .FieldNames = True
                            .RowNumbers = False
                            .FillAdjacentFormulas = True
                            .PreserveFormatting = True
                            .RefreshOnFileOpen = False
                            .RefreshStyle = xlInsertDeleteCells
                            .SavePassword = False
                            .SaveData = True
                            .AdjustColumnWidth = True
                            .RefreshPeriod = 0
                            .TextFilePromptOnRefresh = False
                            .TextFilePlatform = 437
                            .TextFileStartRow = 2
                            .TextFileParseType = xlDelimited
                            .TextFileTextQualifier = xlTextQualifierDoubleQuote
                            .TextFileConsecutiveDelimiter = False
                            .TextFileTabDelimiter = False
                            .TextFileSemicolonDelimiter = True
                            .TextFileCommaDelimiter = False
                            .TextFileSpaceDelimiter = False
                            .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
                            .TextFileTrailingMinusNumbers = True
                            .Refresh BackgroundQuery:=False
                        End With
    
            'Increase X by one value after every repetition
                x = x + 1
        
            ' Exit for loop if X is positioned at last value in the range
            If x = UBound(WorkSheetList, 1) + 1 Then Exit For
            
                End If
            
        'Parse commands on the next worksheet
        Next CurrentSheet

This is the code that I am testing that presents a type mismatch error message.

'Select Instructions worksheet
Sheets("Instructions").Select


    'Specify memory space for necessary information
    Dim CurrentSheet As Worksheet
    Dim WorkSheetList As Variant
    Dim ImportCSVPath As String
    Dim ImportFileName As Variant


    'Assign values to the specified memory space variables
    WorkBookPath = ThisWorkbook.Path
    WorkSheetList = Sheets("Instructions").Range("I3:I30").Value
    ImportCSVPath = "\sites_CSV_files\"
    ImportFileName = Sheets("Instructions").Range("J3:J30").Value
    FullFileNamePath = WorkBookPath & ImportCSVPath & ImportFileName
    
        'Start counter at position 1
            x = 1
   
        'This for procedure loops to all sheets except Intructions and tmp sheets
            For Each CurrentSheet In Worksheets
        
                ' Avoid worksheets named "Instructions" and "tmp"
                If CurrentSheet.Name <> "Instructions" And CurrentSheet.Name <> "tmp" Then
            
                    ' Activate the Worksheet in the loop
                    CurrentSheet.Activate
                    
                    
                        'Setup worksheet connection to the CSV file located at the full file name path
                        With ActiveSheet.QueryTables.Add(Connection:="TEXT;" + FullFileNamePath, Destination:=Range("$A$4"))
                            .Name = ImportFileName
                            .FieldNames = True
                            .RowNumbers = False
                            .FillAdjacentFormulas = True
                            .PreserveFormatting = True
                            .RefreshOnFileOpen = False
                            .RefreshStyle = xlInsertDeleteCells
                            .SavePassword = False
                            .SaveData = True
                            .AdjustColumnWidth = True
                            .RefreshPeriod = 0
                            .TextFilePromptOnRefresh = False
                            .TextFilePlatform = 437
                            .TextFileStartRow = 2
                            .TextFileParseType = xlDelimited
                            .TextFileTextQualifier = xlTextQualifierDoubleQuote
                            .TextFileConsecutiveDelimiter = False
                            .TextFileTabDelimiter = False
                            .TextFileSemicolonDelimiter = True
                            .TextFileCommaDelimiter = False
                            .TextFileSpaceDelimiter = False
                            .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
                            .TextFileTrailingMinusNumbers = True
                            .Refresh BackgroundQuery:=False
                        End With
    
            'Increase X by one value after every repetition
                x = x + 1
        
            ' Exit for loop if X is positioned at last value in the range
            If x = UBound(WorkSheetList, 1) + 1 Then Exit For
            
                End If
            
        'Parse commands on the next worksheet
        Next CurrentSheet

Any assistance that can be provided is greatly appreciated.

Thanks!

Seth

 

[Osvaldo Palmeiro]

Hi.
Maybe you could take the "x" variable value and use it like this:

.Name = ImportFileName(x,1)

 

[sethjbr]

Hi.
Maybe you could take the "x" variable value and use it like this:

.Name = ImportFileName(x,1)

@Osvaldo Palmeiro

Thank you very much for the suggestion, however, would you please provide a bit more information?

If I dim X as a string value and define X as .Name = ImportFileName(x,1), than doesn't there need to be a sheet name that is specified for that named value?

 

[Osvaldo Palmeiro]

Hi.
If I correctly understood your issue, you want to replace "list1.csv" which is associated to the variable ImportFileName in your first code (hard coded) by the list of names from the range J3:J30 into a Loop. Is it?

obs. variable "x" should be Long/Integer type.

Take a look at the example I wrote below. I reproduced a structure similar to your code, in which a MsgBox will show the values of the list in range J3:J7 (that is from list1.csv to list5.csv).

Sub test()
  Dim WorkSheetList As Variant
  Dim ImportFileName As Variant
  Dim x As Long
  'Assign values to the specified memory space variables
  WorkSheetList = Sheets("Instructions").Range("I3:I7").Value
  ImportFileName = Sheets("Instructions").Range("J3:J7").Value
  ' Loop through the list(n).csv list in J3:J7
   For x = 1 To UBound(WorkSheetList, 1)
    MsgBox ImportFileName(x, 1)
   Next x
End Sub

 

[sethjbr]

Hi.
If I correctly understood your issue, you want to replace "list1.csv" which is associated to the variable ImportFileName in your first code (hard coded) by the list of names from the range J3:J30 into a Loop. Is it?

@Osvaldo Palmeiro


No, sorry I apparently didn't explain it well. If my current code is ran (the working version), it will create a new data connection to the the same list1.csv with the worksheets templatecopy1, templatecopy2, templatecopy3, templatecopy4, templatecopy5.


Instead of creating the same connection to the same list for each templatecopy worksheet like this, I am looking for the the first run of the looped code to create a data connection to the CSV file that is named according to the cell contents of cell Instructions sheet I3 for the first templatecopy sheet, then for every subsequent loop until it runs out of templatecopy sheets, it will create a data connection to the next CSV file that is named in the range I3:I7.


So the result at the end of running this sub-procedure will be that templatecopy1 has a data connection to the CSV file that is named according to the value found in I3 on the Instructions sheet, templatecopy2 has a data connection to the CSV file that is named according to the value found in I4, templatecopy3 has a data connection to the CSV file that is named according to the value found in I5, templatecopy4 has a data connection to the CSV file that is named according to the value found in I6 and templatecopy5 has a data connection to the CSV file that is named according to the value found in I7.

Does that make sense?

Thank you very much for helping with this problem.

 

[sethjbr]

Hi.
Sorry, I'm probably not able to understand what you need and sorry again for my poor English.
Assuming the data on the sheet "Instructions" are like below, please run the code "testV2" and see if the connections descriptions are right.
I just changed the MsgBox on the code so it will now display both "templatecopy " versus "list . csv".
Please see if it could be useful to your original code.
Cheers

@Osvaldo Palmeiro

On the contrary, Osvaldo, you have been very helpful. Your last code did show you have a good understanding of what I am trying to accomplish. It helped a lot!

I am really close to getting this all done, but I want to ask two more favors from you.

The following code does everything correctly except the FullFileNamePath shows a mismatch error when it has the ImportFileName variant added to it's relative concatenation and the FullFileNamePath variable in the msgbox also throws up a mismatch error. How can I cancatenate this value to change relative to the value in the ImportFileName variable?

Would you please help me understand why these two variables are throwing up this error?

'Select Instructions SheetSheets("Instructions").Select


    'Specify memory space for necessary information
      Dim WorkSheetList As Variant
      Dim ImportFileName As Variant
      Dim ImportCSVPath As String
      Dim WorkBookPath As String
      Dim FullFileNamePath As Variant
      Dim WorkSheetCounter As Long
      Dim ImportFileNameCounter As Long
  
    'Assign values to the specified memory space variables
      WorkBookPath = ThisWorkbook.Path
      ImportCSVPath = "\sites_CSV_files\"
      WorkSheetList = Sheets("Instructions").Range("I3:I7").Value
      ImportFileName = Sheets("Instructions").Range("J3:J7").Value
      FullFileNamePath = WorkBookPath & ImportCSVPath & ImportFileName


        'Start counters at position 1
           WorkSheetCounter = 1
           ImportFileNameCounter = 1
   
        'This for procedure loops to all sheets except Instructions and tmp sheets
           For Each Current In Worksheets
           
                ' Avoid worksheets named "Instructions" and "tmp"
                If Current.Name <> "Instructions" And Current.Name <> "tmp" Then
                
                    ' Activate the Worksheet in the loop
                    'Current.Activate
            
                    'Display a msgbox showing which sheets will be connected to which CSV files
                    MsgBox FullFileNamePath(ImportFileNameCounter, 1) & vbLf & "     WILL BE CONNECTED TO " & vbLf & WorkSheetList(WorkSheetCounter, 1)
                    
        'Increase counters by one value after every repetition
            WorkSheetCounter = WorkSheetCounter + 1
            ImportFileNameCounter = ImportFileNameCounter + 1
            
        ' Exit for loop if this is the last value in the range
            If ImportFileNameCounter = UBound(WorkSheetList, 1) + 1 Then Exit For
            
                End If
                
            Next Current