How to force to continue vba execution after a function call

[is49460]

Hello,

I have the following dilemma: I have a database and an excel report with a pivot table that feeds from some tables in that database. I refresh that pivot table daily to obtain new data from the database. I want to create a VBA function in the database to open the excel report to refresh it automatically. Everything works fine (opening excel, running excel’s functions, etc). The problem I am facing is that when function attempts to refresh pivot in excel the pivot cannot access the table in the database because the database is in locked state during execution of the VBA. I hope that makes sense... Is there a way to force VBA to continue code execution (and exit out of function) after making a call to excel's function? Any suggestions are appreciated.

Thanks!!

[SoftwareMatters]

It might help if you post your code.

If you are linked to a table in Access from Excel 2007 I'm sure there is an option for Excel to refresh itself after a set number of minutes. Might be worth checking this out.

[is49460]

Here is a simplified code:

Code in the database:
Dim xlApp As Object
Dim xlWb As Object
Dim xlWS As Worksheet
Dim strSheet As String
Dim sFullPath As String

sFullPath = "c:\MyExcelFile.xls"
Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Open(sFullPath, True)
xlApp.Visible = True

xlApp.Run "'MyExcelFile.xls'!RefreshReport"

Code in Excel in RefreshReport Sub:

Sheets("Report").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh

The external source data for PivotTable1 is a table in the database

Thanks!!!!

[is49460]

I guess one solution is to rewrite excel code to access, but I wanted to avoid doing that due to amount of code and there is a need to run the code from excel at times...

[SoftwareMatters]

Have you looked at the auto refresh within Excel?

[is49460]

Have you looked at the auto refresh within Excel?

Only admin should be able to do the refresh. Daily refresh is not always consistent, depending on other factors. I don't think autorefresh would work, but thanks for the suggestion!