Linking between Access and Excel

[houseofturner]

I have an Sage ACT! database which I need to connect to Access 2003 so I can use previously written reports in Access. I cant seem to do this directly but I can create a Excel workbook which use an OLE link to get the data from ACT!, I can then link this to Access using a linked table.

The problem that I have is that I need to refresh the data in the Excel file and then only way this can be done is opening it and closing it again. I am using the code below in Access to do this but it seems to only work once.

I think this is because it does not close the workbook properly as to get it to work a second time I need to go into task manager and kill any Excel processes.

The code I am using is:

Dim appexcel As Object
Set appexcel = CreateObject("Excel.Application")
appexcel.displayalerts = False
appexcel.workbooks.Open "T:\database\LiveACTContactData.xls"
appexcel.activeworkbook.refreshall
appexcel.Visible = False

I assume that this is because the Excel workbook is not closing after the process but is hidden but how do I do this?

 

[Frosty328]

I don't think you need to use VBL coding in Access to refresh data in Excel. You say it only refreshes when opening and closing it. Your soultion may be as simple as this:

What you can do is first open your Excel document and in the toolbar click on Data>Connections. Then choose what connection(s) you want to refresh and click on Properties. You can than choose various options of how you want it refreshed, such as creating a set time.

I'm not sure if this is what you exactly mean't, but I hope it does help.