Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-09-2017, 12:30 PM   #1
AWAISKAZMI
Newly Registered User
 
Join Date: Oct 2017
Posts: 12
Thanks: 1
Thanked 0 Times in 0 Posts
AWAISKAZMI is on a distinguished road
Copy of Excel (OLE Object) and paste as table in word

Dear Experts;

So far I have created a data base that contains a table having 07 fields.

I have successfully export my data (extract from table) to word

Now i am in the need add 08 the field that is "OLE Object" each record have its separate OLE Object (Excel File)

now i want to print (export) table of excel in word..... tried a lot but failed

Kindly help me at which step my code is incorrect.

Code:
Private Sub CMD_MY_DOC_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
On Error GoTo errorhandler
Set cn = New ADODB.Connection

rs.ActiveConnection = CurrentProject.Connection
rs.Open ("select * from MySelectedObservations")

' Opening and Writing to Word Documnt
Dim objWord As Object
Dim pathgetter As String
Dim doc As Object

'Dim WordHeaderFooter As HeaderFooter
Dim filepath As String
Set objWord = CreateObject("Word.Application")

pathgetter = DLookup("Word_Path_Field", "WORD_PATH_TBL", "Serial = 1")
filepath = "" & pathgetter & "\Observations " & ".docx"

MsgBox "Please close Observation file first (if opened)"

With objWord
    .Visible = True

Set doc = .Documents.Open(filepath)
End With

Dim DT_TM As String
DT_TM = "Observations up-to " & CStr(Now())

   Dim d As Database
   Dim rs1 As Recordset
   Dim dept As Field
   Dim head As Field
   Dim obs As Field
   Dim rimp As Field
   Dim ratg As Field
   
      
   Set dbs1 = CurrentDb()
   Set rs1 = dbs1.OpenRecordset("MySelectedObservations")
   Set dept = rs1.Fields("Department_Name")
   Set head = rs1.Fields("Observation_Heading")
   Set obs = rs1.Fields("Observation_Details")
   Set tabl = rsl.Fields("Table")
   Set rimp = rs1.Fields("Risk_Implication")
   Set ratg = rs1.Fields("Risk_Category")
   
   
   Dim dept_nm As String
   dept_nm = "abcd"


With objWord.Selection

.Font.Name = "Times New Roman"
.Font.Size = 16
.Font.Bold = True
.Font.Underline = wdUnderlineSingle
.Font.Color = vbRed
.TypeText DT_TM
.Font.Color = vbBlack
.TypeParagraph

While rs1.EOF = False

If dept_nm <> dept.Value Then

 .Font.Name = "Times New Roman"
 .Font.Size = 10
 .Font.Bold = True
 .Font.Underline = wdUnderlineSingle
 .TypeText dept.Value
 .TypeText ":"
 .TypeParagraph
 dept_nm = dept.Value
 
 End If
 
 .Font.Name = "Times New Roman"
 .Font.Size = 10
 .Font.Bold = True
 .Font.Underline = wdUnderlineSingle
 .TypeText head.Value
 .TypeText ":"
 .TypeParagraph

 .Font.Name = "Times New Roman"
 .Font.Size = 10
 .Font.Bold = False
 .Font.Underline = wdUnderlineNone
 .TypeText obs.Value
 .TypeParagraph
 
'Copy Excel Table Range
 
Dim tbl As Excel.Range
Dim wordtable As Word.Table
Set tbl = tabl.OLEObject.worksheets(Sheet1.Name)


 tbl.Copy
'Paste Table into MS Word
 objWord.Paragraphs(1).Range.PasteExcelTable _
 LinkedToExcel:=False, _
 WordFormatting:=False, _
 RTF:=False

'Autofit Table so it fits inside Word Document
 Set wordtable = objWord.Tables(1)
 wordtable.AutoFitBehavior (wdAutoFitWindow)
   
EndRoutine:
'Optimize Code
'  Application.ScreenUpdating = True
' Application.EnableEvents = True

'Clear The Clipboard
 ' Application.CutCopyMode = False

 .TypeParagraph

 .Font.Name = "Times New Roman"
 .Font.Size = 10
' .Font.TextColor = vbBlack
 .Font.Bold = True
 .Font.Underline = wdUnderlineNone
   
 .TypeText "Risk Implication: "
    
 .Font.Name = "Times New Roman"
 .Font.Size = 10
