Create new sheets based on a list, and populate those sheets with three .txt files that need an inse-CodePudding

Here is a flowchart of what I am trying to accomplish:

I have a list of cities:

Tokyo
Delhi
Shanghai
Sao Paulo
Mexico City
Cairo
Mumbai
Beijing
Dhaka
Osaka

Each of these cities has three .txt files pertaining to each in this format:

PopulationFile{}.txt
LocationFile{}.txt
CityFile{}.txt

Here is the complete list of files that are in the same folder:

PopulationFileTokyo?.txt
PopulationFileBeijing?.txt
PopulationFileCairo?.txt
PopulationFileDelhi?.txt
PopulationFileDhaka?.txt
PopulationFileMexico City?.txt
PopulationFileMumbai?.txt
PopulationFileOsaka.txt
PopulationFileSao Paulo?.txt
PopulationFileShanghai?.txt
LocationFileTokyo?.txt
LocationFileShanghai?.txt
LocationFileSao Paulo?.txt
LocationFileOsaka.txt
LocationFileMumbai?.txt
LocationFileMexico City?.txt
LocationFileDhaka?.txt
LocationFileDelhi?.txt
LocationFileCairo?.txt
LocationFileBeijing?.txt
CityFileTokyo?.txt
CityFileShanghai?.txt
CityFileSao Paulo?.txt
CityFileOsaka.txt
CityFileMumbai?.txt
CityFileMexico City?.txt
CityFileDhaka?.txt
CityFileDelhi?.txt
CityFileCairo?.txt
CityFileBeijing?.txt

Here's the goal:

Create sheets based on a list of values (in this case being Cities)
Use a range to iterate through specific file names (an example being PopulationFileTokyo.txt, then LocationFileTokyo.txt, CityFileTokyo.txt) to paste to Column 1, Column 2, and Column 3 on the {Cities} sheet (example Sheetname: Tokyo)
Loop insert .txt file data process for each city

Using a new Workbook, I want to automate this process. I want to

define the city list in VBA,
then, instruct VBA to create sheets based on this list of cities,
next, iterate through these sheets pasting the three relevant files for each city in the first three columns The script should insert the city name from Cities into the file path/name to correctly insert relevant files to the relevant sheets (as shown after the 'Insert .txt files into columns' box in the flowchart) Only the file name in the file path needs to be edited as all of these files are in the folder

Here is a Macro I created showing manual steps of this process (creating three sheets, then going back and inserting the relevant files):

Sub Macro3()
'
' Macro3 Macro
' Name sheet, create sheet, create sheet, insert data, insert data, insert data, switch sheet, insert data, insert data, insert data
'

'
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "Tokyo"
    Sheets.Add After:=ActiveSheet
    Sheets("Sheet2").Select
    Sheets("Sheet2").Name = "Delhi"
    Sheets.Add After:=ActiveSheet
    Sheets("Sheet3").Name = "Shanghai"
    Sheets("Tokyo").Select
    Application.CutCopyMode = False
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;/Users/MyName/PycharmProjects/pythonProject7/PopulationFileTokyo" & Chr(10) & ".txt" _
        , Destination:=Range("$A$2"))
        .Name = "PopulationFileTokyo" & Chr(10) & ""
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .RefreshPeriod = False
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 10000
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Range("B2").Select
    Application.CutCopyMode = False
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;/Users/MyName/PycharmProjects/pythonProject7/LocationFileTokyo" & Chr(10) & ".txt" _
        , Destination:=Range("$B$2"))
        .Name = "LocationFileTokyo" & Chr(10) & ""
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .RefreshPeriod = False
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 10000
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Range("C2").Select
    Application.CutCopyMode = False
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;/Users/MyName/PycharmProjects/pythonProject7/CityFileTokyo" & Chr(10) & ".txt" _
        , Destination:=Range("$C$2"))
        .Name = "CityFileTokyo" & Chr(10) & ""
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .RefreshPeriod = False
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 10000
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Sheets("Delhi").Select
    Range("A2").Select
    Application.CutCopyMode = False
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;/Users/MyName/PycharmProjects/pythonProject7/PopulationFileDelhi" & Chr(10) & ".txt" _
        , Destination:=Range("$A$2"))
        .Name = "PopulationFileDelhi" & Chr(10) & ""
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .RefreshPeriod = False
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 10000
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Range("B2").Select
    Application.CutCopyMode = False
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;/Users/MyName/PycharmProjects/pythonProject7/LocationFileDelhi" & Chr(10) & ".txt" _
        , Destination:=Range("$B$2"))
        .Name = "LocationFileDelhi" & Chr(10) & ""
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .RefreshPeriod = False
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 10000
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Range("C2").Select
    Application.CutCopyMode = False
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;/Users/MyName/PycharmProjects/pythonProject7/CityFileDelhi" & Chr(10) & ".txt" _
        , Destination:=Range("$C$2"))
        .Name = "CityFileDelhi" & Chr(10) & ""
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .RefreshPeriod = False
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 10000
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Sheets("Shanghai").Select
    Range("A2").Select
    Application.CutCopyMode = False
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;/Users/MyName/PycharmProjects/pythonProject7/PopulationFileShanghai" & Chr(10) & ".txt" _
        , Destination:=Range("$A$2"))
        .Name = "PopulationFileShanghai" & Chr(10) & ""
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .RefreshPeriod = False
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 10000
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Range("B2").Select
    Application.CutCopyMode = False
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;/Users/MyName/PycharmProjects/pythonProject7/LocationFileShanghai" & Chr(10) & ".txt" _
        , Destination:=Range("$B$2"))
        .Name = "LocationFileShanghai" & Chr(10) & ""
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .RefreshPeriod = False
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 10000
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Range("C2").Select
    Application.CutCopyMode = False
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;/Users/MyName/PycharmProjects/pythonProject7/CityFileShanghai" & Chr(10) & ".txt" _
        , Destination:=Range("$C$2"))
        .Name = "CityFileShanghai" & Chr(10) & ""
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .RefreshPeriod = False
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 10000
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

