In Access im running code to update a word document. This code also runs some excel macros shown below:
Code:
xl.Run "ThisWorkbook.datarefresh"
xl.Run "ThisWorkbook.BetterExcelDataToWord"
In excel I have a table of data from access. I cant consistantly get this to update before its pasted into word. Code shown here:
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
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
some other issues:
If I run datarefresh from inside excel it updates no problem. If i run it from access I get error 1004 “Application-defined or Object-defined error”.
any suggestions appreciated.