601

Copy and paste Whole Story from Word to Excel

Question:

I have a Word document that is received periodically that needs to be combined with other data already in Excel as part of a larger output. I can copy and paste the whole document (WholeStory) from Word into Excel, but I want to make this part of the whole Excel output macro. My current code for this portion is below, and it works fine except that it pastes nothing. There is no data to paste when it gets there, so I guess it is either not picking it up to start with or not carrying it over. Looking for assistance with this. Thanks very much!

Sub ImportSectHWord() Dim objWord As Object Dim objDoc As Object Dim wdFileName Set objWord = CreateObject("word.Application") wdFileName = Application.GetOpenFilename("Word Documents, *.doc*") If wdFileName = False Then Exit Sub Set objDoc = GetObject(wdFileName) objWord.Documents.Open (wdFileName) objWord.Selection.WholeStory Selection.Copy Worksheets("H Import").Select Range("A1").Select ActiveSheet.Paste objDoc.Close SaveChanges:=wdDoNotSaveChanges objWord.Quit End Sub

Answer1:

The statement

Selection.Copy

is copying whatever is currently selected in Excel.

To copy the Selection object in Word, use

objWord.Selection.Copy

It is always advisable to qualify what objects you are referring to when using methods and properties, even when VBA provides a default object.

Sub ImportSectHWord() 'It is better to always define constants, even though they will default to zero ' which just happens to be the desired value in this case Const wdDoNotSaveChanges As Long = 0 Dim objWord As Object Dim objDoc As Object Dim wdFileName Set objWord = CreateObject("word.Application") wdFileName = Application.GetOpenFilename("Word Documents, *.doc*") If wdFileName = False Then Exit Sub Set objDoc = GetObject(wdFileName) objWord.Documents.Open (wdFileName) objWord.Selection.WholeStory objWord.Selection.Copy ActiveWorkbook.Worksheets("H Import").Select ActiveWorkbook.Worksheets("H Import").Range("A1").Select ActiveWorkbook.ActiveSheet.Paste objDoc.Close SaveChanges:=wdDoNotSaveChanges objWord.Quit End Sub

Recommend

  • Classic ASP IIS 6 Response buffer limit exceeded
  • Forcing a table to fit the margins
  • VB6.0 MDI Spell Checker
  • Saving and closing office programs (word, excel, access, outlook) with VBS for backups
  • Hadoop: what should be mapped and what should be reduced?
  • VBA: Selecting from dropdown menu to reload page and scraping data
  • compare two arrays based on length: skip empty values
  • Greyhound data import to excel macro formula
  • c# - how to improve this “file explorer” startup speed?
  • Excel 2010 VBA code to delete all Outlook appointments
  • Informatica - Concatenate all rows of 1 column to a single value
  • ActiveX can't create object powerpont vba
  • undirected graphs algorithms
  • .Click action not doing anything - IE Simulation
  • dot 2 pixels together
  • So we've got MEF working with MVC4, how about the convention based model?
  • Msql: Counting growth over time
  • Setting IE “Optical Zoom” feature using Javascript/CSS
  • PowerShell IComparable with subclasses
  • best layer for placing dependency injection related code in layered proeject
  • Why is my database not being updated when someone visits my website?
  • How to get the index of the data element in a histogram on mouseover?
  • Insert list of lists into single column of pandas df
  • Go template remove the last comma in range loop
  • integrating GWT with CDI (Seam/Weld)
  • Assembly Language Absolute addresses and segment registers
  • Dependency Injection - Choose DLL and class implementation at runtime through configuration file
  • EnumerateFiles() equivalent in .NET 3.5
  • Programmatically change properties in email body in Outlook with VBA
  • Trying to delete files older than X number of days on insert in .NET MVC
  • How to fetch the alt value from an img using vba
  • Read files from URL
  • Pasting URLs That Have Been Scraped From a Webpage
  • Rotated Text in Circular Region
  • C++ Armadillo Access Triangular Matrix Elements
  • unable to get jsonEncode in magento2
  • Volusion's generic SQL folder, functionality
  • python regex in pyparsing
  • Android Google Maps API OnLocationChanged only called once
  • How can I use threading to 'tick' a timer to be accessed by other threads?