Web query on Open

[Guest]

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

 

[Tim Williams]

Try adding

objWorkbook.Sheets("sheetname here").QueryTables(1).Refresh
objWorkbook.save
objworkbook.close

 

[Guest]

that worked great. I only had to add WScript.Sleep 10000 to stall while the
update occured.
I would like to save the refreshed information to a new sheet with the name
formatted to today's date. I tried adding this sub

sub save_it
dim fname
dim fpath
fpath="C:\research\"
fname=Format(now,"MMDDYYYY_hhmmss")
activeworkbook.saveas fpath & fname
end sub

but I am getting an error on the format command
and I would like to save it in the same workbook just as a new page named
with the current date.
***************the full code***********************************

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = false
Set objWorkbook = objExcel.Workbooks.Open("C:\research\web52week.xls")
objWorkbook.Sheets("sheet1").QueryTables(1).Refresh

WScript.Sleep 10000

save_it

objWorkbook.save
objworkbook.close


sub save_it
dim fname
dim fpath
fpath="C:\research\"
fname=Format(now,"MMDDYYYY_hhmmss")
activeworkbook.saveas fpath & fname
end sub

*********************end*************************
thanks,
RogueIT

 

[Norman Jones]

Hi RogueIT,

fname=Format(now,"MMDDYYYY_hhmmss")

Try:

fname = Format(Now, "mm-dd-yyyy hh:mm")


---
Regards,
Norman

that worked great. I only had to add WScript.Sleep 10000 to stall while
the
update occured.
I would like to save the refreshed information to a new sheet with the
name
formatted to today's date. I tried adding this sub

sub save_it
dim fname
dim fpath
fpath="C:\research\"
fname=Format(now,"MMDDYYYY_hhmmss")
activeworkbook.saveas fpath & fname
end sub

but I am getting an error on the format command
and I would like to save it in the same workbook just as a new page named
with the current date.
***************the full code***********************************

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = false
Set objWorkbook = objExcel.Workbooks.Open("C:\research\web52week.xls")
objWorkbook.Sheets("sheet1").QueryTables(1).Refresh

WScript.Sleep 10000

save_it

objWorkbook.save
objworkbook.close


sub save_it
dim fname
dim fpath
fpath="C:\research\"
fname=Format(now,"MMDDYYYY_hhmmss")
activeworkbook.saveas fpath & fname
end sub

*********************end*************************
thanks,
RogueIT

 

[Guest]

I am still getting a VBScript runtime error
type mismatch: 'format'
on that line at the first char, even after the change...;-(

thanks,
RogueIT

Hi RogueIT,

fname=Format(now,"MMDDYYYY_hhmmss")

Try:

fname = Format(Now, "mm-dd-yyyy hh:mm")

 

[Norman Jones]

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.

 

[Guest]

well I tried
fname=Format(now,"MMDDYYYY_hhmmss") and still get an error but I ran over to
the vbscript group and got

Dim strNOW
strNOW = Now
Dim strYMD
strYMD = Right(100+DatePart("m",strNOW),2) &"-"
strYMD = strYMD & Right(100+DatePart("d",strNOW),2) & "-"
strYMD = strYMD & DatePart("yyyy",strNOW)
fname = strYMD
which may be the long way around the horse. However as I am taking this in
steps I would like to use fname as the variable to name a new sheet in the
existing workbook.
so far here is what I have
********************************************************
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = true
Set objWorkbook = objExcel.Workbooks.Open("C:\research\web52week.xls")
objWorkbook.Sheets("sheet1").QueryTables(1).Refresh

WScript.Sleep 10000

save_it

objworkbook.sheets.saveas fpath & fname
objWorkbook.save
objworkbook.close


sub save_it
dim fname
dim fpath
fpath="C:\research\"

Dim strNOW
strNOW = Now
Dim strYMD
strYMD = Right(100+DatePart("m",strNOW),2) &"-"
strYMD = strYMD & Right(100+DatePart("d",strNOW),2) & "-"
strYMD = strYMD & DatePart("yyyy",strNOW)
fname = strYMD
end sub
*****************************************************
but objworkbook.sheets.saveas fpath & fname isn't supported...
thanks,
RogueIT

 

[Guest]

OK here is what I have
*********************************************
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True


Set objWorkbook = objExcel.Workbooks.Open("C:\research\template.xls")
Set objWorksheet = objWorkbook.Worksheets(1)
objWorkbook.Sheets("sheet1").QueryTables(1).Refresh

WScript.Sleep 7000

Dim strNOW
strNOW = Now
Dim strYMD
strYMD = Right(100+DatePart("m",strNOW),2) &"-"
strYMD = strYMD & Right(100+DatePart("d",strNOW),2) & "-"
strYMD = strYMD & DatePart("yyyy",strNOW)

objWorkbook.SaveAs("C:\research\" & strYMD & ".xls")
objExcel.Quit

***************************************************
but this makes a new workbook I would like to just be able to add a new
worksheet to the existing workbook, but I just can't seem to find any example
code that works...
thanks,
RogueIT