Creating Excel sheets from VB5 
Author Message
 Creating Excel sheets from VB5

Hi All,

I have a couple of questions that need answering, that I cant figure out.

1.  I know how to read Excel worksheets into Visual Basic, but is there a
way I can *create* an Excel spreadsheet from VB5? The application I am
writing calls for log information to be collated and then the final data
presented in a spreadsheet, and obviously I would like to take care of this
completely within VB.

2. Second, once the report has been outputted to a file, I would like to be
able to click on "View" within my app and Excel will load up and display the
file.  However, I can't find any reference to system calls to load other
applications up.  What would be the best way of doing this?

Thanks in advance for all the help.

Regards,
Stuart Stanfield

--
Kiosk Engineer, Town Pages Ltd.



Sun, 04 Feb 2001 03:00:00 GMT  
 Creating Excel sheets from VB5
Hello

I'm no Excel guru but have used csv files (comma delimited files) with
Excel. Excel will create a spreadsheet directly from a csv file. If
your log file looks something like:

Date/time, event, message
Date/time, event, message
Date/time, event, message
....
then you can simple launch the .csv file directly from your vb app. To
do this you will need to use the ShellExecute Api instead of Shell.

Using OLE it is possible to directly access cells of an Excel
worksheet but this is beyond my scope.

Best wishes, Murray



Mon, 05 Feb 2001 03:00:00 GMT  
 Creating Excel sheets from VB5
Here is some code that a colleague of mine wrote (I hope he doesn't mind) :)
It exports a RDOResultSet to Excel

You need to reference 'Microsoft Excel 8.0 Object Library'

Public Function ExportToExcel(rs As rdoResultset, Optional FileName As String = "resultset.xls") As Boolean
Dim ExcelSheet As Object
Dim X As Excel.Worksheet
Dim cl As rdoColumn
Dim col As Integer, row As Integer: col = 1: row = 1
Dim I As Integer

    On Error Goto Export_Err
    Screen.MousePointer = vbHourglass
    Set ExcelSheet = CreateObject("Excel.Sheet")
    Set X = ExcelSheet.Application.ActiveSheet
    For Each cl In rs.rdoColumns
        X.Cells(row, col) = cl.Name
        X.Cells(row, col).Font.Bold = True
        X.Columns(col).HorizontalAlignment = xlHAlignLeft
        col = col + 1
    Next cl
    rs.MoveFirst
    Do While Not rs.EOF
        row = row + 1
        col = 1
        For Each cl In rs.rdoColumns
            X.Cells(row, col).Value = EmptyIfNull(cl.Value)
            col = col + 1
        Next cl
        rs.MoveNext
    Loop
    For I = 1 To col
        X.Columns(I).AutoFit
    Next I
    If FileExists(FileName) Then Kill FileName
    X.SaveAs FileName
    X.Application.Quit
    Set ExcelSheet = Nothing
    Set X = Nothing
    Screen.MousePointer = vbDefault
    ExportToExcel = True
    Exit Function

Export_Err:

    ' code to display error or handle it
    Screen.MousePointer = vbDefault
    ExportToExcel = False
    Exit Function

End Function

--
Lea Johnson
IT Services Centre, ADFA

To reply via E-Mail, please remove 'nospam.' from the return E-Mail address

Quote:

>Hello

>I'm no Excel guru but have used csv files (comma delimited files) with
>Excel. Excel will create a spreadsheet directly from a csv file. If
>your log file looks something like:

>Date/time, event, message
>Date/time, event, message
>Date/time, event, message
>....
>then you can simple launch the .csv file directly from your vb app. To
>do this you will need to use the ShellExecute Api instead of Shell.

>Using OLE it is possible to directly access cells of an Excel
>worksheet but this is beyond my scope.

>Best wishes, Murray



Tue, 06 Feb 2001 03:00:00 GMT  
 Creating Excel sheets from VB5
Lea,

Good solution - but it will be SLOW. You are making hundreds / thousands of
out-of-proc calls.

Another option is to create a new Excel spreadsheet which you install with
your application. In that spreadsheet, write a public VBA function which
accepts data (probably a byval variant array). Then have the calling VB
program load the data into an array, open Excel, load the particular
spreadsheet and use the method which runs a macro (I think it is
Application.Run), passing in the array as the parameter.

That way the VB program worries about setting up the data - but the code
that inserts the data into the cells and does the formatting is running in
VBA in Excel (ie. in-proc) and so is HEAPS faster (up to 100 times). Also,
if you can embed the actual formatting (eg. Headers etc) in the spreadsheet
(so they don't need to be setup at runtime), that will make it faster as
well.

Another option would be to write the whole thing in the Excel spreadsheet.
Make sure, though, that you don't use RDO (it doesn't work in Office unless
the user has VB development environment installed) - ie use DAO.

Matthew



Here is some code that a colleague of mine wrote (I hope he doesn't mind)
:)
It exports a RDOResultSet to Excel

You need to reference 'Microsoft Excel 8.0 Object Library'



Tue, 06 Feb 2001 03:00:00 GMT  
 Creating Excel sheets from VB5

Quote:

>Hi All,

>I have a couple of questions that need answering, that I cant figure out.

>1.  I know how to read Excel worksheets into Visual Basic, but is there a
>way I can *create* an Excel spreadsheet from VB5? The application I am
>writing calls for log information to be collated and then the final data
>presented in a spreadsheet, and obviously I would like to take care of this
>completely within VB.

I don't know how to write TRUE Excel files with VB (that's one of my questions
as well).  However, you can write comma-delimited files, slap a .csv extension
on it and if Excel is associated to .csv files (it is by default), then Excel
will open with your file when you use ShellExecute (see below).

