Web query on Open

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
Try adding

objWorkbook.Sheets("sheetname here").QueryTables(1).Refresh
objWorkbook.save
objworkbook.close
 
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
 
Hi RogueIT,
fname=Format(now,"MMDDYYYY_hhmmss")
Click to expand...

Try:

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


---
Regards,
Norman



RogueIT said:
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
Click to expand...
 
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 said:
Hi RogueIT,
fname=Format(now,"MMDDYYYY_hhmmss")
Click to expand...

Try:

fname = Format(Now, "mm-dd-yyyy hh:mm")
Click to expand...
 
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.
 
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
 
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
 
Back
Top