Saving to Excel from VB without it prompting 1

[ToeJamNEarl]

Hey Guys,

I am writing an application that will write to an Excel spreadsheet from Visual Basic and I want it to save and close without Excel prompting. How would I go about doing that? Many thanks,

-Diran

 

[CasperTFG]

Have you done any DB programming before?

The best way may be to use ADO.

Casper

There is room for all of gods creatures, "Right Beside the Mashed Potatoes".

 

[ToeJamNEarl]

Oh yeah I've used ADO before, but I want to make an output file into an excel spreadsheet. I'm going to dump data from an ADO recordset into an Excel spreadsheet and save that file. This is primarily for user output. So any help on my topic question would be greatly appreciated.

Thanks,

-Diran

 

[CasperTFG]

Well just use the JET OLEDB driver to write to that file... Using it you can create the file, create the Sheet, write to it, read from it all without opening Excel...

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
"HDR=Yes;" indicates that the first row contains columnnames, not data
"IMEX=1;" tells the driver to always read "intermixed" data columns as text
TIP! SQL syntax: "SELECT * FROM [sheet1$]" - i.e. worksheet name followed by a "$" and wrapped in "[" "]" brackets.


Casper

There is room for all of gods creatures, "Right Beside the Mashed Potatoes".

 

[hmckillop]

Dim xlapp1 As New Excel.Application
Dim xlwb1 As Excel.Workbook
Dim xlws1 As Excel.Worksheet


Set xlwb1 = xlapp1.Workbooks.Open("d:\book1.xls", , False)
Set xlws1 = xlwb1.Worksheets.Add

xlws1.Cells(1, 1) = "Hello WOrld"

Dim i As Integer
i = 2
While i < 6
xlws1.Cells(i, 1) = i
i = i + 1
Wend
xlws1.Cells(i, 1).Formula = "=Sum(a2:a5)"

xlwb1.Save
xlwb1.Close


"Own only what you can carry with you; know language, know countries, know people. Let your memory be your travel bag.

 

[ToeJamNEarl]

Casper: I'm using OraOLEDB provider and not JET.

hmckillop:
More than half the time the XLS file will not exist. When that happens your code doesn't allow the excel file to open, also when the file does exist and it's blank it will still prompt for SaveAs.

I create the Excel file by wrapping Open strOutputFileName For Output as #1

and then close #1 around the Excel operations.

Any additional help would be greatly appreciated.

-Diran

 

[CasperTFG]

Hey Diran,

You can have 2 connections open at the same time... One to JET and One to ORAOleDB.


Casper

There is room for all of gods creatures, "Right Beside the Mashed Potatoes".

 

[ToeJamNEarl]

Casper:

I have to have report style header and footer in my excel file and the data will be formatted in a specific way. If I use the JEToption will I be able to insert my header/footer data easily?

Thanks

 

[CasperTFG]

Ohhh... You never mentioned the headers and footers, ADO would have been the best and fastest to do a dump of data which is what I thought you were doing.

For what you are now describing you have to use the Excel objects...

It wasn't said before so I will mentioned it. You should Reference the "Microsoft Excel 8.0 Objects Library"

The file cannot be created this way... Open strOutputFileName For Output as #1.

Should be doen this way...
Dim wb As Excel.Workbook = ThisApplication.Workbooks.Add()

There is much more on this here...

http://msdn.microsoft.com/library/d..._wrcore/html/wrtskhowtocreatenewworkbooks.asp

It will also help you get your headers and footers etc...


Casper

There is room for all of gods creatures, "Right Beside the Mashed Potatoes".

 

[ToeJamNEarl]

Casper, Thanks a bunch. I'll give you a star when I get home (company settings forbid javascript). I figured it out before checking your reply and I was at that same site.

hmckillop: you'll get a star as well because you took the time to code out an example which was helpful.


Thanks guys,

-Diran

 

[jjames]

Adding to hmckillop's solution, which I believe is the best..

I would create an Excel template file that has all your headers and footers and formatting applied. All you need to do is add the data through VB.

Open the template file (xlt)

Do the work

Save as a new excel file (xls)

To suppress alerts

xlapp1.AlertBeforeOverwriting = False

xlapp1.DisplayAlerts = False

 

[ToeJamNEarl]

JJames you'll also be getting a star when I get home from work, due to the alertbeforeoverwriting = false and displayalerts = false.

Those two lines helped me get rid of my insanity when Excel tried to overwrite a file.

I originally was going to go with the template file and I was until a tester overwrote my templated file. So that is the reason I am not going to go with a template file. I know the user would overwrite it; given the "right" user.

I have been fiddling with dumping data and merging/formatting cells already, the file saving was the biggest part.

One last question is this. Is it possible for Excel to not pop up to write and save the data? I tried objExcel.visible = false, and I can still see Excel pop open to write and save the data.

Much appreciated,

-Diran