No announcement yet.

VBA Concatenation of Static and Dynamic Variables into Connection String

  • Filter
  • Time
  • Show
Clear All
new posts

  • VBA Concatenation of Static and Dynamic Variables into Connection String

    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:

    Instructions tmp

    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
        '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
          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
                            '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

  • #2
    Welcome to the forum! Concatenate strings, not a string and an array. ImportFileName is an array. You probably want another loop to iterate each cell in that range or each element in the array...


    • #3
      Kenneth Hobson

      Thank you very much for the idea. However, I already have a counter that increases each iteration with the ImportFileNameCounter so, how do I go about adding another loop within these this loop that iterates each element in the array.

      Can you provide some example code from another context that would illustrate how to nest multiple for loops into a macro that also excludes running the loop on certain worksheets? Whenever I tried adding a second loop to the code, getting it to work on every worksheet except "tmp" and "Instructions" was a problem for me.

      Thank you very much.,


      • #4

        I added a check for the worksheet existing or not. You should also add a check for the CSV file existing or not. Dir() is the usual method.

        I did not add the import section. It is easily done though. I seldom find need for Activate or Select. e.g. Rather than ActiveSheet, I would use ws in this example. Note how I used A4 as well.

        The Debug.Print lines put the output of a Run into VBE's Immediate Window. I did that to show how the variables resolved. You can delete those lines.

        Sub SetupWorkBookConnections()
          'Specify memory space for necessary information
          Dim WorkSheetList As Range
          Dim ImportFileName As Range
          Dim ImportCSVPath As String
          Dim WorkBookPath As String
          Dim FullFileNamePath As String
          Dim i As Integer, ws As Worksheet
          'Assign values to the specified memory space variables
          WorkBookPath = ThisWorkbook.Path
          ImportCSVPath = "\sites_CSV_files\"
          Set WorkSheetList = Sheets("Instructions").Range("H3:H30")
          Set ImportFileName = Sheets("Instructions").Range("J3:J30")
          'Iterate WorkSheetList
          For i = 1 To 27
            If Not WorkSheetExists(WorkSheetList(i).Value2) Then GoTo NextI
              Set ws = Worksheets(WorkSheetList(i).Value2)
            If ws.Name = "Instructions" Or ws.Name = "tmp" Then GoTo NextI
            FullFileNamePath = WorkBookPath & ImportCSVPath & ImportFileName(i)
            Debug.Print i, ws.Name, ws.[A4].Address(external:=True)
            Debug.Print ImportFileName(i), FullFileNamePath
          Next i
        End Sub
         'WorkSheetExists in a workbook:
        Function WorkSheetExists(sWorkSheet As String, Optional sWorkbook As String = "") As Boolean
            Dim ws As Worksheet, wb As Workbook
            On Error GoTo notExists
            If sWorkbook = "" Then
              Set wb = ActiveWorkbook
              Set wb = Workbooks(sWorkbook) 'sWorkbook must be open already.  e.g. ken.xlsm, not x:\ken.xlsm.
            End If
            Set ws = wb.Worksheets(sWorkSheet)
            WorkSheetExists = True
            Exit Function
            WorkSheetExists = False
        End Function