[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Excel 2010: "Cannot make changes to a table or XML mapping when multiple sheets are selected".

Posted on 2014-01-29
16
Medium Priority
?
4,225 Views
Last Modified: 2014-02-27
Hi Experts,
We have a user who creates Excel 2010 workbooks pulling querys from existing data on Access and other Excel workbooks. These workbooks have a number of worksheets.

When he wants to make any edit to all the worksheets simultaneously  (such as inserting a column on worksheet 1 and having the column insert on all of the worksheets while having all of the worksheets selected [grouped]) he receives the error:
"Cannot make changes to a table or XML mapping when multiple sheets are selected".

He was able to do this in previous versions of Excel (our most recent previous being in Office 2007).
I checked on as many online forums as I could find but could not get a satisfactory response as to why this error occurs or if there is a way to do this operation in Excel 2010.
 
One of the most unhelpful answers (this from a "Microsoft Support Engineer" on http://answers.microsoft.com/en-us/office/forum/office_2010-excel/error-cannot-make-changes-to-a-table-or-xml/8c0b1c2f-4ad0-4df1-a700-862a637a1e3e) was:
"Editing data from a table selecting multiple sheets is not possible".

Is there another answer that you know of?

Thanks!
0
Comment
Question by:Loubot
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 3
16 Comments
 
LVL 46

Expert Comment

by:aikimark
ID: 39820843
Have you introduced a table into one of the selected worksheets or changed the order of the worksheets (in case he is selecting a range of worksheets)?
0
 

Author Comment

by:Loubot
ID: 39824391
Hi aikimark:
Apparently he has not changed the order of the worksheets.  As for the first question he's not quite sure what it is referring to; each worksheet is populated with data using an identical Access database query except for unique identifiers SAMPNO, DUP_SAMPNO, MW_ID, and Col_Date.  He says he has not introduced anything else into any of the worksheets.
Thanks for your help!
0
 
LVL 46

Expert Comment

by:aikimark
ID: 39825425
Is he selecting all the worksheets?  -- he should not, especially those worksheets that include any tables.

You might have to look over his shoulder and report what you see in this thread.

While you are there, you might want to experiment with hiding some of the worksheets that contain tables before he begins his grouped changes and see if the problem still exists.
0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 

Author Comment

by:Loubot
ID: 39840287
Selecting fewer worksheets does not change the inability to insert columns or data across the sheets. He doesn't have tables in the worksheets. The data is being pulled from Access DBs. I'm stumped.
Thanks!
0
 
LVL 46

Expert Comment

by:aikimark
ID: 39840345
If there are no tables in the selected worksheets (mix), then the error message he is receiving seems out of place.
0
 

Author Comment

by:Loubot
ID: 39877891
I'm still unable to find a solution for this user and the Microsoft support site is no help at all. If there is no one who has a solution for this here I'll just pass on that I'm still searching to the user.
thanks anyway.
0
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 39878161
Hi there,

There is absolutely no way to do this with tables, and it is by design. You have to do them one at a time. The only way to do this is to convert the tables to ranges. Or use code to do it.

Regards,
Zack Barresse
1
 

Author Comment

by:Loubot
ID: 39878275
When you say "tables" could that also include data retrieved from Access databases? He says he doesn't have "tables" in any of the worksheets but the data is populated by these databases.
Thanks!
0
 
LVL 14

Accepted Solution

by:
Zack Barresse earned 2000 total points
ID: 39878299
Data imported from an Access database will either need to be in a table, PivotTable or PivotChart (or just the connection), or of course just added to the data model (i.e. Power Pivot). So if it's on the worksheet and it's not a Pivot of any sort, it must be a table.

The easiest way to check if it's a table is, with any cell in the data range selected, if you see the yellow contextual ribbon tab up in your ribbon titled TABLE TOOLS, it's a table, otherwise it's just a range.

Zack
1
 

Author Comment

by:Loubot
ID: 39878373
Zack,
I've left a message with this user (we're a municiplity and we have offices scattered all over the city) to contact me so we can determine if this is indeed the case. I'll let you know soon and hopefully will conclude this issue.
Thanks much!
0
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 39878391
Sounds good. You're very welcome. :)

Zack
0
 

Author Closing Comment

by:Loubot
ID: 39893005
This answer was the most clear and informational so that I could relay the information to the user in a way he could understand. The user is, of course, not happy that this change has been made to the functionality of Excel from previous versions but is accepting that his work-around will have to suffice for his purposes. Thanks!
0
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 39893109
Thanks very much. Just to clarify, since the inception of tables this has never been allowed. The only way you can do this is if they are not tables. You could do it with code fairly easily though.

Here is a routine to do just that...

Sub TestInsertDefault()
    Call InsertColumnInAllTablesInWorkbook
End Sub

Sub TestInsertSpecificPosition()
    Call InsertColumnInAllTablesInWorkbook(1)
End Sub

Sub TestInsertSpecificName()
    Call InsertColumnInAllTablesInWorkbook(, "TEST")
End Sub

Sub InsertColumnInAllTablesInWorkbook(Optional ByVal ColumnIndex As Long = 0, _
                                      Optional ByVal ColumnName As String = "New Column")
    Dim WB As Workbook
    Dim WS As Worksheet
    Dim TB As ListObject
    Dim ColumnInsert As Long
    Set WB = ActiveWorkbook
    For Each WS In WB.Worksheets
        For Each TB In WS.ListObjects
            If ColumnIndex > TB.ListColumns.Count Then GoTo NextTable
            ColumnInsert = ColumnIndex
            If ColumnIndex = 0 Then ColumnInsert = TB.ListColumns.Count + 1
            On Error GoTo NoAddedColumn
            TB.ListColumns.Add ColumnInsert
            TB.HeaderRowRange(1, TB.ListColumns(ColumnInsert).Index).Value = ColumnName
            GoTo NextTable
NoAddedColumn:
            'error trap here if desired
NextTable:
        Next TB
    Next WS
End Sub

Open in new window


There are a few examples at the top of how you can call this routine. Leaving the first parameter (ColumnIndex) out will give a default value of 0, which will make the code always insert it as the last column. Specifying a number will insert it in that location. If that location (column index) cannot be found it skips the table, presumably because there aren't that many columns in the table.

Specifying the second parameter is the name you want to give the column. Leaving it blank will just leave it as the default name Excel will give it. Please note if you have another column named the same thing to the left of the inserted column, the current column you're inserting will get a suffix of 2, the third column will get a suffix of 3, and so on. If you have another column named the same thing to the right of the inserted column it will leave that name the same and rename the column with the same name on the right to the next highest increment available. This is by design.

This may not be perfect, but it's a workaround to inserting columns in multiple tables.

HTH
Zack
0
 

Author Comment

by:Loubot
ID: 39893257
Hmmm...He seems certain that he had been able to do this before in Excel 2007. It's just since our organization had updated to Office 2010 (a few years ago) that he hasn't been able to perform this function in the Excel workbooks that he has worked with from those previous versions.
Thanks again for the above information. I'll pass it on.
0
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 39893297
Probably what happened was the data in the 2007 file was indeed a table at one point, but then got converted to a range, which leaves all formatting in place. Sometimes it's difficult to tell if it's a table or not, best way is to look at the ribbon when you select it, see if you get the contextual TABLE TOOLS ribbon tab show up.

Good luck. :)

Zack
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question