2

I'd like to be able to access an Excel External ODBCConnection Query Parameters using VBA.

The Connection definition window showing the connection query (command text) is also showing a Parameters... button. Clicking on it shows the "Parameters" window.

Is it possible to access the Parameter attributes, namely that of the "Get value from the following cell"?

enter image description here

enter image description here

Community
  • 1
  • 1
tamersalama
  • 4,093
  • 1
  • 32
  • 35

1 Answers1

2

I think I got an answer. According to Microsoft's Query Table documentation

If you import data using the user interface, data from a Web query or a text query is imported as a QueryTable object, while all other external data is imported as a ListObject object.

I can't access QueryTable from the ODBCConnection object - however - a ListObject can be accessed from the Worksheet, that ListObject contains QueryTable, which in turn contains the Parameters object containing a list of all Parameters

ActiveWorkbook.Worksheets("Sheet1").ListObjects(1).QueryTable.Parameters

parameter SourceRange will return a Range (if the Parameter type is xlRange) - setting or changing the SourceRange however is done by setting the whole Parameter again:

param.SetParam xlRange, Workbooks(1).Worksheets(1).Range("A1")
tamersalama
  • 4,093
  • 1
  • 32
  • 35