VBA Code to control Data Connectin from CSV File

[Danhardy]

Hi,

I have a code that was kindly given to me to help me control a csv based data connection

Sub UpdData()    Dim PCon As String
    PCon = "TEXT;" & Sheets("Data Connections").Range("B3").Value
    With Sheets("Pool").Range("A1").QueryTable
        .Connection = PCon
        .TextFileCommaDelimiter = True
        .Refresh BackgroundQuery:=False
    End With
    ActiveWorkbook.Connections("Pool").Refresh End Sub

The Data goes onto sheet "Pool" based on the file source listed on "Data Connections" B3 but i cant seem to get the code working, it fails at

With Sheets("Pool").Range("A1").QueryTable

Any Help would be great, the Idea is that on open

User Prompted by Input (results in B1)
the result in B1 selects the required csv file path by vlookup
the code generates the connection based on the file path. this would be repeated four times
Pool in B3
Schedule in B4
Vehicle in B5
KPI in B6

I have attached the file for any input

Thanks

Danny

[p45cal]

That's because there is no querytable there!
Connect manually once first selecting cell A1 of the Pool tab then using the Data tab of the ribbon, from Text etc. and make sure it's called "Pool" (if there isn't already another connection of that name, or comment-out the line: ActiveWorkbook.Connections("Pool").Refresh).