[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 139
  • Last Modified:

How to load the names of all data sheets from an excel file?

Dear netters:

I am developing a VB program which opens an existing excel file and fill up the data into a user specified data sheet.  However, the excel file has multiple data sheets.  Is there any way to retrieve all the data sheet names from the excel file so the user can choose which data sheet to put the data in?

regards,
0
menshen_tsai
Asked:
menshen_tsai
1 Solution
 
Ryan ChongCommented:
Try this example:

Private Sub Command1_Click()
    On Error GoTo EH
    Dim iExcel As Excel.Application
    Dim iWBook As Workbook
    Dim iWSheet As Worksheet
   
    tempdbpath = "c:\abc.xls"
   
    If tempdbpath = "" Then Exit Sub
    Set iExcel = CreateObject("excel.application")
    iExcel.Visible = True
    Set iWBook = iExcel.Application.Workbooks.Open(tempdbpath)
   
   
    For i = 1 To iWBook.Worksheets.Count
        Set iWSheet = iWBook.Worksheets(i)
        Debug.Print iWSheet.Name
    Next i
   
    'iWSheet.PrintOut
'    iWSheet.PrintPreview False
    'Set iWSheet = Nothing
    'iWBook.Close True
    'Set iWBook = Nothing
    'iExcel.Quit
    'Set iExcel = Nothing
    Exit Sub
EH:
    'ShowErrMsg
End Sub

cheers
0
 
jgvCommented:
This will return all worksheets in the opened workbook:

Private Sub Command1_Click()
    Dim xApp As New Excel.Application
    Dim xSht As Excel.Worksheet
   
    xApp.Workbooks.Open "c:\My Documents\My Workbook.xls"
   
    For Each xSht In xApp.Worksheets
        Debug.Print xSht.Name
    Next
   
    Set xSht = Nothing
   
    xApp.Quit
    Set xApp = Nothing
End Sub
0
 
CleanupPingCommented:
menshen_tsai:
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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