Greetings Ozgrid Excel Extraordinaires!
My objective is to setup excel connections to CSV files that are located on a shared drive. I have an excel file with the following two worksheets:
[TABLE="border: 1, cellpadding: 1, width: 250"]
[tr][td]Instructions
[/td][td]tmp
[/td][/tr]
[/TABLE]
I have a macro that will create as many worksheets based upon the tmp worksheet template as their are values in H3:H30 of the Instructions worksheet.
Now, I am trying to setup a macro that will create a different connection from each of these template copy worksheets to a CSV file on a shared drive that is named according to the values in the cell range of J3:J30.
My code almost works, but I am having difficulty with defining a variable that concatenates both static and dynamic values based upon the array of values in J3:J30 on the Instructions sheet. Excel is throwing up a "type mismatch" error message for the bolded line: FullFileNamePath = WorkBookPath & ImportCSVPath & ImportFileName
Here is the code that I have that is soo close! Any assistance would be greatly appreciated. Thanks!
Sub SetupWorkBookConnections()
'Select Instructions Sheet
Sheets("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("H3:H30").Value
ImportFileName = Sheets("Instructions").Range("J3:J30").Value
[B] FullFileNamePath = WorkBookPath & ImportCSVPath & ImportFileName[/B]
'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
'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 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
End Sub
Display More