Im running code in excel from access. So access does what it;s doing then calls excel to run a macro.
This isn't causing too much issue at the moment but I dont think it's right. Excel remains in task manager wehn I have closed it from access.
I have tested this using a simple message box with no issues at all (Ihe only code I changed in access for this was the macro that im calling). So the problem has to be within the code in excel.
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
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
With objWord
.Visible = True
Set objDoc = .Documents.Open(strFolder)
With objDoc.Bookmarks("RISKS").Range
.Characters.Last.Next.PasteAppendTable
End With
.Activate
End With
Set objWord = Nothing: Set objDoc = Nothing
Application.CutCopyMode = False
Exit Sub
Errorcatch:
Debug.Assert False
MsgBox Err.Description
Resume
End Subafter reading on other posts/forums its because im not closing something here properly. Or referencing properly. Is anything obvious?
Andy.