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:

    [TABLE="border: 1, cellpadding: 1, width: 250"]










    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!

  • [USER="17735"]Kenneth Hobson[/USER]

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

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

  • [USER="17735"]Kenneth Hobson[/USER]

    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:

  • [USER="17735"]Kenneth Hobson[/USER]

    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:

    1. Set CurrentWorkSheet = Worksheets(WorkSheetList(WorkSheetCounter).Value2)

    Do you know why?