none
Download from SharePoint Tables RRS feed

  • Question

  • Hi

    I have inherited some code which downloads lists from SharePoint in into Excel via a QueryTables command - see below.I partially understand this - though i don't really understand the commands .Refreshstyle onwards and what object they relate to.

    Also, it appears that tables are insert by this action - see Names Manager screenshot.  i cannot access Tables 2 to 5 in Name Manager - it is greyed out - not sure why.

    Can anyone help me but explaing how this works - in easy terms please, I am a simple soul!!

    many thanks

    Peter 

    Thursday, August 3, 2017 8:52 AM

Answers

  • ActiveSheet.QueryTables.Add(Connection:=pstrDataSource, Destination:=Range(pstrTargetCell)

    creates a query that can retrieve data from the data source specified in the string variable pstrDataSource, and place it into the range starting at the cell whose address is given by the string variable pstrTargetCell.

    QueryTables.Add returns a QueryTable object that represents the method used to retrieve the data; this is useful if you want to refresh (update) the query results later on. The following lines set various properties of this QueryTable object, and call one method.

    RefreshStyle can be one of the following values:

    • xlInsertDeleteCells means that Excel will insert or delete cells as needed to make room for the query results. So existing data will not be overwritten; data adjacent to the query results will be left in place.
    • xlOverwriteCells means that Excel will place the query results over whatever data were already there, overwriting those data.
    • xlInsertEntireRows means that Excel will insert as many entire rows as needed to make room for the query results. So data that were adjacent to where the query results will be, are shifted down.

    FillAdjacentFormulas can be True or False. If True, formulas in cells to the right of the query result will be updated automatically when the query results are refreshed.

    RefreshOnFileOpen can be True or False. If True, the query results will be automatically refreshed each time the workbook is opened.

    PreserveFormatting can be True or False. If True, formatting applied to the first few rows of the query results will automatically be applied to new rows when the query is refreshed.

    HasAutoFormat can be True or False. If True, the query results are formatted automatically when the query is refreshed.

    BackgroundQuery can be True or False. If True, you can keep on using Excel while the query is being refreshed, otherwise you have to wait until refreshing is finished.

    TablesOnlyFromHTML is an "old" property kept for legacy purposes. It determines how data from web pages are handled.

    Refresh BackgroundQuery:=False "runs" the query.

    SavePassword can be True or False. If True, you have to enter a password (if any!) only once, it will be remembered.

    SaveData can be True or False. If True, all data that are retrieved are stored in the query (in addition to pasting them into the worksheet). If False, only the definition of the query (the "recipe") is stored.

    The query results will automatically be a table (the kind of object that you can create by clicking Table on the Insert tab of the ribbon).

    Since tables resulting from a data query depend on the query definition, you cannot edit them the normal way in Name Manager.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    • Marked as answer by py1 Thursday, August 3, 2017 12:10 PM
    • Edited by Hans Vogelaar MVPMVP Thursday, August 3, 2017 12:36 PM
    Thursday, August 3, 2017 12:04 PM

All replies

  • ActiveSheet.QueryTables.Add(Connection:=pstrDataSource, Destination:=Range(pstrTargetCell)

    creates a query that can retrieve data from the data source specified in the string variable pstrDataSource, and place it into the range starting at the cell whose address is given by the string variable pstrTargetCell.

    QueryTables.Add returns a QueryTable object that represents the method used to retrieve the data; this is useful if you want to refresh (update) the query results later on. The following lines set various properties of this QueryTable object, and call one method.

    RefreshStyle can be one of the following values:

    • xlInsertDeleteCells means that Excel will insert or delete cells as needed to make room for the query results. So existing data will not be overwritten; data adjacent to the query results will be left in place.
    • xlOverwriteCells means that Excel will place the query results over whatever data were already there, overwriting those data.
    • xlInsertEntireRows means that Excel will insert as many entire rows as needed to make room for the query results. So data that were adjacent to where the query results will be, are shifted down.

    FillAdjacentFormulas can be True or False. If True, formulas in cells to the right of the query result will be updated automatically when the query results are refreshed.

    RefreshOnFileOpen can be True or False. If True, the query results will be automatically refreshed each time the workbook is opened.

    PreserveFormatting can be True or False. If True, formatting applied to the first few rows of the query results will automatically be applied to new rows when the query is refreshed.

    HasAutoFormat can be True or False. If True, the query results are formatted automatically when the query is refreshed.

    BackgroundQuery can be True or False. If True, you can keep on using Excel while the query is being refreshed, otherwise you have to wait until refreshing is finished.

    TablesOnlyFromHTML is an "old" property kept for legacy purposes. It determines how data from web pages are handled.

    Refresh BackgroundQuery:=False "runs" the query.

    SavePassword can be True or False. If True, you have to enter a password (if any!) only once, it will be remembered.

    SaveData can be True or False. If True, all data that are retrieved are stored in the query (in addition to pasting them into the worksheet). If False, only the definition of the query (the "recipe") is stored.

    The query results will automatically be a table (the kind of object that you can create by clicking Table on the Insert tab of the ribbon).

    Since tables resulting from a data query depend on the query definition, you cannot edit them the normal way in Name Manager.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    • Marked as answer by py1 Thursday, August 3, 2017 12:10 PM
    • Edited by Hans Vogelaar MVPMVP Thursday, August 3, 2017 12:36 PM
    Thursday, August 3, 2017 12:04 PM
  • Hans

    Thanks very much for such a comprehensive answer - it is much appreicated.

    kind regards,

    Peter

    Thursday, August 3, 2017 12:10 PM