Announcement

Collapse
No announcement yet.

VBA Concatenation of Static and Dynamic Variables into Connection String

Collapse
X
  • 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!

    Code:
    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
          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
                
                            '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...

    Comment


    • #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.,

      Comment


      • #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.

        Code:
        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
        NextI:
          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
              Else
              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
        notExists:
            WorkSheetExists = False
        End Function

        Comment


        • #5
          Kenneth Hobson

          Thank you very much for the example code. I am been playing around with it for some time now, and I have it working now. I even added a check to see whether the CSV file exists that also works really well. There is one modification that I am looking to make yet.

          1. In my case, I don't need the check to verify whether the sheet exists, because every-time I close the workbook, it deletes all worksheets except "Instructions" and "tmp". Every-time the workbook is opened, it will create all the worksheets and connections.

          How can I remove the check for the worksheet exists? I tried commenting out those two lines, that didn't work. Then I moved the "set" line from that check section to the section where it checks whether it is operating on the Instructions sheet or the tmp worksheet, but that didn't work either.

          Here is the code that I now have that is working except for this minor thing:

          Code:
          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 WorkSheetCounter As Integer
            Dim CurrentWorkSheet As Worksheet
          
            'Assign values to the specified memory space variables
            WorkBookPath = ThisWorkbook.Path
            ImportCSVPath = "\sites_CSV_files\"
            
            'Set Range Based Variables in Memory
            Set WorkSheetList = Sheets("Instructions").Range("H3:H30")
            Set ImportFileName = Sheets("Instructions").Range("J3:J30")
          
              'Start a counter that will for a maximum of 27 iterations
              For WorkSheetCounter = 1 To 27
            
                  ' If the worksheet doesn't exist, than skip the whole operation
                     If Not WorkSheetExists(WorkSheetList(WorkSheetCounter).Value2) Then GoTo NextI
                     Set CurrentWorkSheet = Worksheets(WorkSheetList(WorkSheetCounter).Value2)
                
                      ' If the WorkSheet is either Instructions or tmp, than skip this operation and jump to NextI down below
                        If CurrentWorkSheet.Name = "Instructions" Or CurrentWorkSheet.Name = "tmp" Then GoTo NextI
              
            ' For each iteration through the code, specify the entire FileNamePath for each log file
              FullFileNamePath = WorkBookPath & ImportCSVPath & ImportFileName(WorkSheetCounter)
              
             ' This line places the entire directory string for each iteration in the variable DirectoryLocation
              DirectoryLocation = Dir(FullFileNamePath)
              
                  CurrentWorkSheet.Activate
                  'CurrentWorkSheet.Range("B4").Select
              
                     ' This IF function verifies the log file exists, otherwise it will jump to the NextI section below
                      If DirectoryLocation <> "" Then
                      
                              'Setup worksheet connection to the log file located at the full file name path
                              With ActiveSheet.QueryTables.Add(Connection:="TEXT;" + FullFileNamePath, Destination:=CurrentWorkSheet.Range("$A$4"))
                                  .Name = FullFileNamePath
                                  .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
                                  
                      End If
              
              'Jump location if either the worksheet doesn't exist or the worksheet is "Instructions" or "tmp"
          NextI:
          
              'Go through the next iteration of the for loop
              Next WorkSheetCounter
            
          End Sub

          Comment


          • #6
            Just comment out the one line?
            Code:
            'If Not WorkSheetExists(WorkSheetList(WorkSheetCounter).Value2) Then GoTo NextI

            Comment


            • #7
              Kenneth Hobson

              Thank you for the suggestion. However, I tried that already. I get a subscript out of range error when I comment out just that line.

              When I click on debug, the line that is highlighted is the following:

              Code:
              Set CurrentWorkSheet = Worksheets(WorkSheetList(WorkSheetCounter).Value2)
              Do you know why?

              Comment


              • #8


                You said that you did not need the commented line. If your sheet does not exist, the line after would error...

                Check that your sheets exist in the named range and are visible.

                Comment

                Working...
                X