Open, refresh and close Excel from Access

[Scott]

I'm trying to get access to open, refresh and then close an excel
spreadsheet for me. Below is the code I'm using to open and close Excel, I
just can't get it to refresh my data linked back to my database, the problem
appears to be that if I open Excel myself a window pops up and asks to
Enable or Disable Automatic Refresh. Any thoughts on how to get the
spreahseet to refresh?

Dim appexcel As Object
Set appexcel = CreateObject("Excel.Application")
appexcel.workbooks.Open "c:\outlookloader.xls"
appexcel.Visible = True

[RoyVidar]

Scott wrote:

I'm trying to get access to open, refresh and then close an excel
spreadsheet for me. Below is the code I'm using to open and close Excel, I
just can't get it to refresh my data linked back to my database, the problem
appears to be that if I open Excel myself a window pops up and asks to
Enable or Disable Automatic Refresh. Any thoughts on how to get the
spreahseet to refresh?

Dim appexcel As Object
Set appexcel = CreateObject("Excel.Application")
appexcel.workbooks.Open "c:\outlookloader.xls"
appexcel.Visible = True

I think the second arguement in the workbooks.open method, is
UpdateLinks. Try

appexcel.workbooks.Open "c:\outlookloader.xls", True

--
Roy-Vidar

[Scott]

Hi Roy,

That didn't do it, it still won't update the linked data, any more ideas?

Scott

"RoyVidar" <ro*************@yahoo.nowrote in message
news:46**********************@nn1-read.tele2.net...

Scott wrote:

>I'm trying to get access to open, refresh and then close an excel
spreadsheet for me. Below is the code I'm using to open and close Excel,
I
just can't get it to refresh my data linked back to my database, the
problem
appears to be that if I open Excel myself a window pops up and asks to
Enable or Disable Automatic Refresh. Any thoughts on how to get the
spreahseet to refresh?

Dim appexcel As Object
Set appexcel = CreateObject("Excel.Application")
appexcel.workbooks.Open "c:\outlookloader.xls"
appexcel.Visible = True

I think the second arguement in the workbooks.open method, is
UpdateLinks. Try

appexcel.workbooks.Open "c:\outlookloader.xls", True

--
Roy-Vidar

[RoyVidar]

Scott wrote:

Hi Roy,

That didn't do it, it still won't update the linked data, any more ideas?

Scott

"RoyVidar" <ro*************@yahoo.nowrote in message
news:46**********************@nn1-read.tele2.net...

>Scott wrote:

>>I'm trying to get access to open, refresh and then close an excel
spreadsheet for me. Below is the code I'm using to open and close Excel,
I
just can't get it to refresh my data linked back to my database, the
problem
appears to be that if I open Excel myself a window pops up and asks to
Enable or Disable Automatic Refresh. Any thoughts on how to get the
spreahseet to refresh?

Dim appexcel As Object
Set appexcel = CreateObject("Excel.Application")
appexcel.workbooks.Open "c:\outlookloader.xls"
appexcel.Visible = True

I think the second arguement in the workbooks.open method, is
UpdateLinks. Try

appexcel.workbooks.Open "c:\outlookloader.xls", True

--
Roy-Vidar

I was so sure I'd used this recently, that I didn't even test...

The refreshlinks arguement takes 1, 2 or 3, where 3, as I now
understand, *should*, mean an automatic refresh when opening, but it
didn't when I finally tested.

What worked, was invoking the .RefreshAll method of the workbook, i e

appexcel.workbooks.Open "c:\outlookloader.xls"
appexcel.activeworkbook.refreshall

or, as I would probably do, have a separate workbook object

set wr = appexcel.workbooks.Open("c:\outlookloader.xls")
wr.refreshall
appexcel.visible = true
set wr = nothing
set appexcel = nothing

Hopefully this brings you a step further, and as I've been told by MS
Access upteen times, I'm sorry for the inconvenience...

--
Roy-Vidar

[Scott]

Hi Roy,

That appears to be the fix, thank you very much.

Scott

"RoyVidar" <ro*************@yahoo.nowrote in message
news:46**********************@nn1-read.tele2.net...

Scott wrote:

>Hi Roy,

That didn't do it, it still won't update the linked data, any more ideas?

Scott

"RoyVidar" <ro*************@yahoo.nowrote in message
news:46**********************@nn1-read.tele2.net...

>>Scott wrote:
I'm trying to get access to open, refresh and then close an excel
spreadsheet for me. Below is the code I'm using to open and close
Excel, I
just can't get it to refresh my data linked back to my database, the
problem
appears to be that if I open Excel myself a window pops up and asks to
Enable or Disable Automatic Refresh. Any thoughts on how to get the
spreahseet to refresh?

Dim appexcel As Object
Set appexcel = CreateObject("Excel.Application")
appexcel.workbooks.Open "c:\outlookloader.xls"
appexcel.Visible = True

I think the second arguement in the workbooks.open method, is
UpdateLinks. Try

appexcel.workbooks.Open "c:\outlookloader.xls", True

--
Roy-Vidar

I was so sure I'd used this recently, that I didn't even test...

The refreshlinks arguement takes 1, 2 or 3, where 3, as I now
understand, *should*, mean an automatic refresh when opening, but it
didn't when I finally tested.

What worked, was invoking the .RefreshAll method of the workbook, i e

appexcel.workbooks.Open "c:\outlookloader.xls"
appexcel.activeworkbook.refreshall

or, as I would probably do, have a separate workbook object

set wr = appexcel.workbooks.Open("c:\outlookloader.xls")
wr.refreshall
appexcel.visible = true
set wr = nothing
set appexcel = nothing

Hopefully this brings you a step further, and as I've been told by MS
Access upteen times, I'm sorry for the inconvenience...

--
Roy-Vidar