Solved

produce an excel spreadsheet in vb

Posted on 2006-07-03
9
804 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 44

Expert Comment

by:bruintje
Comment Utility
though its nice code you would need excel installed on the client for this to work
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Expert Comment

by:kwebster7327
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
>>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
Comment Utility
Which is a different kettle of fish entirely...
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

I know it’s not a new topic to discuss and it has lots of online contents already available over the net. But Then I thought it would be useful to this site’s visitors and can have online repository on vim most commonly used commands. This post h…
This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

772 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now