Macro to Import Multiple CSV files into specific Excel tabs

[Mencae]

Hello,

I have a project that I'm hoping to get help with.

I need to import multiple CSV files into specific Macro Enabled Workbook that already has uniquely named Worksheet tabs. I have code to do this for 1 CSV file into 1 worksheet tab. However I have 7 CSV files (always the same CSV file name) that need to go into 7 specific tabs.

Here are some details to help:

1. A job on an External Server runs a job creating the 7 CSV files.
2. The files are saved in (let's say) L:\CSV_OutPut
3. The file names are 556.csv, 557.csv, 556.csv, 557.csv, 558.csv, 559.csv, 560.csv, 561.csv, 563.csv

The Excel workbook is: L:\Queues\Queue_Report.xlsm

1. The workbook has tabs called: Data_556, Data_557, Data_558, Data_559, Data_560, Data_561, Data_563
2. So the 556.csv file needs to be imported into tab Data_556 (and the same follows for the Other CSV's into their similarly named TAB)
3. Each CSV file will have its data imported into its corresponding Tab

The code I have tested to do this so far (that I borrowed from this site) allows me to run the macro, manually find the 556.csv file, and it will import the CSV data into the Data_556 tab perfectly.

Since the CSV file names, as well as the Worksheet tab names will never change, I'd like to hard-code this into the macro. Can this be done for all the files I've listed in the the way I've described above?

Here is the code I'm working with right now:

Sub load_csv()
Dim fStr As String

With Application.FileDialog(msoFileDialogFilePicker)
.Show
If .SelectedItems.Count = 0 Then
MsgBox "Cancel Selected"
Exit Sub
End If
'fStr is the file path and name of the file you selected.
fStr = .SelectedItems(1)
End With

With ThisWorkbook.Sheets("Data_556").QueryTables.Add(Connection:= _
"TEXT;" & fStr, Destination:=Range("$A$1"))
.Name = "CAPTURE"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False

End With
End Sub

Any suggestions?
Thanks in advance to all you experts