I have the code shown below. this code is an in excel via a command in access. Access does not keep the formatting i want when pasting into a word table, so I have used excel as a "middle man". Everything works fine but the excel app is showing as closed on my computer. However; If i attempt to open this workbook it says its already in use by me. Or if i try to close the PC it says i need to save changes in this workbook.
How can i properly close it via code once its completed the copy/paste task?
thanks, Andy
Code
Sub BetterExcelDataToWord()
Dim objWord As Object, objDoc As Object
Dim strFolder As String, strName As String
Dim ws As Worksheet
Dim lngLastRow As Long
On Error GoTo Errorcatch
'------------browse---------------
'--------------------defaulting folder------------------------
'With Application.FileDialog(msoFileDialogFilePicker)
' .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---------------
'ActiveWorkbook.RefreshAll
Dim Ticker As Range
Sheets("PasteSpecial").Activate
Sheets("PasteSpecial").Range("A4:H65").Delete
Application.Wait (Now + TimeValue("00:00:05"))
Sheets("RISKS").Activate
Set Ticker = Range(Cells(4, 1), Cells(65, 8))
Ticker.Copy
Sheets("PasteSpecial").Activate
Cells(4, 1).PasteSpecial xlPasteValues
strFolder = "\\server\general\RAMS\RAM_RAMS" & Cells(1, 9).Value
Debug.Print strFolder
Set ws = ThisWorkbook.Sheets("PasteSpecial")
lngLastRow = [LOOKUP(2,1/(A1:A65000<>""),ROW(A1:A65000))]
Set objWord = CreateObject("Word.Application")
ws.Range("A4" & ":H" & lngLastRow).Copy
Debug.Print
'open the word doc
With objWord
.Visible = True
Set objDoc = .Documents.Open(strFolder)
'pastes the value of cell at the bookmark
With objDoc.Bookmarks("RISKS").Range
.Characters.Last.Next.PasteAppendTable
'.Tables(1).Rows(1).HeadingFormat = True
End With
.Activate
End With
Set objWord = Nothing: Set objDoc = Nothing
'Clear The Clipboard
Application.CutCopyMode = False
'Application.DisplayAlerts = False
'ActiveWorkbook.Close
'Application.DisplayAlerts = True
Exit Sub
Errorcatch:
Debug.Assert False
MsgBox err.Description
' This is temporary, if you leave it in it will go into an endless loop so do not forget to remove
Resume
End Sub
Sub datarefresh()
Sheets("RISKS Import").Range("A1").Select
ActiveWorkbook.RefreshAll
'Worksheets("RISKS Import").ListObjects("Table1").Refresh
End Sub
Display More