Data Failing to update

[Homegrownandy]

In Access im running code to update a word document. This code also runs some excel macros shown below:

      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:

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.

[June7]

If I run datarefresh from inside access it updates no problem. If i run it from access I get error 1004 “Application-defined or Object-defined error”.

Those two statements contradict each other.

Which line throws error?

[Micron]

If I run datarefresh from inside access it updates no problem.

I'll wager that was a typo and this is an Excel procedure since it would never run from Access. It doesn't define and set the required automation objects that you'd need in order to run from the Access side of things.

[Homegrownandy]

I have updated the first post, there were several mistakes in it.

sorry, If i run it from inside excel* it runs fine.
Its when I call the macro in access its a problem.

Ive only provided a small part of the code in access because there is so much of it.

Ive edited the datarefresh macro several times for testing, its also included in the main macro (just to clear up any confusion).

datarefresh macro now looks like this:

Sub datarefresh()
Sheets("RISKS Import").Range("A1").Select

ActiveWorkbook.RefreshAll
End Sub

Running this inside excel it does work.

Micron im not sure what to do with your advice there, but it's important to note I have to use late binding. Could you elabourate please?

thanks, Andy

[June7]

Just as code declares and sets Word application and document object variables, to manipulate Excel from Access need to also declare and set Excel application and workbook object variables.

However, if this code is in Access then it should error with "Object variable or With block variable not set."

So, I am still confused.

[Homegrownandy]

Right, Its the line in access;

 xl.Run "ThisWorkbook.datarefresh"

I get the error mentioned earlier. "1004 “Application-defined or Object-defined error”."

but again; inside excel it runs no problem.

[Homegrownandy]

@ june the data refresh code is in excel and run from access. Sorry for any earlier confusion I caused.

[Micron]

the answers in posts 3 and 5 have basically pinpointed the problem, but june7 said it better. You cannot just take code that runs in Excel and use it in Access as is. If you need code in both places, you will need 2 entirely different procedures - 1 in Excel written for Excel, 1 in Access written to manipulate Excel. The Access one needs to properly declare, set and use the Excel objects.
See here http://www.accessmvp.com/kdsnell/EXCEL_MainPage.htm
The one you want should be "Exporting to Excel..."

[Homegrownandy]

I think you are missunderstanding what im doing.

the excel code only ever runs inside excel.

the only code from access i have shown on here is this:

      xl.Run "ThisWorkbook.datarefresh"
   xl.Run "ThisWorkbook.BetterExcelDataToWord"

Which is part of much longer set of code that I didnt think was relevent.

I'm a lot closer to resolving the issue. I should be okay.

[June7]

If you want Access to call code that is in Excel workbook, still have to declare/set/open Excel objects. You haven't shown any code doing that. Review http://access-excel.tips/run-excel-m...om-access-vba/

I could call the Excel procedure only if it is in a general module, not behind Workbook or Sheet.

[Homegrownandy]

sorry for being so unclear, ill sork on it and make a new post if i fail to achieve wwhat i want to do.

Thanks.