Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web query on Open
I have a spreadsheet that I use to update information from the web ( although
I guess by reading the subject you already knew that ). I found this vbscript code to open the file and I can set it to run on a schedule but when the file opens the web query doens't refresh, even though I have refresh on open checked. Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True Set objWorkbook = objExcel.Workbooks.Open("C:\research\web52.xls") I tried checking the "refresh every" and set it for 1 minute but I am still asked if I want to enable automatic refresh. 1. how can I have it refresh on open automatically? 2.once the refresh has occured is it possible to, programatically save the file and close it? thanks in advance, RogueIT |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web query on Open
Try adding
objWorkbook.Sheets("sheetname here").QueryTables(1).Refresh objWorkbook.save objworkbook.close -- Tim Williams Palo Alto, CA "RogueIT" wrote in message ... I have a spreadsheet that I use to update information from the web ( although I guess by reading the subject you already knew that ). I found this vbscript code to open the file and I can set it to run on a schedule but when the file opens the web query doens't refresh, even though I have refresh on open checked. Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True Set objWorkbook = objExcel.Workbooks.Open("C:\research\web52.xls") I tried checking the "refresh every" and set it for 1 minute but I am still asked if I want to enable automatic refresh. 1. how can I have it refresh on open automatically? 2.once the refresh has occured is it possible to, programatically save the file and close it? thanks in advance, RogueIT |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web query on Open
Hi RogueIT,
My suggestion fails because I included the : (colon) character, which is not allowed in file names. However, your code ran without problem for me. --- Regards, Norman "RogueIT" wrote in message ... I am still getting a VBScript runtime error type mismatch: 'format' on that line at the first char, even after the change...;-( thanks, RogueIT "Norman Jones" wrote: Hi RogueIT, fname=Format(now,"MMDDYYYY_hhmmss") Try: fname = Format(Now, "mm-dd-yyyy hh:mm") --- Regards, Norman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Open a Query | New Users to Excel | |||
why does edit query button not open query | Excel Discussion (Misc queries) | |||
Web query - Unable to open | Excel Discussion (Misc queries) | |||
open a query automatically | Excel Worksheet Functions | |||
Edit Query from Excel will not open query in MSQuery | Excel Programming |