PDA

View Full Version : VBA Code to control Data Connectin from CSV File



Danhardy
03-24-2016, 07:47 AM
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
03-24-2016, 10:57 AM
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).