Finally, the outcome should be 10 sheets named [Cities] with Column 1 containing PopulationFile{Cities}.txt, Column 2 containing LocationFile{Cities}.txt, and Column 3 containing CityFile{Cities}.txt. 30 .txt files should be imported into their proper sheets in the end.

Here is Python code that executes creating these files with the content written inside:

Cities.txt
Tokyo
Delhi
Shanghai
Sao Paulo
Mexico City
Cairo
Mumbai
Beijing
Dhaka
Osaka

readNames = open('Cities.txt', 'r').readlines()
print(readNames)
for i in readNames:
    open('PopulationFile{}.txt'.format(i), 'w').write('Pop1\nPop2\nPop3')
    open('LocationFile{}.txt'.format(i), 'w').write('Loc1\nLoc2\nLoc3')
    open('CityFile{}.txt'.format(i), 'w').write('City1\nCity2\nCity3')

If any other information is needed or further clarification please ask!

CodePudding user response：

Please, test the next code. But take care to eliminate the strange character from the end of the files name (a question mark in the text you show, and Chr(10) in your code, which is not allowed. A file name placed on two lines is not allowed by Windows...). So, PopulationFileTokyo?.txt should become PopulationFileTokyo.txt and so on. And read carefully the code lines comments before running it:

Sub testPopulateCities()
   Dim wb As Workbook, foldPath As String, fileName As String, arrTxt
   Dim arrC, cit, arrCol, cl, colNo As Long, sheetN As String, i As Long
   
   'fill the cities array:
   arrC = Split("Tokyo,Delhi,Shanghai,Sao Paulo,Mexico City,Cairo,Mumbai,Beijing,Dhaka,Osaka", ",")
   arrCol = Split("Population,Location,City", ",") 'array of prefixes with meaning related to the insertion column
   
   Set wb = Workbooks.Add(xlWBATWorksheet) 'create a new workbook with a single sheet
   'insert the necessary sheets and naming them according to arrC array elements:
   For Each cit In arrC
        i = i   1
        If i = 1 Then
            wb.Sheets(i).Name = cit                                                     'name the first sheet
        Else
            wb.Sheets.Add(After:=wb.Sheets(wb.Sheets.Count)).Name = cit 'insert and name the following sheets
        End If
   Next cit

   foldPath = "C:\Users\RealName\PycharmProjects\pythonProject7\" 'The folder path where the text files exist. Take care of its ending backslash (\)!!!
   
   fileName = dir(foldPath & "*.txt")              'determine the first file in the folder
   Do While fileName <> ""
         For Each cl In arrCol 'iterate between the array elements
            If InStr(fileName, cl) > 0 Then 'if the element exists in the file name:
                colNo = Application.match(cl, arrCol, 0)            'column where to insert data
                sheetN = Split(fileName, ".")(0)
                sheetN = Right(sheetN, Len(sheetN) - (Len(cl)   4)) 'sheet name to insert data
                Exit For
            End If
         Next
         'place the text file content in an array, split by vbCrLf (Carriage return - linefeed combination):
         arrTxt = Split(CreateObject("Scripting.FileSystemObject").OpenTextFile(foldPath & fileName, 1).ReadAll, vbCrLf)
         wb.Sheets(sheetN).cells(2, colNo).Resize(UBound(arrTxt)   1, 1) = Application.Transpose(arrTxt) ' drop the array content
    
        fileName = dir()                        'continue the iteration between files
   Loop
   MsgBox "Ready...", vbInformation, "Job done"
End Sub