Solved: Importing Data from Excel into Word using VBA

[Solrac3030]

Problem: Need to write a macro in Word that would go to a particular Excel spreadsheet and copy the contents of the spreadsheet and paste into a bookmark in the Word document. I am not sure what the oced is to open the Excel spreadsheet and copy the data and paste it into Word. Any help would be greatly appreciated.

thank you.

[fumei]

Having you tried searching on this? There are many examples here, and on other forums.

[Solrac3030]

Searched but did not find what I was looking for. Found how to move Excel to a Word document while in Excel but don't need that. I have a Word template that has x amont of bookmarks. The names of the bookmarks are the same as the names of the Excel spreadsheets that are in another location. While in the template I need to run a macro that will go to each excel spreadsheet open the spreadsheet copy the contents of sheet1 and paste that content into the bookmark with the same name in the Word document. Hope that clarifies a little.

[lucas]

does it have to be run from Word? Can it be run from the excel file?

[macropod]

Hi Solrac3030,

Your first post indicated a single Word document, containing a single bookmark, into which the contents of a single Excel workbook needed to be pasted. Your second post suggests there are multiple bookmarks to be populated from multiple Excel workbooks.

Which is it?

Is there a reason the Excel data can't be consolidated into a single workbook?

You also say "I have a Word template". Templates have specific meaning in Word terminology. Is your's a file with a .dot extension (ie a true Word template), or something else?

[Solrac3030]

Ok. It is a template .dot, and there are multiple bookmarks or textfields which will be accepting the data from the Excel spreadsheets. There are multiple spreadsheets as the programmer cannot place the data he is pulling from AS400 onto a single spreadsheet with multiple sheets. He can only put it into multiple spreadsheets. The code cannot sit on the Excel spreadsheet as the programmer cannot write this code everytime the spreasheet is created by AS400, so code needs to sit on the the Word template.

[Solrac3030]

This is the code that I have right now that will write the code from the spreadsheet to the template. Problem is the Window that opens up letting the user know that ther is a large amount of data on the clipboard and the user needs to click no to close the window. Need to figure out how to Keep this window from opening up.


Sub SuppCode3()
Dim Xl As Excel.Application, Wb As Excel.Workbook, ws As Excel.Worksheet, i As Integer
Dim XlOpen As Boolean
'//This code requires a reference to the Excel object library to use some Xl methods
'//such as End(xlup) and Dim objects Xl and Wb (otherwise just declare as Object)

On Error Resume Next
Set Xl = GetObject(, "Excel.Application") 'Select Xl app if open
If Err.Number <> 0 Then 'If Excels not already open then open an instance
Set Xl = CreateObject("Excel.Application")
'Xl.Visible = True 'If you want Excel to be visible. Its invisible by default.
Else
XlOpen = True 'an indicator so we know whether to close Xl app or not when finished
End If
On Error GoTo 0

'Open workbook
On Error Resume Next
Set Wb = Xl.Workbooks.Open("C:\Documents and Settings\Dominguc\My Documents\SuppCode3.xls")
If Wb Is Nothing Then
MsgBox "Unable to open file!"
On Error GoTo 0
GoTo CleanUp
End If
On Error GoTo 0

With Wb.Sheets(1)
'see if theres anything to copy
If .Cells(1, 1) = vbNullString Then
MsgBox "There is no text to copy!"
Selection.GoTo What:=wdGoToBookmark, Name:="SuppCode3"
Selection.Delete
GoTo CleanUp
End If
.UsedRange.Copy ' Copy contents of Sheet 1
'Go to bookmark called where you want to insert values
Selection.GoTo What:=wdGoToBookmark, Name:="SuppCode3"
Selection.Range.Paste 'Paste sheet 1 into bookmark
End With
CleanUp:
If XlOpen = False Then Xl.Quit 'close xl if we started it otherwise leave open
Set Xl = Nothing
Set Wb = Nothing
End Sub

[Solrac3030]

There would be as many blocks of code for each SuppCode there is. My other problem is getting the next block of code to run after this one is complete. Each would be identical except for the name of the bookmark and the name of the Sub. Can they be run automatically one after the other. If they cannot I can put each sub on a menu and the user would run each sub one at a time.

[Dave]

At the start...
Application.DisplayAlerts = False
At the end...

Application.DisplayAlerts = True

HTH with post #7. Dave

[Solrac3030]

This is my code right now. All is working and each block is being run by a menu in the addins tab. So user has to select each Supp. Code to run. Is there a way to have the first code run and after that one is done go automatically to the next one.


Sub SuppCode3()
Dim Xl As Excel.Application, Wb As Excel.Workbook, ws As Excel.Worksheet, i As Integer
Dim XlOpen As Boolean
'//This code requires a reference to the Excel object library to use some Xl methods
'//such as End(xlup) and Dim objects Xl and Wb (otherwise just declare as Object)


On Error Resume Next
Set Xl = GetObject(, "Excel.Application") 'Select Xl app if open
If Err.Number <> 0 Then 'If Excels not already open then open an instance
Set Xl = CreateObject("Excel.Application")
Else
XlOpen = True 'an indicator so we know whether to close Xl app or not when finished
End If
On Error GoTo 0

'Open workbook
On Error Resume Next
Set Wb = Xl.Workbooks.Open("C:\Documents and Settings\Dominguc\My Documents\SuppCode3.xls")
Wb.Application.DisplayAlerts = False
If Wb Is Nothing Then
MsgBox "Unable to open file!"
On Error GoTo 0
GoTo CleanUp
End If
On Error GoTo 0

