Start Excel in VB

[Grey]

Dear All

I want to know if it is posssible to start MS Excel from VB application (not
VBA in Excel). I have Vb application with MS Access as DB, but now i need to
have report module for this app. this module should have report preview and
export to excel functionality. I don't want to use Crystal report...what
else I can use?? is it possible to bring VB data to excel directly with vb
programming or what else i can use??

Million Thanks

Eric Yum

 

[losmac]

MS Access have metod OutputTo.... Excel
Use it.

 

[John]

Eric,
This is strictly a knowledgable guess based on my experience with MS VBA
but I'd have to say the answer is "yes". Unfortunately this newsgroup is
devoted to MS Project VBA issues so I haven't got a clue on how to help
you other than to suggest you try a newsgroup devoted to VB6.

Good luck.
John

 

[Helmut Weber]

Hi Yum,
to start Excel via VB is just the same as to
start it from an Office application. You may use
"early binding" by setting a reference to the excel-
library or use late binding by getobject or
createobject. Like that (not tested)
early binding
Dim oX as excel.application
set oX = new excel.application
late binding
dim oX as object
set oX = createobject("Excel.application")
or
set oX = getobject(, "Excel.Application")
Greetings from Bavaria, Germany
Helmut Weber
"red.sys" & chr$(64) & "t-online.de"

 

[losmac]

Here, You have an example how to export data to Excel
application from VBA.

I hope it helps...

Sub SendReportToXLS(Optional autosave As Boolean = False)
Dim ExApp As Excel.Application
Dim ExWbk As Excel.Workbook
Dim ExWsh As Excel.Worksheet
Dim rst As Recordset ' your data in report
Dim i As Long, j As Long

On Error GoTo Err_SendReportToXLS

Set ExApp = CreateObject("Excel.Application") 'create new
instance of Excel
Set ExWbk = ExApp.Workbooks.Add 'add new workbook
Set ExWsh = ExWbk.Worksheets(1) 'get first worksheet
Set rst = CurrentDb.OpenRecordset("U1402603") 'open query
or table on which is based your report
With rst
Do While Not rst.EOF 'rows begins from 0
For j = 0 To .Fields.Count - 1 'columns begins
form 0
'Excel columns and rows begins from 1
ExWsh.Cells(i + 1, j + 1) = Nz(.Fields
(j).Value, "") 'nz - if field value=null
Next j
i = i + 1
.MoveNext
Loop
.Close
End With

'to save report
If autosave Then ExWbk.SaveAs "C:\" & Now() & ".xls"

'show exported data
ExApp.Visible = True

Exit_SendReportToXLS:
On Error Resume Next
Set rst = Nothing
Set ExWsh = Nothing
'ExWbk.Close SaveChanges:=False
Set ExWbk = Nothing
'ExApp.Quit
Set ExApp = Nothing
Exit Sub

Err_SendReportToXLS:
MsgBox Err.Description, vbExclamation, "Error
number: " & Err.Number
Err.Clear
GoTo Exit_SendReportToXLS

End Sub

 

[Tushar Mehta]

Instead of looping through the recordset, consider using the XL Range
object's CopyFromRecordSet method. For more: XL VBA help.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[losmac]

Hi Tushar Mehta,

Thanks for tip. I know about it.
I always try to show more...