PDA

View Full Version : Data update does not work from VBS initiated open



abesimpson
05-06-2008, 09:43 AM
I have a spreadsheet which is linked to an Access table (Data/Data Import). The permissions are set to have the data update when the spreadsheet opens. This works when the spreadsheet is opened from Excel (File/Open) or when the spreadsheet file is opened.

I have a problem when I call the spreadsheet from a vbscript:Set xlobj = CreateObject("Excel.application")
xlobj.visible = True
xlobj.workbooks.open "C:\name of file.xls"
xlobj.Run "Start"
Sub Start()
Application.OnTime Now () + TimeValue("00:00:30"), "SaveExit"
End Sub
Sub SaveExit()
Activeworkbook.Save
Application.Quit
End Sub In this case the spreadsheet opens, the macros execute (yes I know this is clumsy code, it is just for example), but the data-link update is ignored.

I know it's me, what am I doing wrong?

Thanks in advance

a

Bob Phillips
05-06-2008, 10:24 AM
Maybe



Set xlobj = CreateObject("Excel.application")
xlobj.visible = True
Set wb = xlobj.workbooks.open("C:\name of file.xls")
xlobj.Run "'" & wb.name & "'!Start"

abesimpson
05-06-2008, 12:57 PM
Nope same results. Bugger of a problem in that it appears to work... but doesn't (spreadsheet opens, macro executes, spreadsheet closes).

Thanks

a

Bob Phillips
05-06-2008, 02:46 PM
But isn't that what is supposed to happen? If not, wat should?

abesimpson
05-06-2008, 04:21 PM
The data link to an Access table does not update when the spreadsheet is started from the VBScript.

When I open the spreadsheet manually the data updates as per normal.

I want to start the spreadsheet, update the data link, run the macro (which includes and exit command).

Regards

a