Running VBA Code from Excel to delete a Rich Text Format from a Word Document Dependent on A Dropdown Box in Excel

[CumminsCowboy]

Hi all,
I am working on a project that uses a form I have created in Excel to populate Rich Text Formats in a word document in order to streamline report generations. I have almost completed the project but I have ran into a problem I can't seem to figure out. I am fairly new to the VBA programming language, so any help I can get that I can also learn from would be greatly appreciated.

Problem Description:
The excel form I created has a dropdown box with "Yes" and "No" options. I need the module code to run to delete a RTF (labeled "AK") that contains a table in the Word document when No is the selected answer. If the answer is yes, it doesn't need to do anything because the table in the RTF is already in the document. The code I currently have runs and somewhat works, but not in the way I want. When I run my code, 2 documents pull up. One document opens that has all the values from the form populated, but doesn't delete the RTF I wish to delete. The other document that opens deletes the RTF, but does not fill in the other RTF's that are supposed to be pushed through.

Code:
Sheet 1-
Sub CTR()
'Fill in information on word doc.

Dim objWord As Word.Application
Dim oCC As ContentControl
Dim Username As String
Dim ActDoc As Word.Document

Set objWord = New Word.Application
objWord.Visible = True
objWord.Activate
Username = Environ$("username")

Set ActDoc = objWord.Documents.Open("File Path")
For Each oCC In ActDoc.ContentControls
Select Case oCC.Tag
Case "A"
oCC.Range.Text = Sheets("Sheet1").Cells(4, 10)
Case "B"
oCC.Range.Text = Sheets("Sheet1").Cells(6, 10)
Case "C"
oCC.Range.Text = Sheets("Sheet1").Cells(8, 10)
Case "D"
oCC.Range.Text = Sheets("Sheet1").Cells(10, 10)
Case "E"
oCC.Range.Text = Sheets("Sheet1").Cells(12, 10)
Case "F"
oCC.Range.Text = Sheets("Sheet1").Cells(14, 10)
Case "G"
oCC.Range.Text = Sheets("Sheet1").Cells(16, 10)
Case "H"
oCC.Range.Text = Sheets("Sheet1").Cells(18, 10)
Case "I"
oCC.Range.Text = Sheets("Sheet1").Cells(20, 10)
Case "J"
oCC.Range.Text = Sheets("Sheet1").Cells(22, 10)
Case "K"
oCC.Range.Text = Sheets("Sheet1").Cells(24, 10)
Case "L"
oCC.Range.Text = Sheets("Sheet1").Cells(26, 10)
Case "M"
oCC.Range.Text = Sheets("Sheet1").Cells(28, 10)
Case "N"
oCC.Range.Text = Sheets("Sheet1").Cells(30, 10)
Case "O"
oCC.Range.Text = Sheets("Sheet1").Cells(32, 10)
Case "P"
oCC.Range.Text = Sheets("Sheet1").Cells(34, 10)
Case "Q"
oCC.Range.Text = Sheets("Sheet1").Cells(36, 10)
Case "R"
oCC.Range.Text = Sheets("Sheet1").Cells(38, 10)
Case "S"
oCC.Range.Text = Sheets("Sheet1").Cells(40, 10)
Case "T"
oCC.Range.Text = Sheets("Sheet1").Cells(42, 10)
Case "U"
oCC.Range.Text = Sheets("Sheet1").Cells(44, 10)
Case "V"
oCC.Range.Text = Sheets("Sheet1").Cells(46, 10)
Case "W"
oCC.Range.Text = Sheets("Sheet1").Cells(48, 10)
Case "X"
oCC.Range.Text = Sheets("Sheet1").Cells(50, 10)
Case "Y"
oCC.Range.Text = Sheets("Sheet1").Cells(52, 10)
Case "Z"
oCC.Range.Text = Sheets("Sheet1").Cells(54, 10)
Case "AA"
oCC.Range.Text = Sheets("Sheet1").Cells(56, 10)
Case "AB"
oCC.Range.Text = Sheets("Sheet1").Cells(58, 10)
oCC.Range.Text = Sheets("Sheet1").Cells(60, 10)
Case "AD"
oCC.Range.Text = Sheets("Sheet1").Cells(62, 10)
Case "AE"
oCC.Range.Text = Sheets("Sheet1").Cells(64, 10)
Case "AF"
oCC.Range.Text = Sheets("Sheet1").Cells(66, 10)
Case "AG"
oCC.Range.Text = Sheets("Sheet1").Cells(68, 10)
Case "AH"
oCC.Range.Text = Sheets("Sheet1").Cells(70, 10)
Case "AI"
oCC.Range.Text = Sheets("Sheet1").Cells(72, 10)
Case "AJ"
oCC.Range.Text = Sheets("Sheet1").Cells(74, 10)
Case "AL"
oCC.Range.Text = Sheets("Sheet1").Cells(78, 10)
Case "AM"
oCC.Range.Text = Sheets("Sheet1").Cells(80, 10)
Case "AN"
oCC.Range.Text = Sheets("Sheet1").Cells(82, 10)
Case "AO"
oCC.Range.Text = Sheets("Sheet1").Cells(84, 10)
Case "AP"
oCC.Range.Text = Sheets("Sheet1").Cells(86, 10)
Case "AQ"
oCC.Range.Text = Sheets("Sheet1").Cells(88, 10)
Case "AR"
oCC.Range.Text = Sheets("Sheet1").Cells(90, 10)
Case "AS"
oCC.Range.Text = Sheets("Sheet1").Cells(92, 10)
Case "AT"
oCC.Range.Text = Sheets("Sheet1").Cells(94, 10)
Case "AU"
oCC.Range.Text = Sheets("Sheet1").Cells(96, 10)
Case "AV"
oCC.Range.Text = Sheets("Sheet1").Cells(98, 10)


