Need to create Excel spreadsheet from within VB

[johnk]

Can anyone point me to information on how to interact with Excel spreadsheets from VB?
John Kisner

 

[jeepxo]

You'll need to add a reference to the excel object in your application.

Then create an excel application with this line

Public excelObj as New Excel.Application

Now you will be able to open spreadsheets, create spreadsheets, modify spreadsheet.

 

[johnk]

Thanks much Jeepxo.

That gets me started. Now can you point me to where I can learn about the particulars of opening, creating & modifying spreadsheets from with vb?

John Kisner

 

[barryna]

Make sure you have the microsoft excel 9.0 object library reference set in your project. Here is some additional code that might help you interact with excel:

Dim xlapp As Excel.Application, wrkbk As Excel.Workbook, wrkst As Excel.Worksheet
Dim PathandNameofFile as string, WorkSheetNumber as Integer

PathandNameofFile = App.Path & "\file.xls"
WorkSheetNumber = 1

Set xlapp = New Excel.Application
Set wrkbk = xlapp.Workbooks.Open(PathandNameofFile)
Set wrkst = wrkbk.Sheets(WorkSheetNumber)

'Access cells by using:
'wrkst.Cells(1, 1)
'where the cell reference is row then column

 

[dvannoy]

I got some help here with this as well. check out the below code. I made some corredtions based on what I was doing. But this basically extracts a recordset from VB and puts it into an excel spread sheet. you can do this from a datagrid, form or list.

Dim objExcel As Excel.Application

On Error Resume Next
Set objExcel = GetObject(, "Excel.Application&quot
If Err.Number <> 0 Then
Set objExcel = CreateObject(&quot;Excel.Application&quot
End If
Err.Clear

objExcel.Visible = True
objExcel.WindowState = xlMaximized
objExcel.Workbooks.Add

rs.MoveFirst
s = 0
r = rs.Fields.Count
RowNum = 0
Do While (rs.EOF = False)
s = s + 1
RowID = Trim(s)
For ColIdx = 1 To r
ColSet = Int((ColIdx - 1) / 26)
If (ColSet = 0) Then
ColId = Chr(ColIdx + 64)
Else
ColId = Chr(ColSet + 64) & Chr((ColIdx - (ColSet * 26)) + 64)
End If
objExcel.Range(ColId & RowID).Value = rs.Fields(ColIdx - 1)
Next ColIdx
rs.MoveNext
Loop

The code i have is very long so I just gave you the starting point. what you can do as well is, create a macro within excel and copy the code and paste it into VB. Thats why my code is so long. you can format cells etc.

hope this helps

 

[johnk]

Many thanks to jeepxo, barryna and dvannoy. With your combined responses I believe I have a good basis to get started with.

My application involves creating a spreadsheet from highly variable input, inserting totals and hopefully other things such as background colors, and also loading graphics.

I'll post back into this forum if I learn anything that I think might help anyone else.

This is really an effecive site. Thanks again for your willingness to help a stranger.
John Kisner