Quote:

>2. Second, once the report has been outputted to a file, I would like to be
>able to click on "View" within my app and Excel will load up and display the
>file.  However, I can't find any reference to system calls to load other
>applications up.  What would be the best way of doing this?

Use ShellExecute.  This opens up documents based on the Windows' file
association.

Quote:

>Thanks in advance for all the help.

>Regards,
>Stuart Stanfield

>--
>Kiosk Engineer, Town Pages Ltd.

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Kevin McCracken

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-



Sun, 18 Feb 2001 03:00:00 GMT  
 Creating Excel sheets from VB5
Open an instance of Excel, pump the data in, call save as function from
instance of excel. Works like a charm.

Quote:



>>Hi All,

>>I have a couple of questions that need answering, that I cant figure out.

>>1.  I know how to read Excel worksheets into Visual Basic, but is there a
>>way I can *create* an Excel spreadsheet from VB5? The application I am
>>writing calls for log information to be collated and then the final data
>>presented in a spreadsheet, and obviously I would like to take care of
this
>>completely within VB.

>I don't know how to write TRUE Excel files with VB (that's one of my
questions
>as well).  However, you can write comma-delimited files, slap a .csv
extension
>on it and if Excel is associated to .csv files (it is by default), then
Excel
>will open with your file when you use ShellExecute (see below).

>>2. Second, once the report has been outputted to a file, I would like to
be
>>able to click on "View" within my app and Excel will load up and display
the
>>file.  However, I can't find any reference to system calls to load other
>>applications up.  What would be the best way of doing this?

>Use ShellExecute.  This opens up documents based on the Windows' file
>association.

>>Thanks in advance for all the help.

>>Regards,
>>Stuart Stanfield

>>--
>>Kiosk Engineer, Town Pages Ltd.

>-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

>Kevin McCracken

>-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-



Thu, 22 Feb 2001 03:00:00 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. Creating an Excel sheet from VB5 Enterprise

2. Need help displaying Excel sheet with VB5 - Please.

3. Need help displaying Excel sheet with VB5 - Please.

4. Create Excel-sheet with VB4 ??

5. Creating charts as objects in excel sheets using vb6 / vb dotnet

6. Linking Excel sheets in VB5

7. Need help displaying Excel sheet with VB5 - Please.

8. Embedding Excel Sheet in a VB5 application

9. Somebody please help me to create an Excel Workbook w/sheets from VB4.0

10. Create Excel-sheet with VB4 ??

11. Creating and Saving Excel Sheets

12. HOW TO CREATE A DYNAMIC ROWS IN THE TABLE AS IN EXCEL SHEET

 

 
Powered by phpBB® Forum Software