MAC Excel macros to Copy data from excel and paste in word error

%3CLINGO-SUB%20id%3D%22lingo-sub-3286654%22%20slang%3D%22en-US%22%3EMAC%20Excel%20macros%20to%20Copy%20data%20from%20excel%20and%20paste%20in%20word%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3286654%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20create%20a%20macros%20to%20copy%20a%20table%20in%20excel%20and%20paste%20it%20in%20word%20document.%20I%20wrote%20the%20code%20and%20it%20works%20seamlessly%20in%20Windows%2C%20but%20when%20i%20run%20it%20in%20mac%20after%20few%20changes%20to%20file%20paths%20(as%20they%20are%20different%20%5C%20for%20windows%20and%20%2F%20for%20MAC%2C%20code%20stops%20at%20the%20this%20line%20and%20throughs%20an%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EwdDoc.Paragraphs(1).Range.PasteExcelTable%20_%3CBR%20%2F%3ELinkedToExcel%3A%3DFalse%2C%20_%3CBR%20%2F%3EWordformatting%3A%3DFalse%2C%20_%3CBR%20%2F%3ERTF%3A%3DFalse%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMAC%20Full%20CODE%3A%3C%2FP%3E%3CP%3E'%23%23%23%23%23%23%23%23%23%23%23%23%23%23%23%23%23%23%23%23%23%23%23%23%23%23%23%23%23%23%23%23%3CBR%20%2F%3E'Export%20as%20.doc%20(word%20file)%3CBR%20%2F%3E%3CBR%20%2F%3ESub%20View_And_Save_As_Doc()%3CBR%20%2F%3EApplication.DisplayAlerts%20%3D%20False%3CBR%20%2F%3E%3CBR%20%2F%3EDim%20ProjectAddress%20As%20String%2C%20SaveAsDocName%20As%20String%3CBR%20%2F%3EDim%20lrnwso%20As%20Long%2C%20lcnwso%20As%20Long%3CBR%20%2F%3EDim%20copyRng%20As%20Range%3CBR%20%2F%3EDim%20wsO%20As%20Worksheet%2C%20wsT%20As%20Worksheet%3CBR%20%2F%3EDim%20path%20As%20String%3CBR%20%2F%3E%3CBR%20%2F%3EDim%20oRng%20As%20Object%3CBR%20%2F%3EDim%20wdApp%20As%20Object%3CBR%20%2F%3EDim%20wdDoc%20As%20Object%3CBR%20%2F%3E%3CBR%20%2F%3E'Dim%20oRng%20As%20Range%3CBR%20%2F%3E%3CBR%20%2F%3EDim%20Logo%20As%20Object%3CBR%20%2F%3E%3CBR%20%2F%3EDim%20bStart%20As%20Boolean%3CBR%20%2F%3EbStart%20%3D%20False%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E'--Check%20if%20Input%20and%20output%20sheet%20exist%20and%20have%20data%3CBR%20%2F%3EOn%20Error%20Resume%20Next%3CBR%20%2F%3ESet%20wsT%20%3D%20ThisWorkbook.Worksheets(%22Tile%22)%3CBR%20%2F%3EIf%20wsT%20Is%20Nothing%20Then%3CBR%20%2F%3EMsgBox%20%22Sheet%20-%20'Tile'%20Not%20Exist%2C%20Please%20check%22%2C%20vbInformation%3CBR%20%2F%3EExit%20Sub%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3ESet%20wsO%20%3D%20ThisWorkbook.Worksheets(%22Output%22)%3CBR%20%2F%3EIf%20wsO%20Is%20Nothing%20Then%3CBR%20%2F%3EMsgBox%20%22Sheet%20-%20'Output'%20Not%20ExistPlease%20check%22%2C%20vbInformation%3CBR%20%2F%3EExit%20Sub%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EOn%20Error%20GoTo%200%3CBR%20%2F%3E'------------------%3CBR%20%2F%3E%3CBR%20%2F%3E'--create%20filenameToBeSaved%20as%20Doc%3CBR%20%2F%3EwsT.Activate%3CBR%20%2F%3EProjectAddress%20%3D%20VBA.Trim(wsT.Range(%22B2%22).Value)%3CBR%20%2F%3ESaveAsDocName%20%3D%20VBA.Trim(wsT.Range(%22A1%22).Value)%20%26amp%3B%20%22-%22%20%26amp%3B%20ProjectAddress%20%26amp%3B%20%22.docx%22%3CBR%20%2F%3E'Debug.Print%20SaveAsDocName%3CBR%20%2F%3E%3CBR%20%2F%3E'%3D%3Dset%20MS%20WORDapplication%3CBR%20%2F%3EOn%20Error%20Resume%20Next%3CBR%20%2F%3ESet%20wdApp%20%3D%20GetObject(%2C%20%22Word.Application%22)%3CBR%20%2F%3EOn%20Error%20GoTo%200%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20wdApp%20Is%20Nothing%20Then%3CBR%20%2F%3ESet%20wdApp%20%3D%20CreateObject(Class%3A%3D%22Word.Application%22)%3CBR%20%2F%3EbStart%20%3D%20True%3CBR%20%2F%3EEnd%20If%3C%2FP%3E%3CP%3E'--if%20output%20doc%20is%20already%20open%2C%20close%20it%2C%20delete%20it%3CBR%20%2F%3EOn%20Error%20Resume%20Next%3CBR%20%2F%3EDim%20openDoc%20As%20Object%3CBR%20%2F%3ESet%20openDoc%20%3D%20wdApp.Documents.Open(Application.ActiveWorkbook.path%20%26amp%3B%20%22%2F%22%20%26amp%3B%20SaveAsDocName)%3CBR%20%2F%3EopenDoc.Activate%3CBR%20%2F%3EopenDoc.Close%20SaveChanges%3A%3DwdDoNotSaveChanges%3CBR%20%2F%3E%3CBR%20%2F%3EKill%20Application.ActiveWorkbook.path%20%26amp%3B%20%22%2F%22%20%26amp%3B%20SaveAsDocName%3CBR%20%2F%3EOn%20Error%20GoTo%200%3CBR%20%2F%3E%3CBR%20%2F%3E'--Create%20WORD%20Application%20object%3CBR%20%2F%3EOn%20Error%20Resume%20Next%3CBR%20%2F%3ESet%20wdApp%20%3D%20GetObject(%2C%20%22Word.Application%22)%3CBR%20%2F%3EOn%20Error%20GoTo%200%3CBR%20%2F%3EIf%20wdApp%20Is%20Nothing%20Then%3CBR%20%2F%3ESet%20wdApp%20%3D%20CreateObject(Class%3A%3D%22Word.Application%22)%3CBR%20%2F%3EbStart%20%3D%20True%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3E'--Open%20Word%20Template%20file%3CBR%20%2F%3EwdApp.Visible%20%3D%20True%3CBR%20%2F%3E%3CBR%20%2F%3EOn%20Error%20Resume%20Next%3CBR%20%2F%3ESet%20wdDoc%20%3D%20wdApp.Documents.Open(Application.ActiveWorkbook.path%20%26amp%3B%20%22%2F%22%20%26amp%3B%20%22Tile%20Selection_Template.Docx%22)%3CBR%20%2F%3EIf%20wdDoc%20Is%20Nothing%20Then%3CBR%20%2F%3EMsgBox%20%22Word%20Template%20file%20-%20Tile%20Selection_Template.Docx%20'%20Not%20Exist%2C%20Please%20check%22%2C%20vbInformation%3CBR%20%2F%3EExit%20Sub%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EOn%20Error%20GoTo%200%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3EwdApp.Visible%20%3D%20True%3CBR%20%2F%3E%3CBR%20%2F%3EwdDoc.PageSetup.Orientation%20%3D%200%20'Portrait%3CBR%20%2F%3E%3CBR%20%2F%3E'--set%20worksheet%20%2F%20copy%20range%3CBR%20%2F%3E%3CBR%20%2F%3EwsO.Activate%3CBR%20%2F%3Elrnwso%20%3D%20wsO.Cells.Find(%22*%22%2C%20searchorder%3A%3DxlByRows%2C%20searchdirection%3A%3DxlPrevious).Row%3CBR%20%2F%3ESet%20copyRng%20%3D%20wsO.Range(Cells(1%2C%201)%2C%20Cells(100%2C%204))%3CBR%20%2F%3E%3CBR%20%2F%3E'--%20copy%20all%20Data%3CBR%20%2F%3EcopyRng.Copy%3CBR%20%2F%3E'--paste%20in%20word%20document%3CBR%20%2F%3E%3CBR%20%2F%3EwdDoc.Paragraphs(1).Range.PasteExcelTable%20_%3CBR%20%2F%3ELinkedToExcel%3A%3DFalse%2C%20_%3CBR%20%2F%3EWordformatting%3A%3DFalse%2C%20_%3CBR%20%2F%3ERTF%3A%3DFalse%3CBR%20%2F%3E%3CBR%20%2F%3EApplication.CutCopyMode%20%3D%20False%3CBR%20%2F%3E%3CBR%20%2F%3E'--save%20only%20but%20not%20close%3CBR%20%2F%3EwdDoc.SaveAs%20Application.ActiveWorkbook.path%20%26amp%3B%20%22%2F%22%20%26amp%3B%20SaveAsDocName%20%26amp%3B%20%22.docx%22%20'12%20%3D%20wdFormatXMLDocument%3CBR%20%2F%3E%3CBR%20%2F%3ESet%20wdDoc%20%3D%20Nothing%3CBR%20%2F%3ESet%20wdApp%20%3D%20Nothing%3CBR%20%2F%3E%3CBR%20%2F%3EExit%20Sub%3CBR%20%2F%3E%3CBR%20%2F%3E'ErrorHandler%3A%3CBR%20%2F%3E'MsgBox%20%22%20Some%20Error%20Occured%2C%20Please%20Close%20the%20document%20and%20Try%20again!%22%2C%20vbInformation%3CBR%20%2F%3E%3CBR%20%2F%3E'MsgBox%20%22Done!%20Word%20Document%20Created%20and%20saved%20in%20current%20folder%22%2C%20vbInformation%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20let%20me%20know%20if%20there%20is%20some%20error%20or%20is%20there%20any%20other%20way%20to%20do%20with%20excel%20macros%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3286654%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

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

0 Replies