Word Macro - Switch Values from table columns

Sarahmueller

New Member
Joined
May 17, 2020
Messages
24
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hello,

I have a word document, in which results of medicinical tests are documented.

Several tables are part of the document. In these tables, the results of tests for the current and the last weeks are stored.

Afterwards, an assesment by the responsible doctor is provided. Therefore, I have to work with word, and can´t just use excel.

The tables are looking as follows:


Name of patient Current week1 weeks ago
Name 1XY
Name 2XY
Table 1: Medication 1 (week 1)

I have tried to write a macro that automatically switches the values of the columns after application, so that the table looks as follows:

Name of patientCurrent week1 week ago
Name 1X
X
Table 1: Medication 1 (week 2)


I have tried 2 solutions:
1) Edit the tables in Word with record a macro: Doesn´t work, because the selection always changes depending of the length of the doctors assessment
2) Insert tables from Excel into the word document (paste-special - excel worksheet object). I have also written excel makros to switch the columns, but I cant start the excel-macros automatically in word. I always have to double-click on the table, so that an excel worksheet opens and i have to manually activate the macro.

Therefore, i kindly ask you the following questions:

1) is it possible to write a macro in word to automatically edit word-tables?
2) Is ist possible to automatically run an macro in an embedded excel sheet from Microsoft word via vba?

Thank you in advance and stay healthy,

Sarah
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Macropod

Retired Moderator
Joined
Aug 27, 2007
Messages
3,535
For example:
VBA Code:
Sub Demo()
Application.ScreenUpdating = False
Dim Tbl As Table
For Each Tbl In ActiveDocument.Tables
  With Tbl
    .Columns.Add .Columns(2)
    .Cell(1, 2).Range.Text = Split(.Cell(1, 3).Range.Text, vbCr)(0)
    .Cell(1, 3).Range.Text = Split(.Cell(1, 4).Range.Text, vbCr)(0)
    .Columns(4).Delete
  End With
Next
Application.ScreenUpdating = True
End Sub
 

Sarahmueller

New Member
Joined
May 17, 2020
Messages
24
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Thank you so much!

How can i modify the code to only apply the corresponding macro to a distinctive table in the document?

To address only table 1I have tried the following, but the code does not work.

Sub Demo()
Application.ScreenUpdating = False
Dim Tbl As Table
For Each Tbl In ActiveDocument.Tables
With Tbl = Table 1
.Columns.Add .Columns(2)
.Cell(1, 2).Range.Text = Split(.Cell(1, 3).Range.Text, vbCr)(0)
.Cell(1, 3).Range.Text = Split(.Cell(1, 4).Range.Text, vbCr)(0)
.Columns(4).Delete
End With
Next
Application.ScreenUpdating = True
End Sub


Thank you in advance and best regards,

Sarah
 

Macropod

Retired Moderator
Joined
Aug 27, 2007
Messages
3,535
How can i modify the code to only apply the corresponding macro to a distinctive table in the document?
That depends on what you mean by 'a distinctive table'. To limit the process to one or more selected tables, simply change:
ActiveDocument
to:
Selection
 

Sarahmueller

New Member
Joined
May 17, 2020
Messages
24
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Thank you for the answer!
with distinctive table, i mean e.g. table 1 of the document, independent of the selection.

Since I have several tables in the document that contain information that should not be changed, i want to exclude these tables from the macro.
Is it always necessary to select the respective table before i can apply the macro to the table?

Best regards,

Sarah


(and sorry for the crosspost, I have deleted the question)
 

Macropod

Retired Moderator
Joined
Aug 27, 2007
Messages
3,535

ADVERTISEMENT

In that case...
VBA Code:
Sub Demo()
Application.ScreenUpdating = False
With ActiveDocument.Tables(1)
  .Columns.Add .Columns(2)
  .Cell(1, 2).Range.Text = Split(.Cell(1, 3).Range.Text, vbCr)(0)
  .Cell(1, 3).Range.Text = Split(.Cell(1, 4).Range.Text, vbCr)(0)
  .Columns(4).Delete
End With
Application.ScreenUpdating = True
End Sub
 

Sarahmueller

New Member
Joined
May 17, 2020
Messages
24
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Thank you so much!!

Since I have to work with different tables (see below) , I have tried to modify the code for table 2, so that in Table 2 all values 2.1-2.7 are replaced by 1.1-1.7. But i want to keep the headers of the tables.


I have come up with the following code:
Sub OnlyTable2()
'
' OnlyTable2 Macro
'
Application.ScreenUpdating = False
With ActiveDocument.Tables(2)
.Columns.Add .Columns(2)
.Cell(2, 4).Range.Text = Split(.Cell(3, 3).Range.Text, vbCr)(0)

.Columns(4).Delete
End With
Application.ScreenUpdating = True
End Sub

But I cant solve the problems I have with the code:
1) The macro does not delete the values in column 1 (except the header), so that i can enter new ones manually
2) I cant stop the macro from deleting header 2

Can you give me tips how to modify my code?

Thank you so much for your help, i really appreciate it!

Best regards and thanks in advance

Sarah








Name of patientHeader 1Header 2Header 3
Value 1.1Value 1.1Value 2.1Value 4.1
Value 1.2Value 1.2Value 2.2Value 4.2
Value 1.3Value 1.3Value 2.3Value 4.3
Value 1.4Value 1.4Value 2.4Value 4.4
Value 1.5Value 1.5Value 2.5Value 4.5
Value 1.6Value 1.6Value 2.6Value 4.6
Value 1.7Value 1.7Value 2.7Value 4.7
Table 1


Header 1Header 2Header 3Header 4
Value 1.1Value 2.1Value 3.1Value 4.1
Value 1.2Value 2.2Value 3.2Value 4.2
Value 1.3Value 2.3Value 3.3Value 4.3
Value 1.4Value 2.4Value 3.4Value 4.4
Value 1.5Value 2.5Value 3.5Value 4.5
Value 1.6Value 2.6Value 3.6Value 4.6
Value 1.7Value 2.7Value 3.7Value 4.7
Table 2
 

Sarahmueller

New Member
Joined
May 17, 2020
Messages
24
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Additionally:


For my understanding of the problem: How would the code look, if all values in the second row will be deleted, except the header of the corresponding second column?
If I would understand this, I might be able to solve my problem!

Many thanks in advance,

Sarah
 

Macropod

Retired Moderator
Joined
Aug 27, 2007
Messages
3,535
You keep moving the goal posts. To work with the second table in the document, all you needed to change is the '1' in With ActiveDocument.Tables(1).
For my understanding of the problem: How would the code look, if all values in the second row will be deleted, except the header of the corresponding second column?
I have no idea what you mean by that.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,798
Messages
5,626,951
Members
416,211
Latest member
lanka123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top