produce an excel spreadsheet in vb

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
ljhodgettAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
kwebster7327Connect With a Mentor Commented:
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
 
bruintjeCommented:
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
 
yotamsherCommented:
if you don't need excel but rather need nice formatted printing, consider using crystal reports

Yotam
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
bruintjeCommented:
though its nice code you would need excel installed on the client for this to work
0
 
kwebster7327Commented:
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
 
bruintjeCommented:
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
 
JohnBPriceCommented:
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
 
JohnBPriceCommented:
>>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
 
kwebster7327Commented:
Which is a different kettle of fish entirely...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.