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

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

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