Word to Excel

[hjgrassi]

I have a Word document that I need to convert to an Excel sheet. The document has the following structure: bold text RGB 0, 0, 160 that should go in column 1 of the Excel sheet, bold text RGB 0, 128, 64 that would go in column 2 of Excel, normal text RGB 0, 0, 0, 0 that would go in column 3 of Excel, bold text RGB 128, 0, 0, 0 that would go in column 4 of Excel, normal text RGB 0, 0, 0, 0 that would go in column 5 of Excel, the latter may be absent. Then the process starts again, moving on to the next row of Excel with the bold text RGB 0,0,160 and so on until the end of the document.
I appreciate your help in advance.

 

[Peter_SSs]

Welcome to the MrExcel board!

I am not sure this is something I can help with but is there any chance that you could make up a small sample Word document with the required formatting and the corresponding Excel workbook with the required results manually filled in from the Word document and upload them both to DropBox/OneDrive/Google Drive etc and provide publicly shared links to both here?

That way whoever might be able to help would have something relevant to test with and also the expected result to compare as they test.

 

[Macropod]

That is not a document structure; all it describes is some character formatting. What else is in the document (e.g. other text, tabs, paragraph breaks, etc.)?

 

[Trevor G]

Welcome to the forum,

This possibly sounds as though your using/referring to a table. As suggest by Peter if you can provide an example it will help find a solution. Also I agree with Macropod that your only describing formatting.

 

[hjgrassi]

Welcome to the MrExcel board!

I am not sure this is something I can help with but is there any chance that you could make up a small sample Word document with the required formatting and the corresponding Excel workbook with the required results manually filled in from the Word document and upload them both to DropBox/OneDrive/Google Drive etc and provide publicly shared links to both here?

That way whoever might be able to help would have something relevant to test with and also the expected result to compare as they test.

Sitio.rar

drive.google.com

Attached link to site.rar My office version is 2010

 

[Macropod]

Try the following Word macro:

Sub ExportToExcel()
'Note: This code requires an VBA refernce to Excel to be set, via Tools|Refernces in the VBA IDE
Application.ScreenUpdating = False
Dim objUndo As UndoRecord: Set objUndo = Application.UndoRecord
With ActiveDocument
  objUndo.StartCustomRecord ("Export")
  With .Range
    With .Find
      .ClearFormatting
      .Replacement.ClearFormatting
      .Text = "^l"
      .Replacement.Text = "^p"
      .Wrap = wdFindContinue
      .Forward = True
      .Format = False
      .Execute Replace:=wdReplaceAll
      .Text = ""
      .Replacement.Text = "^t^&"
      .Font.Bold = True
      .Font.Color = 4227072
      .Execute Replace:=wdReplaceAll
      .Font.Color = wdColorDarkRed
      .Execute Replace:=wdReplaceAll
      .ClearFormatting
      .Font.Color = wdColorBlack
      .Execute Replace:=wdReplaceAll
      .ClearFormatting
      .Text = "^t."
      .Replacement.Text = "^t"
      .Execute Replace:=wdReplaceAll
      .Text = "^t "
      .Execute Replace:=wdReplaceAll
    End With
    .ConvertToTable Separator:=wdSeparateByTabs, AutoFitBehavior:=wdAutoFitFixed
    .Tables(1).Columns(7).Delete
    .Tables(1).Columns(2).Delete
    .Tables(1).Range.Copy
    objUndo.EndCustomRecord
  End With
  .Undo
  .Range(0, 0).Select
End With
Dim xlApp As New Excel.Application, xlWkBk As Excel.Workbook
With xlApp
  'Add a workbook
  Set xlWkBk = .Workbooks.Add
  With xlWkBk.Sheets(1)
  'Paste the data
    .Paste
    .UsedRange.WrapText = False
    .Columns.AutoFit
    .Rows.AutoFit
    .Range("A1").Select
  End With
  .CutCopyMode = False
  .Visible = True
End With
Application.ScreenUpdating = True
End Sub

 

[hjgrassi]

Thank you very much, it works