New to VBS and MSSql

[Robby]

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!!

 

[Jim Rech]

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
| 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!!

 

[Robby]

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.