I have this code working in parts but not together. I can pen a word document using the top part and I can insert to a table using the bottom part (but not using file select I have to define the document path.)
When I put these together I get type mismatch. Any ideas why?
Type mismatch happens once i select the word document.
Code
Sub ExcelDataToWord()
Dim objWord As Object
Dim ws As Worksheet
Dim lngLastRow As Long
On Error GoTo Errorcatch
lngLastRow = Sheets("RISKS").Range("A65535").End(xlUp).Row
Set ws = ThisWorkbook.Sheets("RISKS")
Set objWord = CreateObject("Word.Application")
objWord.Visible = True
'Optimize Code
Application.ScreenUpdating = False
Application.EnableEvents = False
ws.Range("A4" & ":H" & lngLastRow).Copy
'------------browse---------------
Dim fileExplorer As FileDialog
Set fileExplorer = Application.FileDialog(msoFileDialogFilePicker)
'--------------------defaulting folder------------------------
With fileExplorer
.Title = "Select a Folder"
.AllowMultiSelect = False
.ButtonName = "Select"
.InitialView = msoFileDialogViewList
.InitialFileName = "\\server\general\RAMS\RAM_RAMS"
If Right(strName, 1) <> "\" Then
strFolder = strFolder & "\"
End If
If .Show <> -1 Then
Exit Sub
Else
strFolder = .SelectedItems(1) & "\"
End If
End With
'--------------------defaulting folder------------------------
'------------browse---------------
objWord.Documents.Open Filename:=fileExplorer
'pastes the value of cell at the bookmark
With objWord.ActiveDocument.Bookmarks("RISKS").Range.Characters.Last.Next.PasteAppendTable
End With
'Optimize Code
Set objWord = Nothing
Application.ScreenUpdating = True
Application.EnableEvents = True
'Clear The Clipboard
Application.CutCopyMode = False
Exit Sub
Errorcatch:
MsgBox err.Description
End Sub
Display More