Copying whole word document, into excel + formatting columns and cells

[algrey7]

I am quite the novice, so forgive me for any wrong speak...

I am trying to piece together a number of individual codes / functions, into either A (Preferably) a button that sits natively in the Word Ribbon for all our users, or B(if A is too difficult) a button in a master template document, which all users will have to use each time they want to run this automation.

Currently our quotations are exported from a third party cloud system in Word format, but we have to present them in Excel. The current process is as follows -

- Open a new Excel document, format the first 4 column widths
- Open the word document from Downloads folder, CTRL A CTRL C to copy entire word document
- CTRL V into cell A1 of excel sheet
- Click Unwrap text then Wrap text to format text in individual rows
- Manually save as the same filename as the original Word document

With practice this can be done in a minute or so, but the number of times each day our users are doing this and the discrepancies in formatting and filenames with various users, is making this tedious now..

Using power automate, I have recorded a couple of functions to try and break this down, but coming a little unstuck now and perhaps I am coming at it from the wrong angle altogether?

This works really well in formatting the columns widths -


function main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook.getActiveWorksheet();
// Set width of column(s) at range A:A on selectedSheet to 400
selectedSheet.getRange("A:A").getFormat().setColumnWidth(400);
// Set width of column(s) at range B:D on selectedSheet to 125
selectedSheet.getRange("B:D").getFormat().setColumnWidth(125);
}

and this works well with copying over the required data from the word document -

Sub ImportSectHWord()
Dim WordApp As Object
Dim objDoc As Object
Dim wdFileName As Variant

wdFileName = Application.GetOpenFilename("Word Documents, *.doc*")
If wdFileName = False Then Exit Sub
Set WordApp = CreateObject("Word.Application")
Set objDoc = WordApp.Documents.Open(wdFileName)

WordApp.Selection.WholeStory
WordApp.Selection.Copy

ThisWorkbook.Sheets("Sheet1").Range("A1").Select
ActiveSheet.Paste
objDoc.Close False
WordApp.Quit

Set WordApp = Nothing
Set objDoc = Nothing
End Sub

This is script was recorded when unwrapping / wrapping the copied over source data -

function main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook.getActiveWorksheet();
// Set wrap text to true for all cells on selectedSheet
selectedSheet.getRange().getFormat().setWrapText(true);
// Indent set to 0 for all cells on selectedSheet
selectedSheet.getRange().getFormat().setIndentLevel(0);
// Set wrap text to false for all cells on selectedSheet
selectedSheet.getRange().getFormat().setWrapText(false);
// Indent set to 0 for all cells on selectedSheet
selectedSheet.getRange().getFormat().setIndentLevel(0);
// Indent set to 0 for range A1 on selectedSheet
selectedSheet.getRange("A1").getFormat().setIndentLevel(0);
// Set wrap text to true for range A1 on selectedSheet
selectedSheet.getRange("A1").getFormat().setWrapText(true);
// Indent set to 0 for range A1 on selectedSheet
selectedSheet.getRange("A1").getFormat().setIndentLevel(0);

However the above does require us to have the excel / target document open to run the macro, find and select the correct word / source document via the open pop up box etc., hence why it would be good to have a native button in Word, so when the source document is opened from the download folder, we click the native button and the following functions run -

- Source content is copied
- New excel workbook is opened
- Column formatting takes place
- Source content is pasted into A1
- Unwrap / wrap formatting entire sheet
- Save as pop up, auto populated with source filename (less the .docx)
- User then selects destination folder and saves.

Hope I've explained this correctly, any thoughts or advice is greatly appreciated.

Ross

 

[Macropod]

Cross-posted at: Copying Word content into excel with formatting and Autosave
Please read Mr Excel's policy on Cross-Posting in rule 13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html