Loading to worksheet or data model in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to load data into an Excel worksheet.

Excel

QueryTable.Refresh BackgroundQuery=[1]

Drag options to blanks, or click blank then click option'

ATrue

BFalse

CNone

D0

Attempts:

3 left

💡 Hint

Common Mistakes

Using True causes the query to run asynchronously, delaying data load.

Using 0 or None are invalid for this property.

Setting BackgroundQuery to False ensures the query runs synchronously and loads data directly into the worksheet.

2fill in blank

medium

Complete the code to load data into the Excel data model using Power Query.

Excel

WorkbookConnection.ModelTable.LoadToDataModel = [1]

Drag options to blanks, or click blank then click option'

A0

BFalse

CTrue

D1

Attempts:

3 left

💡 Hint

Common Mistakes

Using 0 or 1 instead of True/False causes errors.

Setting False loads data only to worksheet, not data model.

Setting LoadToDataModel to True loads the query results into the Excel data model.

3fill in blank

hard

Fix the error in the code to correctly load data into the worksheet.

Excel

ActiveWorkbook.Queries.Add Name:="SalesData", Formula:= "let Source = Excel.CurrentWorkbook(){[Name=\"Table1\"]}[Content] in Source"
ActiveWorkbook.Connections.Add2 Name:="SalesData", _
    Description:="", _
    ConnectionString:= "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$", _
    CommandText:= Array("SELECT * FROM [SalesData]"), _
    lCmdtype:= [1]

Drag options to blanks, or click blank then click option'

AxlCmdText

BxlCmdTable

CxlCmdDefault

DxlCmdSql

Attempts:

3 left

💡 Hint

Common Mistakes

Using xlCmdSql is invalid in Excel VBA.

Using xlCmdTable expects a table name, not SQL text.

The CommandType should be xlCmdText when using SQL command text in the connection.

4fill in blank

hard

Fill both blanks to load data into worksheet and refresh it.

Excel

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Range([1]), _
    Destination:=Range("A1"))
    .QueryTable.Refresh BackgroundQuery:= [2]
End With

Drag options to blanks, or click blank then click option'

A"Table1"

BTrue

CFalse

D"Sheet1!Table1"

Attempts:

3 left

💡 Hint

Common Mistakes

Using just "Table1" may cause range not found error.

Setting BackgroundQuery True delays data loading.

The Source must be a valid range reference like "Sheet1!Table1" and BackgroundQuery should be False to refresh synchronously.

5fill in blank

hard

Fill all three blanks to load Power Query data into the data model and refresh it.

Excel

Dim cn As WorkbookConnection
Set cn = ActiveWorkbook.Connections.Add2(Name:= "PQConnection", _
    Description:= "Power Query Connection", _
    ConnectionString:= "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$", _
    CommandText:= Array("let Source = Excel.CurrentWorkbook(){[Name=\"Table1\"]}[Content] in Source"), _
    lCmdtype:= [1])
cn.ModelTable.LoadToDataModel = [2]
cn.Refresh [3]

Drag options to blanks, or click blank then click option'

AxlCmdText

BTrue

CFalse

DxlCmdDefault

Attempts:

3 left

💡 Hint

Common Mistakes

Using xlCmdDefault causes command text to be ignored.

Setting LoadToDataModel False loads only to worksheet.

Using Refresh True delays data availability.

Use xlCmdText for SQL command, set LoadToDataModel True to load into data model, and Refresh with BackgroundQuery False for synchronous refresh.