Gurus,
I recorded a macro and changed it to a loop.
Below is the part where I think should have gone wrong. Basically I forgot to use variable string qname(qname is from day1 to day10) to replace "day1" in the "Data Source=$Workbook$;Location=day1" part.
However, the code ran and gave me the right tables from datasource. I tried leaving "location=" blank or changing it to some random names and the code can't proceed and returns "can't find xxx query" error.
So this means that "Location=day1" corresponds to qname right?
But if it does correspond with qname, which is from day1 to day10 generating 10 different queries, why was code ran without error returning and feeding me the right tables????
I recorded a macro and changed it to a loop.
Below is the part where I think should have gone wrong. Basically I forgot to use variable string qname(qname is from day1 to day10) to replace "day1" in the "Data Source=$Workbook$;Location=day1" part.
However, the code ran and gave me the right tables from datasource. I tried leaving "location=" blank or changing it to some random names and the code can't proceed and returns "can't find xxx query" error.
So this means that "Location=day1" corresponds to qname right?
But if it does correspond with qname, which is from day1 to day10 generating 10 different queries, why was code ran without error returning and feeding me the right tables????
VBA Code:
ActiveWorkbook.connections.Add2 _
qname, _
"", _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=day1;Extended Properties=" _
, qname, 6, True, False