Solved

produce an excel spreadsheet in vb

Posted on 2006-07-03
9
811 Views
Last Modified: 2008-01-09
Hi Experts.

Im creating a stock control system and want to be able to produce an excel spread sheet. The stock control system will be converted into a cab file for when customers want to install it. I want to be able to create an excel spreadsheet as a formatted invoice. My question really is how to produce an excel spreadsheet and print it off without the user having excel on there PC.

Many Thanks
Lee
0
Comment
Question by:ljhodgett
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 44

Expert Comment

by:bruintje
ID: 17035174
Hi ljhodgett,
----------

>>> produce an excel spreadsheet and print it off without the user having excel

a bit of a tall order without resorting to writing your own conversion or buying a third party tool
there is an alternative dll that enables you to create xl files without xl from ASP

http://www.tutorialized.com/tutorial/Dynamic-Excel-Reports-with-ASP/9360

but classic asp is easily converted to vb

----------
bruintje
share what you know, learn what you don't
0
 
LVL 7

Expert Comment

by:yotamsher
ID: 17036005
if you don't need excel but rather need nice formatted printing, consider using crystal reports

Yotam
0
 
LVL 1

Accepted Solution

by:
kwebster7327 earned 500 total points
ID: 17046002
This code fragment will take a recordset and output it to an Excel spreadsheet. It assumes you have added a reference to Excel to your project. This came from a much larger project, so I hope I've filtered out everything confusing. There are a gazillion things you can do to the data formats, etc, but this'll get you started.

        ' Assign object references to the variables. Use
        ' Add methods to create new workbook and worksheet
        ' objects.
        If xlApp Is Nothing Then
            Set xlApp = New Excel.Application
        End If
       
        If xlBook Is Nothing Then
            Set xlBook = xlApp.Workbooks.Add
        End If
       
        On Error Resume Next
        Err.Clear
        Set xlSheet = xlBook.Worksheets(0)
        If Err.Number <> 0 Then
            On Error GoTo ErrorHandler
            Set xlSheet = xlBook.Worksheets.Add
        End If
        On Error GoTo ErrorHandler
       
        'Get the field names and put them in as headers
        CellCnt = 1
        For Each FD In RS.Fields
            Select Case FD.Type
                Case adBinary, adGUID, adVarBinary
                    ' This type of data can't export to excel
                Case Else
                    xlSheet.Cells(1, CellCnt).Value = FD.Name
                   
                    ' Use XLSettingFromCommand where possible
                    xlSheet.Cells(1, CellCnt).Interior.ColorIndex = 33
                    xlSheet.Cells(1, CellCnt).Font.Bold = True
                    xlSheet.Cells(1, CellCnt).BorderAround xlContinuous
                   
                    CellCnt = CellCnt + 1
            End Select
            DoEvents
        Next
       
        ' now do each data row
        i = 2
        Do While Not RS.EOF()
            CellCnt = 1
            For Each FD In RS.Fields
                Select Case FD.Type
                    Case adBinary, adGUID, adVarBinary
                        ' This type of data can't export to excel
                    Case Else
                        xlSheet.Cells(i, CellCnt).Value = _
                            RS.Fields(FD.Name).Value
                        CellCnt = CellCnt + 1
                End Select
                DoEvents
            Next
            DoEvents
           
            RS.MoveNext
            i = i + 1
        Loop
0
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
LVL 44

Expert Comment

by:bruintje
ID: 17046116
though its nice code you would need excel installed on the client for this to work
0
 
LVL 1

Expert Comment

by:kwebster7327
ID: 17046203
Yes, you would. There are only two ways I can think of around this: buy an object that'll do it for you (be careful- some of them actually require Excel to work), or write the file in CSV format (or something else Excel reads) and have the user import it.

Directly writing an Excel formatted file is not something that's documented (at least in my experience). In any case, it'd be far more complex to do than the alternatives.

Try Google: http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLG,GGLG:2005-27,GGLG:en&q=XLS+activex
0
 
LVL 44

Expert Comment

by:bruintje
ID: 17046293
you could combine the code [from kwebster7327] with the component above [DypsXLS.XLSApplication] to create the excel sheet without excel at least if its working as advertised
0
 
LVL 16

Expert Comment

by:JohnBPrice
ID: 17046389
If you write the file in .csv you can use the free Excel viewer to print it.  You won't get pretty formatting though.  http://www.microsoft.com/downloads/details.aspx?FamilyID=c8378bf4-996c-4569-b547-75edbd03aaf0&displaylang=EN

0
 
LVL 16

Expert Comment

by:JohnBPrice
ID: 17046442
>>My question really is how to produce an excel spreadsheet and print it off without the user having excel on there PC.

I think, perhaps, your question is "How do I design a pretty invoice in Excel, and then enable my application to print it with appropriate data", which is different.  In which case what I have often done is after you are done editing you Invoice template in Excel, save it as HTML and find the text to replace.  Put the HTML in your app.
0
 
LVL 1

Expert Comment

by:kwebster7327
ID: 17046463
Which is a different kettle of fish entirely...
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
Monitor input from a computer is usually nothing special.  In this instance it prevented anyone from using the computer.  This was a preconfiguration that didn't work.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question