No Data Upload - Fingers on Buzzers - First Answer 2 Points

[ianb]

Hi,


I have been using excel 2003 and if I want to load the spreasheet that automatically refreshes the data I just click on the file.


If I want to bypass the uploading of the data (refresh pivot tables) I just point a vbscript to a module in the spresheet and it bypassed the refresh. Great if you consider it takes 5 minutes to load the data.


My Question is this. How can I do this in Excel 2007 and Excel 2010 as the vbscript loads the data ???


Here is what I do for Excel 2003 in a vbscript.... The Module in Excel does not need to contain anything except to goto a start sheet and range.


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:ReportsStatisticsDashboard.xls")


oExcel.Run "MacroNamedBlob"

 

[SirJB7]

Hi, ianb!

As far as I can see still writing more than reading...

I'm thinking seriously about sending you a Ninja Assassin like Ras Al Ghul of Batman Begins to steal your keyboard...

Regards!

PS: Answered without reading.

 

[shrivallabha]

Hi ianb,


You can use VB Scripting in 2007 and (2010 should not be an issue I'd hope).


We had tried this on vbaexpress and it worked:

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

 

[ianb]

Thanks JB and Shrivallabha.


This is the additonal part of the code I needed. I will adapt and test :


'Write Sheet's full path here

strPath = "C:MyPathBook1.xlsm"


'Write the macro name including module

strMacro = "Sheet1.Macro2"


'Create an Excel instance and set visibility of the instance

Set objApp = CreateObject("Excel.Application")

objApp.Visible = False


'Open workbook; Run Macro; Save Workbook with changes; Close; Quit Excel

Set wbToRun = objApp.Workbooks.Open(strPath)

objApp.Run wbToRun.Name & "!" & strMacro

wbToRun.Save

wbToRun.Close

objApp.Quit


'Leaves an onscreen message!

MsgBox "Code Running Was Successful!", vbInformation

 

[ianb]

JB, I have enough Programs, Guidance, Data Files, Etc to last me to the end of the dshabord project... has been fun, Highs and lows. thanks for being around.....