Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-02-2022, 07:34 AM
jackfruit88 jackfruit88 is offline VBA code to export data to word with header values Windows 10 VBA code to export data to word with header values Office 2019
Novice
VBA code to export data to word with header values
 
Join Date: Jun 2022
Posts: 4
jackfruit88 is on a distinguished road
Default 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
Attached Files
File Type: docx OUTPUT_RESULT-FILE.docx (22.7 KB, 7 views)
File Type: xls twoheader.xls (34.5 KB, 7 views)
Reply With Quote
  #2  
Old 07-02-2022, 09:26 AM
jeffreybrown jeffreybrown is offline VBA code to export data to word with header values Windows 10 VBA code to export data to word with header values Office 2016
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

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.
Attached Files
File Type: xls twoheader.xls (48.5 KB, 5 views)
Reply With Quote
  #3  
Old 07-02-2022, 05:02 PM
jackfruit88 jackfruit88 is offline VBA code to export data to word with header values Windows 10 VBA code to export data to word with header values Office 2019
Novice
VBA code to export data to word with header values
 
Join Date: Jun 2022
Posts: 4
jackfruit88 is on a distinguished road
Default

hi sir i want export excel data into word file ,not export data from word into excel
Reply With Quote
  #4  
Old 07-02-2022, 07:22 PM
jeffreybrown jeffreybrown is offline VBA code to export data to word with header values Windows 10 VBA code to export data to word with header values Office 2016
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

Sorry. I read your query backwards. Hopefully one of these Word experts will stop by to help.
Reply With Quote
  #5  
Old 07-03-2022, 04:31 AM
Guessed's Avatar
Guessed Guessed is offline VBA code to export data to word with header values Windows 10 VBA code to export data to word with header values Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,047
Guessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud of
Default

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
Reply With Quote
  #6  
Old 07-03-2022, 04:18 PM
jackfruit88 jackfruit88 is offline VBA code to export data to word with header values Windows 10 VBA code to export data to word with header values Office 2019
Novice
VBA code to export data to word with header values
 
Join Date: Jun 2022
Posts: 4
jackfruit88 is on a distinguished road
Default

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?
Attached Images
File Type: png error.png (58.7 KB, 13 views)
Reply With Quote
  #7  
Old 07-03-2022, 04:46 PM
Guessed's Avatar
Guessed Guessed is offline VBA code to export data to word with header values Windows 10 VBA code to export data to word with header values Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,047
Guessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud ofGuessed has much to be proud of
Default

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.
Attached Images
File Type: png 2022-07-04_9-37-43.png (21.1 KB, 13 views)
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #8  
Old 07-05-2022, 12:17 AM
Debaser's Avatar
Debaser Debaser is offline VBA code to export data to word with header values Windows 7 64bit VBA code to export data to word with header values Office 2010 32bit
Competent Performer
 
Join Date: Oct 2015
Posts: 195
Debaser is on a distinguished road
Default

FYI, cross-posted: https://www.myonlinetraininghub.com/...ord-from-excel
Reply With Quote
  #9  
Old 07-06-2022, 07:45 AM
macropod's Avatar
macropod macropod is offline VBA code to export data to word with header values Windows 10 VBA code to export data to word with header values Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,588
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
Reply

Tags
vba code

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA code to export data to word with header values Export data to word table Dzib Excel Programming 7 08-28-2019 11:51 PM
VBA code to export data to word with header values 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 code to export data to word with header values VBA Export Data as Text from Excel to Word lwbarnes Word VBA 3 06-09-2016 02:47 PM
VBA code to export data to word with header values need vba code export data to closed workbook sheets manilara Excel Programming 1 02-12-2016 08:20 PM
VBA code to export data to word with header values Export Word Form Data to Access pboland Word VBA 1 06-12-2015 06:53 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:54 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2022, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2022 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft