Writing Excel files from VB 4

[Jonathan]

I would like to be able to create MS Excel files directly from VB withough resorting to automation. Does anyone know how this is done?

Thanks

Jonathan
j.w.george@virginnet.co.uk

Working against: Visual Basic 6, Access 97, Visual Interdev 6, VBScript, Active Server Pages, SQL Server 6.5, Oracle 7

 

[swilliams]

I normally just write csv files - which open OK in Excel - using the normal methods of writing to file.

Simon

 

[ICET]

Consider this piece of code(vb6)

Dim bWasntRunning As Boolean 'Excel already running
Dim Excel As Object 'Excel-object

Private Sub Yourbutton_Click()

Dim ExcelSheet As Object

' ---------------------------------------------------
'Start Excel if not running.
' ---------------------------------------------------
bWasntRunning = False
'Try first to use an existing instance.
Set Excel = GetObject(, "Excel.Application&quot
If Err Then
Err.Clear
'Excel isn't running, so start it.
bWasntRunning = True
Set Excel = CreateObject("Excel.Application&quot
If Err <> 0 Then
MsgBox CL(&quot;Could Not Load Excel!&quot, vbExclamation
End
End If
End If
On Error GoTo 0

' ---------------------------------------------------
'Section 3. Get Excel ready to receive the data.
' ---------------------------------------------------
Excel.Visible = True
If FileExists(App.Path + &quot;\excelfile.xls&quot Then

Excel.Workbooks.Open App.Path + &quot;\excelfile.xls&quot;
'Read the name of the sheet
LastMethod = Excel.ActiveWorkbook.Sheets(1).Name
Set ExcelSheet = Excel.ActiveWorkbook.Sheets(LastMethod)
Else
Excel.Workbooks.Add (xlWBATWorksheet)
Set ExcelSheet = Excel.ActiveWorkbook.Sheets(&quot;Sheet1&quot
'fill the sheet
'first with a heading
ExcelSheet.Range(&quot;A11&quot.Font.Bold = True
'then the cells
ExcelSheet.Cells(1, 1).Value = &quot;Datum/tijd&quot;
ExcelSheet.Range(&quot;A1&quot.ColumnWidth = Len(&quot;Datum/tijd&quot
ExcelSheet.Cells(1, 2).Value = &quot;Pad&quot;
ExcelSheet.Range(&quot;B1&quot.ColumnWidth = Len(App.Path)
ExcelSheet.Cells(1, 3).Value = &quot;Methode&quot;
ExcelSheet.Cells(1, 4).Value = &quot;Tijdsduur&quot;
ExcelSheet.Cells(1, 5).Value = &quot;Patientnumber&quot;
ExcelSheet.Range(&quot;E1&quot.ColumnWidth = Len(&quot;Patientnumber&quot
ExcelSheet.Cells(1, 6).Value = &quot;AB1&quot;
ExcelSheet.Cells(1, 7).Value = &quot;AB2&quot;
ExcelSheet.Cells(1, 8).Value = &quot;AB3&quot;
ExcelSheet.Cells(1, 9).Value = &quot;AB4&quot;

ExcelSheet.SaveAs (MakePathStr(App.Path, &quot;excelfile.xls&quot;, &quot;xls&quot) 'own routine
End If
Set Excelsheet = Nothing
End Sub


'Cancel
Private Sub Exit_btn_Click()

'Only shut down Excel if it wasn't running before.
If bWasntRunning Then
Excel.Quit
End If

'The Quit method closes Excel, but it's still in memory,
'because Excel still points to it. This statement
'will remove it from memory unless it was running when
'this procedure started.
Set Excel = Nothing

Unload Me
End Sub

 

[Jonathan]

That code will work, but it's not what I'm trying to do. I want to be able to write Excel files directly, not create them in Excel and save them. I have found that under Windows 95 (the environment which my customer uses, so I can't do anything about it) creating several largish excel workbooks in this way causes the app to slow down and eventually grind to a complete halt.

We eventually put this down to poor memory management in Win 95 - the same code runs fine under Windows NT - and the only way I could find to stop it happenning was to quit Excel after each sheet is created and then start it again.

Obviously this is far from ideal. The system generates several hundred excel spreadsheets, and we are currently looking at times in the region of hours to produce them all. My feeling was that it would be a lot faster to write the files directly to disk, hence the question.

Any more ideas?

Thanks,
Jonathan

Jonathan
j.w.george@virginnet.co.uk

Working against: Visual Basic 6, Access 97, Visual Interdev 6, VBScript, Active Server Pages, SQL Server 6.5, Oracle 7

 

[Dilyias]

Well, if you want to write XLS files, you could check out

http://www.wotsit.org

, I believe they have info on the XLS file format, although it would mean writing all the binary header info, etc.. yourself.

 

[larissalle]

Jonathan,
Re: &quot;...creating several largish excel workbooks in this way causes the app to slow down and eventually grind to a complete halt.&quot;

Not sure if this applies to Excel running on Win 95, but I found that Excel2000 running on Win NT 2000 Professional, comes to a grinding halt because any VBA buttons, checkboxes, etc., create a *.emf file in the user's temp folder. The .emf files accumulate and eventually eat up the memory. Below is the web page that describes the problem and fix. I had over 2900 .emf files accumulated at the time I finally found this fix. Currently have a script that runs each time I open any excel file that contains any VBA buttons/checkboxes,etc.

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q299372

Hope this helps.

 

[TheKing]

I think I have the same problem, this is my question, and I also don't really want excel to open I just want an output of a pie chart in a VB form to come up.

I have a check system program that I wrote and would like to make it spit out a report when it is done.
I have narrowed it down to 2 ways, datagrid output to Word (got that one to work ok) and a pie chart showing the good and bad amounts from each coustomer.
I don't really know how to go about doing the excel pie chart. I can pass a 'good record' count, a 'bad record' count, and a 'total record' count.
That should be enought to bring up a 2 color pie chart to show that this coustomer had 45% bad records out of the whole record count.


Thanks
TheKing

 

[dsi]

One note on ICET's sample:

You should not use the variable name Excel. If you include the Excel Object library, this name is acutually reserved by the Excel Application.

Dim Excel As Object

would be better represented as

Dim xlApp As Object
Set xlApp = New Excel.Application

 

[NipsMG]

Jonathan:

Try these:

http://www.planetsourcecode.com/vb/scripts/ShowCode.asp?txtCodeId=22638&lngWId=1

**** ThIS one is probably the best ****

http://www.planetsourcecode.com/vb/scripts/ShowCode.asp?txtCodeId=22638&lngWId=1

--NipsMG

 

[stnkyminky]

Jonathan,

If you are wanting just text in your xls file I would recommend swilliams solution.


<example>
Open &quot;C:\foo.txt&quot; for output shared as #1
write #1, something & vbtab & somethingelse
close #1

'Automation is required for this unless you want the users to open the text file and do the conversion. However, this is extremely straight forward and easy.

Dim oExcel as Excel.Application
dim oWork as Excel.Workbook
set oExcel = new Excel.Application

'If you manually open the text file, Excel will start a wizard to convert the data. If you just open the file, no wizard appears and the text is instantly in the spreadsheet.

set oWork = oExcel.Workbooks.Open (&quot;C:\foo.txt&quot
oWork.SaveAs &quot;C:\bar.xls&quot;

oExcel.Quit

set oWork = Nothing
set oExcel = Nothing
</example> Scott
Programmer Analyst
<><

 

[leangross]

I found this code in microsoft knowledge base but it has an error when i ran the codes.

I this the proper way to transfer file from SQL table to excel?



Dim strSQL As String
strSQL = &quot;SELECT * INTO [Excel 8.0;Database=&quot; & App.Path & _
&quot;\book1.xls].[Sheet1] FROM Customers&quot;
cnSrc.Execute strSQL

 

[mattKnight]

Jonathan,

I am starting froma position of ignorance (its a longtime since I last used win95!) but ODBC has an XL driver. It seems you should therefore be able to &quot;pipe&quot; your output through the ODBC driver straight to disk.

As I said earlier, Iam not sure that 95 supports odbc.

Good luck

Matt

 

[Jack58]

I have an Excel spreadsheet with a form that I would like to be able to add a Combo box to and have the combo box populate two fields of data on my form from two columns on my spreadsheet.

Anyone have any ideas?


Thanks in advance



Jack

 

[Timme1]

Hello,

I have put the code proposed by icet (first in thread) in a module and i call it when a button is pressed in a form.
I get the message that the method FileExists(...) isn't known. Does anyone know the reason for this?

thanks in advance

Tim