' .Font.TextColor = vbBlack
 .Font.Bold = False
 .Font.Underline = wdUnderlineNone
    
 .TypeText rimp.Value
 .TypeParagraph
 
 .Font.Name = "Times new Roman"
 .Font.Size = 10
 '.Font.TextColor = vbBlack
 .Font.Bold = True
 .Font.Underline = wdUnderlineNone

 .TypeText "Risk Category: "
  
 .Font.Name = "Times New Roman"
 .Font.Size = 10
 '.Font.TextColor = vbBlack
 .Font.Bold = False
 .Font.Underline = wdUnderlineNone
 
 .TypeText ratg.Value
 .TypeParagraph
 
 .Font.Name = "Times New Roman"
 .Font.Size = 10
 '.Font.TextColor = vbBlack
 .Font.Bold = True
 .Font.Underline = wdUnderlineNone

 .TypeText "Branch Remarks: "
 .TypeParagraph
 .TypeParagraph
 
rs1.MoveNext
Wend
 
'    'Add header and footer

'ActiveDocument.Sections(1).Headers(wdHeaderFooterPrimary).Range.Text = "Audit & Inspection Division"
'ActiveDocument.Sections(1).Footers(wdHeaderFooterPrimary).Range.Text = Now()
End With

doc.Save
doc.Activate

MsgBox "Your Observation File has been exported at your saved path"

errorhandler:
'MsgBox Err.Description

End Sub

AWAISKAZMI is offline   Reply With Quote
Old 11-09-2017, 01:40 PM   #2
AWAISKAZMI
Newly Registered User
 
Join Date: Oct 2017
Posts: 12
Thanks: 1
Thanked 0 Times in 0 Posts
AWAISKAZMI is on a distinguished road
Re: Copy of Excel (OLE Object) and paste as table in word

No reply so far
AWAISKAZMI is offline   Reply With Quote
Old 11-09-2017, 09:15 PM   #3
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,313
Thanks: 2
Thanked 1,957 Times in 1,913 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Copy of Excel (OLE Object) and paste as table in word

Quote:
Originally Posted by AWAISKAZMI View Post
No reply so far
Just to get it in place, remember, it's only volunteers here, not employees where you can demand an answer in a short space of time. And 1 hour and 10 minutes is not that long.
You might have used the time to describe what you have tried and what error you've got.
So, where does it fail, (code line, comment out your error handling) and what is the error message?

__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Old 11-09-2017, 10:22 PM   #4
AWAISKAZMI
Newly Registered User
 
Join Date: Oct 2017
Posts: 12
Thanks: 1
Thanked 0 Times in 0 Posts
AWAISKAZMI is on a distinguished road
Re: Copy of Excel (OLE Object) and paste as table in word

Quote:
Originally Posted by JHB View Post
Just to get it in place, remember, it's only volunteers here, not employees where you can demand an answer in a short space of time. And 1 hour and 10 minutes is not that long.
You might have used the time to describe what you have tried and what error you've got.
So, where does it fail, (code line, comment out your error handling) and what is the error message?
Dear sir;

Thanks for your reply.

First all note few important points.
my project is to export data into word file. For other fields you would see my above code. data is correctly exporting into word file.

I have placed an excel file as attachment in a field
now through vba code i want to add table which should be copy from excel file and paste it in my word file.

i have obtained code from following file
thespreadsheetguru.com/blog/2014/5/22/copy-paste-an-excel-table-into-microsoft-word-with-vba[/url]

i could not succeed to call attached excel file, copy data and paste as table it into word file (remember word file is open and table is open). what code i should place in vba that may able me to do so.

I 'll be very thankful to you if you or any access expert could help me.
AWAISKAZMI is offline   Reply With Quote
Old 11-11-2017, 09:33 AM   #5
AWAISKAZMI
Newly Registered User
 
Join Date: Oct 2017
Posts: 12
Thanks: 1
Thanked 0 Times in 0 Posts
AWAISKAZMI is on a distinguished road
Re: Copy of Excel (OLE Object) and paste as table in word

Reply awaited.... Code needed
To get table from excel file stored in a field as attachment
to be copied and pasted into word file
AWAISKAZMI is offline   Reply With Quote
Old 11-11-2017, 09:47 AM   #6
Dystonia
Access 2002, 2010, 2016
 
Join Date: Nov 2017
Location: Rainy part of the UK
Posts: 17
Thanks: 1
Thanked 4 Times in 4 Posts
Dystonia is on a distinguished road
Re: Copy of Excel (OLE Object) and paste as table in word

Hi

Am I understanding your problem correctly ?

a) You want to use Access to open an Excel file
b) Then use Dynamic Data Exchange to transfer data in the Excel worksheet into Word
Dystonia is offline   Reply With Quote
Old 11-11-2017, 10:02 AM   #7
AWAISKAZMI
Newly Registered User
 
Join Date: Oct 2017
Posts: 12
Thanks: 1
Thanked 0 Times in 0 Posts
AWAISKAZMI is on a distinguished road
Re: Copy of Excel (OLE Object) and paste as table in word

