Accessing Excel sheets

[amichalik]

I am a very new Vb beginer

I need code which will populate a Combo box with the names of all the worksheets in an Excel 97 workbook?

Can someone help me?

Thank you

Antony

Ok from the Project menu option, select references, click on MS Excel in the list box. This allows your project to have access to the Excel object library.

Ok in General Options Type

Private oExcel As Object
Private oExcelSheet As Object

In the form Load Event

On Error Resume Next ' can expect an error
DoEvents

Set oExcel = GetObject(,"Excel.Application")
'
' If Excel is not running, start it
'
If Err = 429 Then
Err.Clear
Set oExcel = CreateObject("Excel.Application")
End If
'
' Don't have vb on this machine, but you would add code to
' get the spreadsheet you wanted here
'
cboList.Clear
oExcel.DisplayAlerts = False
For iCnt = 1 To oExcel.Sheets.Count
cboList.AddItem oExcel.Sheets(iCnt).Caption
Next iCnt
cboList.ListIndex = 0

[HDR]

This should get you started. First, put a reference to the Microsoft Excel 8.0 object library in your project/references. Then put the following code in a procedure:


Dim xlApp As Excel.Application
Set xlApp = CreateObject("Excel.Application")

Dim xlWS As Excel.Worksheet
Dim sNames() As String
Dim nCnt As Integer

xlApp.Workbooks.Open "Path and FileName.xls"
ReDim sNames(xlApp.Worksheets.Count)
For nCnt = 1 To xlApp.Worksheets.Count
Set xlWS = xlApp.Worksheets.Item(nCnt)
sNames(nCnt) = xlWS.Name
Next

xlApp.Workbooks.Close
xlApp.Quit
Set xlWS = Nothing
Set xlApp = Nothing


Instead of putting the name in the array sNames, you will populate your combo box. Good luck!