Export and (reimport) shape data to do bulk changes in Excel - is there a simple and reliabel option?

>>> It very detailed :)

Yeah -- I got into figuring out a way to make this work and might have gotten carried away! But I tried every step as I worked through it and it does work. Give it a try when you have time and I think you'll find it's not so bad.

thx for the detailed explanation!
It very detailed :)

I'll try it when I have the patient again to tackle this :)

First, thanks for providing a very complete description of what you've tried, what worked, and what didn't.

Second, I'm going to propose a different technique that may allow you to accomplish what you need. And while I don't think the issues you described above have anything to do with using the German version of Visio, I'm certain that the steps I'm proposing are language independent.

My suggestion avoids use of the ODBC mechanism for exporting data and uses a simple technique that gives you control over exactly which fields you export and import. The basic steps are these:

1. Use the Visio report writer to create a custom report that will export your chosen fields.
2. Run the custom report and choose Excel as the output option.
3. Make your desired data changes in Excel, including updating Tag numbers.
4. Use Custom Import on the Data tab to link to the modified Excel data file.
5. Use autolink to connect each shape to its corresponding row of data by means of a unique ID.

Two notes:

1. Steps 4 and 5 are the same as what you described above.
2. My technique, like yours, requires that each shape have a unique ID. As I suggested in this thread, using Visio's internal shape ID to uniquely identify each shape only works if you know the shape ID will never be altered. However, as you've discovered, there are operations like the Change Shape function, that do change the shape ID. Consequently, you may want to assign a unique ID to each shape rather than using the internal ID. It is more work up front to assign IDs manually, but it leaves you in charge of the IDs rather than leaving them to chance.

Preparation

Two facts combine to create the need to tinker with Shape Data and User cells: 1) Data linking via Custom Import can only feed data values to Shape Data fields, not to User cells. 2) The P&ID add-on uses a user cell called PETagNumber to store the tag number.

Consequently, you'll need to edit master shape(s) to 1) add a Shape Data field to receive a value from data linking and 2) enter a formula in the PETagNumber User cell to get its value from the new shape data field. The result will look something like this:

You'll also want to add a Shape Data field for your shape ID if one doesn't already exist in your master shapes (refer to Prop.ID above).

Details

0. Enter a unique value for the ID field in each shape.
1. With your P&ID diagram open, select Shape Reports on the Review tab.
2. It's probably easier to start with one of the built-in reports, e.g., select the "Valve List" report and then select Modify.

3. Click the Advanced button on the first page of the report wizard to display the shape selection criteria for this report. The first line in the Defined criteria section limits shape selection to those that include a User cell identifying the P&ID solution (it's an add-on to the core Visio code in the same way that swimlane diagrams, floor plans, and some other Visio diagram types use add-on code). The second line refers to another User cell that must have the value "Valve" for this particular report.

You probably don't need to make any changes here but I want to point out the Advanced dialog in case you need it in the future. BTW, in an ideal world, if you'd like to create a report that selects multiple ShapeClass values, e.g., "Value" and "Instrument", you'd use an OR condition.... however, this report writer doesn't provide that option. You can accomplish this indirectly by editing the XML code generated by the report writer... but let's save that for a future question.

4. The next page of the report wizard is where you'll select the fields you want to include in the report. Be sure to check Show all properties if it's not already checked because that option exposes User cells and other values.

-- Include your ID field because that's the one that will be used to link data to shapes. (NOTE: there is a field in the list called ID but I'm not sure what it is. It isn't a User cell or a Shape Data field; I assumed it would display the shape ID but it doesn't.)

-- Include the field called PETagNumber (Custom Property) because that's the one you'll want to edit in Excel to apply your own tags.

5. On the final page of the report definition wizard, give your report a name, then you can either save the report definition in the current drawing, or if you plan to reuse it in multiple drawings, use the Save in a file option. Assuming you choose the latter option, click the Browse button to select a save location. (You won't be able to save the modified report in the default location because that's a protected folder.)
6. Run your modified report and choose Excel as the output format

with a result like this:

7. Delete the Excel report title row ("Valve List" in this case) because it will interfere with the import in the next step.
8. Make changes to any data fields except the ID field; save and close the Excel file.
9. Select Custom Import on the Data tab and select the Excel file. Two notes as As you step through the import wizard:

-- The Visio report wizard creates a named range in Excel called VisRpt that contains the report data; you'll see that name as the default data range when you run the import wizard.

-- Select your ID field name on the Unique Identifier page of the wizard:

10. Select Link Data on the Data tab, select the appropriate data field name and Shape Data field name to identify the unique ID for each row and shape.

At this point, your diagram is linked to the Excel file; you can make additional data changes and simply click the Refresh All button on the Data tab to update values in the diagram. If you add new shapes to the diagram, you'll need to recreate the data file and link again by repeating steps 6-10.

I know this is a lot! And it's probably not the only way to solve the problem, but it will work and it respects the behavior of the P&ID add-on, while giving you the flexibility you need.

Dear Müller Stefan,

Good day! Thank you for posting to the Microsoft Community. We are happy to help you.

Based on your description, it seems that you are concerned about Visio and Excel integration and need to do bulk changes in Excel and update the shapes with the changed data.

From our research, we've found that VBA code might be a better choice to help with this process. Since our team focuses on general queries, for VBA code, we recommend posting your detailed concern on Stack Overflow using the VBA tag, along with any other relevant tags. There are many experienced engineers and experts on that platform who can provide detailed assistance.

Disclaimer: Microsoft provides no assurances and/or warranties, implied or otherwise, and is not responsible for the information you receive from the third-party linked sites, or any support related to technology.

For "Export to Database command failing with some ODBC errors," we understand how frustrating this can be and feel very sorry about it.

We've noticed some users have reported similar situation in the community, the issue can sometimes be resolved on its own on the file, there might be temporary glitches that self-resolved.

Given this, we'd recommend you report the issue via Help > Feedback within Visio. This ensures that the relevant team is aware and can investigate further and retry when error occurs.

We will also keep this thread open so that other Community members and Experts can share their suggestions and inputs.

If our understanding is not consistent with your situation, please feel free to post back and clarify.

Your patience and understanding are highly appreciated. Stay safe and healthy!

Sincerely,

Rhoda | Microsoft Community Moderator