listobject

What is the best way to adjust the width of each column in a ListObject to the maximum length of its content? Currently the column width seems to correctly fit the name of the column but the contents just spill outside. I see a ListObject.QueryTable.AdjustColumnWidth but that is supposedly true...

Hello. I am having troubles with a sub routine that selects a range and converts that range to a list object. I am able to successfully create the ListObject, but when it sets the name it appends an "_" at the end of the table name if the variable ends in a number. For example: Dim tableName...

Hello Everyone, Can anyone suggest a clean way of updating an excel table from an array that includes new column headings? I've got a macro that outputs an array of column headings and values to a spreadsheet, and then formats the output as a table. When updating the information using the...

I need to be able to submit an XML request to a WSDL, get the XML response and deal with it. The manual way is that I go into SOAP-UI, load up a request, supply parameters, click the green triangle, get the response, and save it as an XML file. Then in Excel I import it using Data / From Other...

Hi, I am attempting to create a form that for time and complexitys sake I am not using Infopath, and have got to the point where I need my excel spreadsheet to write its' form fields to a sharepoint list. I have the code below to do this: Public Function uploadForm() ' Get the collection...

I've got a couple different scenarios where I need help with writing vba for. I know I can do this via excel formulas and a manual approach, but I need to do it as a macro because my company's admin staff will need to do this on a daily basis so an automated works best. In my first situation...

I have an Excel file with a table (Listobject) that has a column called "Project". In the Project field, there are many varying values and the list is always expanding. Some records in the table have a value for Project called "N/A" and I'm trying to create a macro that automatically filters...

I have a table (ListObject) in Excel 2007 with a total of 10 columns. I want to fill this table with data from a XML file with other column names. I import the XML data with this code: Public Sub ImportXML() ActiveWorkbook.XmlImport URL:=Application.GetOpenFilename(FileFilter:="XML Files...

I have a table in Excel 2007 looking like <colgroup><col span="2"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody> Identification Specification Budget Prognosis Acounting Dimension_1 Dimension_2 Name Category Description Function Creditor_ID...

I have the following VBA code which is failing with an "incomplete datasource" error on the last line: Application.ScreenUpdating = False Worksheets(Import).Select Range("F10").Select Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False The name of the ListObject is...

Normally I lock a column or row as "$A1" or "A$1", but when I use the names of my columns in a ListObject, eg. "SUM(MyTableName[Amount])", and copy the column it will change the name "Amount" to the column name just next to the column named "Amount". Is it possible to lock the column name?

Can I protect a sheet without protecting an entire table? I will still have the option to insert, delete, and edit rows inside a table.

Using Excel 2010 I want to set the connection of a ListObjects.QueryTable to and ADO source. Under MSDN's info on QueryTable.Connection property they say that "Alternatively, you may choose to access a data source directly by using the Microsoft ActiveX Data objects (ADO) library Instead" of...

How can I delete a row in a table in Excel 2007 based on the row number from ActiveCell. I can't delete the row with ActiveCell.EntireRow.Delete without checking if the row is in a given ListObject.

I can see that I'm not allowed to delete rows from a list when it's filtered. Do I have any options to avoid this restriction?

Hi, I've got a table with a lot of raw data which looks something like: == TypeID, Amount -- 1, 200 1, 300 1, 100 2, 400 2, 500 8, 300 == and I want another table in another sheet in the same workbook with data like this: == TypeID, Amount -- 1, 600 2, 900 3, 0 4, 0 5, 0 6, 0 7, 0 8, 300 9...

Since Excel 2007 querytables are part of a Listobject. I've been able to succesfully alter the CommandText and Connection property using VBA For Each ws In ActiveWorkbook.Sheets For Each lo In ws.ListObjects If lo.SourceType = XlListObjectSourceType.xlSrcQuery Then...

Hi, I got a table in Excel 2007 like this <TABLE style="WIDTH: 432pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=576><COLGROUP><COL style="WIDTH: 48pt" span=9 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid...

I want the value of the first column in the active row in a ListObject. I've tried MsgBox ActiveCell.ListObject.ListRows(ActiveCell.Row).Range.Address, but don't know how to refer to the first column in this row. I've also tried MsgBox...

How can I create a SQL query based on the column names and values in each row? I've tried to search for a simple and free Excel based database management system, but couldn't find anything. I want to perform the action which i've tried to describe in codes below. For each row in a ListObject I...