VBA code to export data to word with header values

jackfruit88 · #1 07-02-2022, 07:34 AM

Dear experts

I have fallen in data to transpose from excel to word manually i am in need of vba code help ,

i have set of data which is need to copy first 2 rows only 8 columns A:H paste into word with header point

The data layer segregate as two layer FY 19-20 one row, FY 21-22 second row
first two row copy with header paste into word as vertical
third and fourth row as another segment with header value paste into word
fifth and sixth row as another segment with header value paste into word

Thanks

jeffreybrown · #2 07-02-2022, 09:26 AM

I'll take a stab at it. Click the button to run. This code will ask for where you want to start with the table, but I would assume it's always going to start with 1. This part can be removed. This code will also ask you thru a dialogue box to select where the Word file exists.

jackfruit88 · #3 07-02-2022, 05:02 PM

hi sir i want export excel data into word file ,not export data from word into excel

jeffreybrown · #4 07-02-2022, 07:22 PM

Sorry. I read your query backwards. Hopefully one of these Word experts will stop by to help.

Guessed · #5 07-03-2022, 04:31 AM

Try this code. Put it in your Excel workbook and add a reference to "Microsoft Word x.x Object Library".

Before running the macro, make sure the correct worksheet is active.

This code is putting a duplicate of the first table at the end of the document and adding the Excel data into that new table. It repeats for each pair of rows in Excel.

Code:

Sub Write2Word()
  Dim wdDoc As Word.document, wdTbl As Word.Table, wdApp As Object, wdRng As Word.Range
  Dim iRow As Integer, aSheet As Worksheet, iCol As Integer, iPair As Integer
  Dim sPath As String
  Set aSheet = ActiveSheet
  iRow = 2
  sPath = ActiveWorkbook.Path & "\"
  Set wdApp = CreateObject("Word.Application")
  wdApp.Visible = True
  
  Set wdDoc = wdApp.Documents.Add(sPath & "OUTPUT_RESULT-FILE.docx")  'creates new doc based on this file
  
  Do While aSheet.Cells(iRow, 1).Value <> ""
    Set wdRng = wdDoc.Range
    wdRng.Collapse Direction:=0
    wdRng.InsertBefore Chr(13) & Chr(13)
    wdRng.Collapse Direction:=0
    wdRng.FormattedText = wdDoc.Tables(1).Range.FormattedText
    Set wdTbl = wdRng.Tables(1)
    For iPair = 0 To 1
      For iCol = 0 To 7
        wdTbl.Cell(iCol + 1, iPair + 2).Range.Text = aSheet.Cells(iRow, 1).Offset(iPair, iCol).Value
      Next iCol
    Next iPair
    iRow = iRow + 2
  Loop
End Sub

jackfruit88 · #6 07-03-2022, 04:18 PM

its works but its not insert each image on each page , its place 2 or 3 image in each page can u do help on tis please?

Guessed · #7 07-03-2022, 04:46 PM

You should do this by using a paragraph attribute in Word.

Go to the Word document and put your cursor in the first cell of the first table. Then via the Home Tab, open the Paragraph dialog and set the paragraph to have a Page Break Before. Once this is done, the macro will reproduce this across all your created tables.

Ideally, you would use styles to be as efficient as possible. You shouldn't put this setting on subsequent rows in the table, just the first row.

Debaser · #8 07-05-2022, 12:17 AM

FYI, cross-posted: https://www.myonlinetraininghub.com/...ord-from-excel

macropod · #9 07-06-2022, 07:45 AM

Also cross-posted at:
need vba code to export data from excel to word file
Need VBA Help to export data from excel to word as 2 header [SOLVED]

Kindly read the following on the expected cross-posting etiquette: Excelguru Help Site - A message to forum cross posters

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Export data to word table	Dzib	Excel Programming	7	08-28-2019 11:51 PM
Export Access report as pdf -- save as .rft -- and Word puts some text into the header	louiseword	Word	1	11-21-2016 04:32 PM
VBA Export Data as Text from Excel to Word	lwbarnes	Word VBA	3	06-09-2016 02:47 PM
need vba code export data to closed workbook sheets	manilara	Excel Programming	1	02-12-2016 08:20 PM
Export Word Form Data to Access	pboland	Word VBA	1	06-12-2015 06:53 PM