Solved

produce an excel spreadsheet in vb

Posted on 2006-07-03
9
817 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

696 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