VBScript Running Excel Modules in 2010 (Upgrade)

[ianb]

Hi all,


I have loads of modules in an excel spreadsheet (Dashboard) that I run via vbscripts.

They all work fine in excel 2003. they bypass the pivottable updates (as the dashboard is connected to an access db. - excel : refresh data when opening a file)


I have upgraded to 2010 and none of my vbscripts work. Also when testing on excel 2007 it loads refreshes and loads the data yet the module times out.


1. what changes do I need on my vbscript to open and run a module.

2. can I bypass the refresh data (else do i need to switch this off and doa click button manual load to avoid the conflicts).


listed below is one of my vbscripts. I will at the end change all files, etc to .xlsm yet for the time being I keep .xls until all vbscripts are working correcly.


I aim for the simple solution. Open spreadsheet run modules then close spreadsheets.


option explicit

Dim oExcel, oStats

Set oExcel = CreateObject("Excel.Application")


oExcel.Visible = True

oExcel.DisplayAlerts = False

oExcel.AskToUpdateLinks = False


Set oStats = oExcel.Workbooks.Open("H:ReportsStatistics DataDashboard.xls")


oExcel.Run "ModuleMenuRun"

 

[Hui]

Ian


Saving the file as a *.xls will force Excel to open the file in Compatibility mode

Hence you may not get access to the right Excel resources to do what you want


Why not try saving one file as a *.xlsm filetype and try again


Also when something doesn't work, tell us what line it stops on

 

[ianb]

.xlsm


error : unable to get to the Open Property of the workbook class


Code : 800A03Ec


Line 11:


Set oStats = oExcel.Workbooks.Open("H:ReportsStatistics DataDashboard.xlsm")

 

[SirJB7]

Hi, ianb!

Any chance of uploading a sample .xls workbook, i.e., one of the original and running in 2003 version?

Regards!

 

[shrivallabha]

What OS do you use, I hope XP.


See if this thread is of any help to you.

http://www.vbaexpress.com/forum/showthread.php?t=42794

 

[ianb]

With .xls


error : The server threw an exception


code : 80010105


oExcel.Run "ModuleMenuRun"


Thinking I need to switch off the automupdate when opening and then have a manual auto update. this way the module will run ??? loads of pivot tables to switch off over 50 connections. any ideas from anyone ?? thanks.

 

[ianb]

Its Now. Windows 7 and Office 2010.


I was move onto it last week. have changed alot of my work. yet this is the biggest challenge.

 

[ianb]

Hi JB. the module are very large. they do various configurations to the dashboard.


1. the dashboard uploads the data.

2. i think the module times out as the dat takes about 1-2 mins to upload into teh pivots.


I can give a sample of the dashboard.


I think module calling in vbscript can bypass data upload in 2003 yet not in 2010.

the only solution would be to switch off manual refresh (data upload on all pivots. Then I could trigger each vscript to upload and in each module do a refresh where a refresh is required.


Just when all was calm in 2003 along comes 2010 !!! lol

 

[SirJB7]

Hi, ianb!

I use Win7 x64 and Office 2010 Pro Plus x86, so if you want I give a try just advise.

Regards!

 

[ianb]

option explicit


Dim oExcel, oStats


Set oExcel = CreateObject("Excel.Application")


oExcel.Visible = True

oExcel.DisplayAlerts = False

oExcel.AskToUpdateLinks = False


Set oStats = oExcel.Workbooks.Open("H:ReportsStatistics DataPSO Dashboard.xls")


oExcel.Run "PivotAndDropDownUpdateNow"

 

[ianb]

And Module all the top lines are the same


oExcel.Run "AddToCellMenu"


Here are examples of modules :


Sub PivotAndDropDownUpdateNow()


Application.Run "DropDownUpdateNow" 'this updates all dropdown boxes

Application.Run "PivotUpdateNow" 'this updates all pivto tables


Sheets("Dashboard (Overview)").Select


'ActiveWorkbook.Save

Application.DisplayAlerts = False


ChDir "H:ReportsStatistics Data"


ActiveWorkbook.SaveAs Filename:= _

"H:ReportsStatistics DataPSO Dashboard.xls", FileFormat:=xlNormal, _

Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _

CreateBackup:=False


Application.DisplayAlerts = True


Application.Quit


End Sub

 

[ianb]

It never makes it to the module as the updates take too long.


I think simple solutino would be to which of refresh on all pivot tables and place refresh in all vba programs required ?


Is this the best idea..... ?


Also is it best from experience to go from .xls to .xlsm ?


Thanks.

 

[SirJB7]

Hi, ianb!

Without the related sample data it's difficult to verify the code since I'd have to build the dummy data structure to properly test it, so I'll be passing.

Regarding your last question, absolutely yes the switch to .xlsm/.xlsx. I'd rather convert all files opening and saving them in the new formats than waiting for the first time use.

Regards!

 

[Colin Legg]

I would definitely stick with converting the book into an xlsm and trying it from there. In my experiece running workbooks in compatability mode can give all sorts of bugs which were fixed once the workbooks had been compatability checked and converted. The pivot table model changed quite a bit between 2003 and 2010. I think you should convert the file to an xlsm, open it manually and debug all of the code to ensure it complies with the Excel 2010 object model before trying to run it from your VBS script.


The 'unable to get to the Open Property of the workbook class' error you got implies that the xlsm workbook has been disabled. Start Excel 2010, go to File > Options > Add-Ins, in the dropdown box at the bottom choose Manage Disabled Items and Enable the xlsm workbook if it is listed in there.

 

[ianb]

Thansk Colin. Will follow the advise and test from there. Long day testing tomorrow... many thanks for the other input by the members here. Will keep you posted.. thanks.

 

[Hui]

Ian


Taking your original code:

[pre]

option explicit
Dim oExcel, oStats
Set oExcel = CreateObject("Excel.Application")

oExcel.Visible = True
oExcel.DisplayAlerts = False
oExcel.AskToUpdateLinks = False

Set oStats = oExcel.Workbooks.Open("H:ReportsStatistics DataDashboard.xls")
oExcel.Run "ModuleMenuRun"
I think the last 2 lines should be:

Set oStats = oExcel.Workbooks.Open("H:ReportsStatistics DataDashboard.xlsm")
oExcel.Application.Run "ModuleMenuRun"

[/pre]
and note that the file to run a macro must be a *.xlsm or *.xlsb file type