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

  • Kenneth Hobson
    replied
    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.

    Leave a comment:


  • sbrauning
    replied
    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?

    Leave a comment:


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

    Leave a comment:


  • sbrauning
    replied
    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

    Leave a comment:


  • Kenneth Hobson
    replied
    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

    Leave a comment:


  • sbrauning
    replied
    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.,

    Leave a comment:


  • Kenneth Hobson
    replied
    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...

    Leave a comment:

Working...
X