![]() VBA stops running in line Worksheets("hs").Names("ExternalData_1").Delete. Worksheets("hs").Range("data_value").ClearContents ![]() Dim qt As QueryTable, RngName As Name For Each qt In Worksheets ('hs').QueryTables Debug.Print qt.Name 'ExternalData1' WILL PRINT qt.Delete Next qt For Each RngName In Worksheets ('hs').Names. From below, you would see that only one 'ExternalData1' will print with Debug.Print. Note: You cannot name a cell while you are changing the contents of the cell. To test, run a Name range loop just after your QueryTables loop. Names can be up to 255 characters in length. Name box Type the name you want to use to refer to your selection. Click the Name box at the left end of the formula bar. Worksheets("hs").Names("ExternalData_1").Delete Select the cell, range of cells, or nonadjacent selections that you want to name. ![]() Worksheets("hs").QueryTables.Add(Connection:=connstring, Destination:=Range("data_value"), Sql:=sqlstring)įor Each qt In Worksheets("hs").QueryTables ![]() You only need the VBA code if you data changes often. No need to adjust the lookups or named formula. Sqlstring = "SELECT data_value FROM tb_data_values WHERE data_date='" & input_date & "'" If the option Plot only visible cells in on, select the chart and use menus Tools > Options and then look on the chart tab, then simply hiding columns with no data will make you chart correct. Function get_data(input_id As String, input_date As String) I am running a QueryTable in Excel for Mac 2016, with destination to range 'data_value', name for cell A1 in spreadsheet 'hs', QueryTable assigns the name 'ExternalData_1' to the same cell A1 after extracting the data, I want to delete name 'ExternalData_1' so I can refer to the value in 'data_value' at the end of the script but can't.
0 Comments
Leave a Reply. |