With Wb.Sheets(1)
'see if theres anything to copy
If .Cells(1, 1) = vbNullString Then
MsgBox "There is no text to copy!"
GoTo CleanUp
End If

.UsedRange.Copy ' Copy contents of Sheet 1
Selection.TypeText Text:="Your Text Here" & vbCr & vbCr
Selection.Paste 'Paste sheet 1 into document at cursor
End With
Wb.Application.DisplayAlerts = False
WordBasic.EditOfficeClipboard
CommandBars("Office Clipboard").Visible = False

CleanUp:
If XlOpen = False Then Xl.Quit 'close xl if we started it otherwise leave open
Set Xl = Nothing
Set Wb = Nothing
End Sub

Sub SuppCode5()
Dim Xl As Excel.Application, Wb As Excel.Workbook, ws As Excel.Worksheet, i As Integer
Dim XlOpen As Boolean
'//This code requires a reference to the Excel object library to use some Xl methods
'//such as End(xlup) and Dim objects Xl and Wb (otherwise just declare as Object)

On Error Resume Next
Set Xl = GetObject(, "Excel.Application") 'Select Xl app if open
If Err.Number <> 0 Then 'If Excels not already open then open an instance
Set Xl = CreateObject("Excel.Application")
'Xl.Visible = True 'If you want Excel to be visible. Its invisible by default.
Else
XlOpen = True 'an indicator so we know whether to close Xl app or not when finished
End If
On Error GoTo 0

'Open workbook
On Error Resume Next
Set Wb = Xl.Workbooks.Open("C:\Documents and Settings\Dominguc\My Documents\SuppCode5.xls")
Wb.Application.DisplayAlerts = False
If Wb Is Nothing Then
MsgBox "Unable to open file!"
On Error GoTo 0
GoTo CleanUp
End If
On Error GoTo 0

With Wb.Sheets(1)
'see if theres anything to copy
If .Cells(1, 1) = vbNullString Then
MsgBox "There is no text to copy!"
GoTo CleanUp
End If

.UsedRange.Copy ' Copy contents of Sheet 1
Selection.TypeText Text:="Your Text Here" & vbCr & vbCr
Selection.Paste 'Paste sheet 1 into document at cursor
End With
Wb.Application.DisplayAlerts = False
WordBasic.EditOfficeClipboard
CommandBars("Office Clipboard").Visible = False

CleanUp:
If XlOpen = False Then Xl.Quit 'close xl if we started it otherwise leave open
Set Xl = Nothing
Set Wb = Nothing
End Sub

[lucas]

Just call the next procedure at the end of the first one. In this I call sub b after sub a has run:

Option Explicit
Sub a()
MsgBox "Test"
Call b
End Sub
Sub b()
MsgBox "Test Two"
End Sub

[Dave]

This would be better. Dave

CleanUp:
If xlOpen = False Then Xl.Quit 'close xl if we started it otherwise leave open
Wb.Application.DisplayAlerts = False
Set Wb = Nothing
Set Xl = Nothing
End Sub

[Solrac3030]

Will try the new code and see how that works. I have been trying to figure out a way to clear the contents of the clipboard so that it will not get full if all the subs run one after another. I got the message window from coming up but the clipboard still contains the data it copied form the Excel file. Is there a way to delete the data from the clipboard so that it does not get full and then cause problems when the code is running?

[lucas]

Sub ClearClipboard()

Dim oDataObject As DataObject

Set oDataObject = New DataObject
oDataObject.SetText ""
oDataObject.PutInClipboard

Set oDataObject = Nothing

End Sub


Edit: Will not work unless you have a userform in your document.

Looking at the Help files it appears that the DataObject is associated with a user form. So, simply after adding a form to the template the code compiles. Note that the form was just added to the template project; it wasn't referenced in any way, it wasn't called in any way and, apart from just sitting there in the code, it does nothing. Well, apart from making the code compile -- and work.

What's the reason for this? Simple, the DataObject object is referenced in the MS Forms object. So all the programmer has to do is to add (and then remove a form if he wishes) as this will force the reference to the object or he can just simply add the MS Forms reference himself.
This template, if placed into the user's start-up folder, shows its presence by displaying a single button on a toolbar. Clicking on this simply empties the Clipboard by the simple process of copying a zero length string to it.

[lucas]

or maybe:

Option Explicit
Public Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
Public Declare Function EmptyClipboard Lib "user32" () As Long
Public Declare Function CloseClipboard Lib "user32" () As Long

Public Function ClearClipboard()
OpenClipboard (0&)
EmptyClipboard
CloseClipboard
End Function

Sub ccc()
Call ClearClipboard
End Sub

[lucas]

The second one works in word. The first one I think is excel specific. The second one works in word I just tested it.

Edit: the first one works if you have a userform in your document.....see notes in post #14

[Dave]

Not sure, but this is probably even better. However, this probably won't help with the clearing clipboard thing? HTH. Dave

CleanUp:
If xlOpen = False Then Xl.Quit 'close xl if we started it otherwise leave open
Wb.Application.CutCopyMode = False
Wb.Application.DisplayAlerts = False
Set Wb = Nothing
Set Xl = Nothing
End Sub

on edit I'm not so sure about this code but I'll leave it for trial