• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 149
  • Last Modified:

How to create a excel sheet from VB

Hi all,

I want to create a new excel sheet and i want to store some data and save it how
to do that from vb..code please

thanks
0
askq
Asked:
askq
1 Solution
 
Ryan ChongCommented:
Try add the M$ Excel Object Library from the Reference, then try customize example below:

This example export data from a ListView to an Excel file:

Private Sub ExportFromListViewToExcel(ByVal TargetFile As String, ByVal SheetName As String, ByVal myListView As MSComctlLib.ListView)
    Dim iExcel As New Excel.Application
    Dim iExcelWB As Excel.Workbook
    Dim iExcelWS As Excel.Worksheet
   
    iExcel.Visible = True
   
    If dir$(TargetFile) <> "" Then
        iExcel.Workbooks.Open TargetFile
    Else
        iExcel.Workbooks.add
    End If
    i = iExcel.Workbooks.Count
    Set iExcelWB = iExcel.Workbooks(i)
   
    iExcelWB.Worksheets.add
    j = iExcel.Workbooks(i).Worksheets.Count
   
    Set iExcelWS = iExcelWB.Worksheets(j)
    'Set iExcelWS = iExcelWB.ActiveSheet
    iExcelWS.Name = SheetName
    DoEvents
   
    iExcelWS.Columns(1).ColumnWidth = 20
    iExcelWS.Columns(2).ColumnWidth = 10
    iExcelWS.Columns(3).ColumnWidth = 10
    iExcelWS.Columns(4).ColumnWidth = 50
    'iExcelWS.Range("A1", "G100").EntireColumn.AutoFit
    iExcelWS.Range("A1", "D1").Font.Bold = True
    iExcelWS.Range("A1", "G100").EntireColumn.WrapText = True
   
    For j = 1 To myListView.ColumnHeaders.Count
        iExcelWS.Cells(1, j).Value = myListView.ColumnHeaders(j).Text
    Next j
   
    For j = 1 To myListView.ListItems.Count
        iExcelWS.Cells(j + 1, 1).Value = myListView.ListItems(j).Text
        For h = 1 To myListView.ColumnHeaders.Count - 1
            iExcelWS.Cells(j + 1, h + 1).Value = myListView.ListItems(j).SubItems(h)
        Next h
    Next j
   
    iExcel.Workbooks(i).Save
   
    If Check1.Value = 1 Then
        iExcelWB.Save
        iExcelWB.Close
        iExcel.Quit
        Set iExcelWS = Nothing
        Set iExcelWB = Nothing
        Set iExcel = Nothing
    End If
End Sub
0
 
askqAuthor Commented:
Thansk for u r reply...
i want to create a new excel file....
in the specified path...
how to do that...
i dont want to open the existing file....
if u use open...i think we have to give the file name
which is already existed in the path which we
specified..so let me know how to do that...

thanks
0
 
Ryan ChongCommented:
To create new excel workbook:

iExcel.Workbooks.add
i = iExcel.Workbooks.Count
Set iExcelWB = iExcel.Workbooks(i)

or

Set iExcelWB = iExcel.Workbooks.add


To save it as new file, try:

tmp = "c:\myfolder\abc.xls"
iExcelWB.SaveAs tmp
0
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!

 
askqAuthor Commented:
Thanks for u r reply
it is working ...

bye
0
 
CleanupPingCommented:
askq:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
Experts: Post your closing recommendations!  Who deserves points here?
0
 
DanRollinsCommented:
Moderator, my recommended disposition is:

    Accept ryancys's comment(s) as an answer.

DanRollins -- EE database cleanup volunteer
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now