Forum Discussion
Ranadeep
Apr 15, 2022Copper Contributor
MAC Excel macros to Copy data from excel and paste in word error
Hi,
I am trying to create a macros to copy a table in excel and paste it in word document. I wrote the code and it works seamlessly in Windows, but when i run it in mac after few changes to file paths (as they are different \ for windows and / for MAC, code stops at the this line and throughs an error.
wdDoc.Paragraphs(1).Range.PasteExcelTable _
LinkedToExcel:=False, _
Wordformatting:=False, _
RTF:=False
MAC Full CODE:
'################################
'Export as .doc (word file)
Sub View_And_Save_As_Doc()
Application.DisplayAlerts = False
Dim ProjectAddress As String, SaveAsDocName As String
Dim lrnwso As Long, lcnwso As Long
Dim copyRng As Range
Dim wsO As Worksheet, wsT As Worksheet
Dim path As String
Dim oRng As Object
Dim wdApp As Object
Dim wdDoc As Object
'Dim oRng As Range
Dim Logo As Object
Dim bStart As Boolean
bStart = False
'--Check if Input and output sheet exist and have data
On Error Resume Next
Set wsT = ThisWorkbook.Worksheets("Tile")
If wsT Is Nothing Then
MsgBox "Sheet - 'Tile' Not Exist, Please check", vbInformation
Exit Sub
End If
Set wsO = ThisWorkbook.Worksheets("Output")
If wsO Is Nothing Then
MsgBox "Sheet - 'Output' Not ExistPlease check", vbInformation
Exit Sub
End If
On Error GoTo 0
'------------------
'--create filenameToBeSaved as Doc
wsT.Activate
ProjectAddress = VBA.Trim(wsT.Range("B2").Value)
SaveAsDocName = VBA.Trim(wsT.Range("A1").Value) & "-" & ProjectAddress & ".docx"
'Debug.Print SaveAsDocName
'==set MS WORDapplication
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
On Error GoTo 0
If wdApp Is Nothing Then
Set wdApp = CreateObject(Class:="Word.Application")
bStart = True
End If
'--if output doc is already open, close it, delete it
On Error Resume Next
Dim openDoc As Object
Set openDoc = wdApp.Documents.Open(Application.ActiveWorkbook.path & "/" & SaveAsDocName)
openDoc.Activate
openDoc.Close SaveChanges:=wdDoNotSaveChanges
Kill Application.ActiveWorkbook.path & "/" & SaveAsDocName
On Error GoTo 0
'--Create WORD Application object
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
On Error GoTo 0
If wdApp Is Nothing Then
Set wdApp = CreateObject(Class:="Word.Application")
bStart = True
End If
'--Open Word Template file
wdApp.Visible = True
On Error Resume Next
Set wdDoc = wdApp.Documents.Open(Application.ActiveWorkbook.path & "/" & "Tile Selection_Template.Docx")
If wdDoc Is Nothing Then
MsgBox "Word Template file - Tile Selection_Template.Docx ' Not Exist, Please check", vbInformation
Exit Sub
End If
On Error GoTo 0
wdApp.Visible = True
wdDoc.PageSetup.Orientation = 0 'Portrait
'--set worksheet / copy range
wsO.Activate
lrnwso = wsO.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
Set copyRng = wsO.Range(Cells(1, 1), Cells(100, 4))
'-- copy all Data
copyRng.Copy
'--paste in word document
wdDoc.Paragraphs(1).Range.PasteExcelTable _
LinkedToExcel:=False, _
Wordformatting:=False, _
RTF:=False
Application.CutCopyMode = False
'--save only but not close
wdDoc.SaveAs Application.ActiveWorkbook.path & "/" & SaveAsDocName & ".docx" '12 = wdFormatXMLDocument
Set wdDoc = Nothing
Set wdApp = Nothing
Exit Sub
'ErrorHandler:
'MsgBox " Some Error Occured, Please Close the document and Try again!", vbInformation
'MsgBox "Done! Word Document Created and saved in current folder", vbInformation
End Sub
Please let me know if there is some error or is there any other way to do with excel macros
- AshleyZMCopper Contributor
Hi, did you ever figure this out or get a reply on another site? I need the same help (somewhat different) on a Mac -Copy whatever is selected in Excel and paste it into the currently open Word document - to the bottom of the page (last page). Or where the cursor is, if that's possible... Any ideas?
Thanks
-Ashley