#1
|
|||
|
|||
VBA code to export data to word with header values
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 |
#2
|
|||
|
|||
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.
|
#3
|
|||
|
|||
hi sir i want export excel data into word file ,not export data from word into excel
|
#4
|
|||
|
|||
Sorry. I read your query backwards. Hopefully one of these Word experts will stop by to help.
|
#5
|
||||
|
||||
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
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#6
|
|||
|
|||
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?
|
#7
|
||||
|
||||
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.
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#8
|
||||
|
||||
FYI, cross-posted: https://www.myonlinetraininghub.com/...ord-from-excel
|
#9
|
||||
|
||||
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
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
Tags |
vba code |
Thread Tools | |
Display Modes | |
|
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 |