End Select
Next oCC


Select Case Range("J76")

Case "No": Hide_Table

End Select
End Sub

Module-
Sub Hide_Table()
Dim objWord As Word.Application
Dim oCC As ContentControl
Dim Username As String
Dim ActDoc As Word.Document

Set objWord = New Word.Application
objWord.Visible = True
Username = Environ$("username")

Set ActDoc = objWord.Documents.Open("File_Path")

For Each oCC In ActDoc.ContentControls
Select Case oCC.Tag
Case "AK"
oCC.Delete True


End Select
Next oCC
End Sub
_________________________________________________________________________________________________________________________________________________________________

The code at the bottom of Sheet 1 with red font is the code I have that refers to the module code. I know the reason it is pulling up 2 documents is because the code is the same as what's on Sheet 1 with the exception of the oCC.Delete command. But I cannot get it to work without that code on top. So is there a way to make the module code where it will work and delete the RTF labeled "AK" in the document that is opened from the Sheet 1 code, or even a way to code this command directly into sheet 1 without the use of a module?
Any help that you can provide would be greatly appreciated. Thanks in advance!

Cheers,
BF

 

[jdellasala]

Errr... What about saving the Word file as Text and just pulling that in? (VBA UGGGHHH!)

LOL.

 

[Dan_W]

Doesn't seem like a very scalable solution, though. Or one that can be automated. And you'd have to manually position the text in cell for every other row.

That said, I'm not sure I fully understand what is being referred to as RTFs. :-/

 

[jdellasala]

It can be automated with Power Automate available through Office Online as well as the Desktop app.

 

[Dan_W]

It can be automated with Power Automate available through Office Online as well as the Desktop app.

Only just saw this - Sorry. I don't have much experience with PowerAutomate, but I'm interested to learn that it can populate the specific cells. I dont know that youd be able to UI forms either. And the solution requires installation of additional software, which is not often possible in the office workplace.

Office Online wouldn't help here, as it won't have automated access to the local drive or the Environ info.