Details are pointwise:
1 i am begginer level user
2 in a table i have different ""text" fields which are being export to word file
My new need is:
I added new field in table as attachment which contains excel file
.
I want to copy table from excel and paste in word file in which i am already exporting my text fields
.
You may see above in my code other fields are exporting
Please please provide me complete code

AWAISKAZMI is offline   Reply With Quote
Old 11-11-2017, 10:22 AM   #8
Dystonia
Access 2002, 2010, 2016
 
Join Date: Nov 2017
Location: Rainy part of the UK
Posts: 17
Thanks: 1
Thanked 4 Times in 4 Posts
Dystonia is on a distinguished road
Re: Copy of Excel (OLE Object) and paste as table in word

You can use the code below to open Excel and copy the contents of a worksheet to the clipboard which you can then paste into word

Code:
Rem *********************************
Rem Open Excel Workbook
Rem *********************************
    
    Dim WB As Object
    Dim WS As Object
    Dim XL As Object
    
    Set WB = GetObject("Path and filename to your Excel Workbook.xlsx")
    Set WS = WB.Worksheets(1) ' Alter if not sheet 1 
    Set XL = WB.Application ' identify Excel application
    
Rem *********************************
Rem Make things visible
Rem *********************************

    XL.visible = true
 
    XL.Windows(WB.Name).visible = true
    
    WS.Usedrange.Copy
 
Rem *********************************
Rem Paste into word at the correct point in the Document
Rem *********************************

   ' add your own code here

Rem *********************************
Rem Close Workbook and quit Excel
Rem *********************************

    XL.DisplayAlerts = False
    WB.Close
    XL.DisplayAlerts = True
    XL.Quit
Dystonia is offline   Reply With Quote
Old 11-11-2017, 11:21 AM   #9
AWAISKAZMI
Newly Registered User
 
Join Date: Oct 2017
Posts: 12
Thanks: 1
Thanked 0 Times in 0 Posts
AWAISKAZMI is on a distinguished road
Re: Copy of Excel (OLE Object) and paste as table in word

Lovely sir...
Thanks... Guide me another thing

Last edited by AWAISKAZMI; 11-11-2017 at 11:33 AM.
AWAISKAZMI is offline   Reply With Quote
Old 11-11-2017, 11:31 AM   #10
AWAISKAZMI
Newly Registered User
 
Join Date: Oct 2017
Posts: 12
Thanks: 1
Thanked 0 Times in 0 Posts
AWAISKAZMI is on a distinguished road
Re: Copy of Excel (OLE Object) and paste as table in word

Lovely sir...
Thanks... Guide me another thing
In my following code i will use

As my excel file is stored in field name "table" which i am calling through variable "tabl"

Shall i use your recommended code as

Set WB = GetObject(tabl)

Am i correct?



My code is

Dim d As Database
Dim rs1 As Recordset
Dim dept As Field
Dim head As Field
Dim obs As Field
Dim tabl As field
Dim rimp As Field
Dim ratg As Field


Set dbs1 = CurrentDb()
Set rs1 = dbs1.OpenRecordset("MySelectedObservations")
Set dept = rs1.Fields("Department_Name")
Set head = rs1.Fields("Observation_Heading")
Set obs = rs1.Fields("Observation_Details")
Set tabl = rsl.Fields("Table")
AWAISKAZMI is offline   Reply With Quote
Old 11-11-2017, 11:47 AM   #11
Dystonia
Access 2002, 2010, 2016
 
Join Date: Nov 2017
Location: Rainy part of the UK
Posts: 17
Thanks: 1
Thanked 4 Times in 4 Posts
Dystonia is on a distinguished road
Re: Copy of Excel (OLE Object) and paste as table in word

You need to post the value of your variable "tabl" so that I can see what it contains
Dystonia is offline   Reply With Quote
Old 11-11-2017, 11:04 PM   #12
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,575
Thanks: 55
Thanked 2,088 Times in 2,000 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Copy of Excel (OLE Object) and paste as table in word

if your Excel file is in OLE Object field, you need to extract it first before you can use it on your current code.

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Reply

Tags
excel

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
copy chart and paste into Word teel73 Excel 4 03-12-2014 08:01 AM
Using VB in Access to Copy & Paste from 1 Word doc to another AlanJBS Modules & VBA 0 09-14-2009 11:02 PM
automation word/excel copy/paste SamDeMan Modules & VBA 4 02-02-2006 01:20 PM
Copy & Paste from Access Table to Excel without header info BJS Tables 1 12-12-2004 08:54 PM
copy from excel & paste to word sando Modules & VBA 0 09-27-2004 09:54 PM




All times are GMT -8. The time now is 08:27 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World