Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
New to VBS and MSSql
Hi all.. Need a little help please...
I have a nice formatted report that I have used the "New Database Query" wizard to populate the worksheet from a few SQL tables and it works great! I have two problems: 1.) This report is opened and then saved as another file name manually. When the report is opened by anyone without acces to the data source, no info! It tries to run the query by design. 2.) I need for the file to save as the date & time with the current SQL data instead of the query as an .xls file (my current script saves it as an .html file or tries to). Here is what I have so far: Dim oExcel Dim gfuncs Dim oBook sSourceFile = "C:\Documents and Settings\Administrator\Application Data\Microsoft\Templates\Book2.xlt" sOutputFile = "C:\CD\Support\Scripts\" & Month(date()) & "-" & Day(date()) & "-" & year(date()) & "_Current.xls" Set gfuncs = CreateObject("uisupport.globalfunctions") installpath = gfuncs.getinstallpath Set oExcel = CreateObject("Excel.Application") oExcel.Visible = False oExcel.Workbooks.Add sSourceFile oExcel.Workbooks(1).SaveAs sOutputFile, 44 oExcel.Workbooks.Close oExcel.Quit Set wBook = Nothing Set oExcel = Nothing This would work perfect if it would simply put the data from the query in there instead of the query itself. Any ideas? Thanks all!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
New to VBS and MSSql
It looks as if you're creating a new workbook based on a template
(Book2.xlt) and saving it. How can there be any data in it to save? -- Jim Rech Excel MVP "Robby" wrote in message ... | Hi all.. Need a little help please... | | I have a nice formatted report that I have used the "New Database Query" | wizard to populate the worksheet from a few SQL tables and it works great! I | have two problems: | | 1.) This report is opened and then saved as another file name manually. | When the report is opened by anyone without acces to the data source, no | info! It tries to run the query by design. | | 2.) I need for the file to save as the date & time with the current SQL data | instead of the query as an .xls file (my current script saves it as an ..html | file or tries to). | | Here is what I have so far: | | Dim oExcel | Dim gfuncs | Dim oBook | sSourceFile = "C:\Documents and Settings\Administrator\Application | Data\Microsoft\Templates\Book2.xlt" | | sOutputFile = "C:\CD\Support\Scripts\" & Month(date()) & "-" & Day(date()) | & "-" & year(date()) & "_Current.xls" | | Set gfuncs = CreateObject("uisupport.globalfunctions") | installpath = gfuncs.getinstallpath | | Set oExcel = CreateObject("Excel.Application") | oExcel.Visible = False | | oExcel.Workbooks.Add sSourceFile | | oExcel.Workbooks(1).SaveAs sOutputFile, 44 | | oExcel.Workbooks.Close | oExcel.Quit | Set wBook = Nothing | Set oExcel = Nothing | | | This would work perfect if it would simply put the data from the query in | there instead of the query itself. | | Any ideas? | | Thanks all!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
New to VBS and MSSql
Thanks for the reply Jim. That is really my question. I use the data query
to populate the tables for the report when Excel opens the file. "Jim Rech" wrote: It looks as if you're creating a new workbook based on a template (Book2.xlt) and saving it. How can there be any data in it to save? -- Jim Rech Excel MVP "Robby" wrote in message ... | Hi all.. Need a little help please... | | I have a nice formatted report that I have used the "New Database Query" | wizard to populate the worksheet from a few SQL tables and it works great! I | have two problems: | | 1.) This report is opened and then saved as another file name manually. | When the report is opened by anyone without acces to the data source, no | info! It tries to run the query by design. | | 2.) I need for the file to save as the date & time with the current SQL data | instead of the query as an .xls file (my current script saves it as an ..html | file or tries to). | | Here is what I have so far: | | Dim oExcel | Dim gfuncs | Dim oBook | sSourceFile = "C:\Documents and Settings\Administrator\Application | Data\Microsoft\Templates\Book2.xlt" | | sOutputFile = "C:\CD\Support\Scripts\" & Month(date()) & "-" & Day(date()) | & "-" & year(date()) & "_Current.xls" | | Set gfuncs = CreateObject("uisupport.globalfunctions") | installpath = gfuncs.getinstallpath | | Set oExcel = CreateObject("Excel.Application") | oExcel.Visible = False | | oExcel.Workbooks.Add sSourceFile | | oExcel.Workbooks(1).SaveAs sOutputFile, 44 | | oExcel.Workbooks.Close | oExcel.Quit | Set wBook = Nothing | Set oExcel = Nothing | | | This would work perfect if it would simply put the data from the query in | there instead of the query itself. | | Any ideas? | | Thanks all!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002, MS Query and MSSQL View with periods | Excel Discussion (Misc queries) | |||
Resource of excel to connection mssql to get data | Excel Programming | |||
SQLRequest to MSSQL server - macro | Excel Programming | |||
SQL.REQUEST query omitting rows in MSSQL DB | Excel Programming |