Link to home
Start Free TrialLog in
Avatar of gsmotherman
gsmotherman

asked on

Excel & VB - Open method failed!

I am filling a combobox with the names of the sheets in an excel workbook
like so...

  Dim xlApp As Excel.Application
  Dim i As Integer
  Set xlApp = GetObject(, "Excel.Application")
  'Set xlApp = CreateObject("Excel.Application")
  'Set xlApp = CreateObject("Excel.Application.5")

  'xlApp.Workbooks.Open txtSpreadSheet
  xlApp.Workbooks.Open txtSpreadSheet, 0, True   'Open read only
  cboSheets.Clear
  For i = 1 To xlApp.Sheets.Count - 1
    If xlApp.Sheets(i).Visible Then
        cboSheets.AddItem xlApp.Sheets(i).Name
        cboSheets.ItemData(cboSheets.NewIndex) = i
    End If
  Next i

This works fine on my PC and on some others,  however it wont work on the PC that matters.

Error message:
Error 1004: Open method of Workbooks Class Failed.

Are there files that I need to distribute to use the Excel object (I have included xl5en32.olb and all files from the application setup wizard dependency file)

Works fine on my PC VB5 & Office 95
Works fine on PC with Office 97
Error on PC with Office 95

Anyone have any Ideas?

Gary
Avatar of chdy
chdy

Dear gsmotherman...! :)
Below Code is Connection Excel and VB...
I want to help you by below program...

Option Explicit
Dim ExcelSheet As Object
Dim WorkSheets
Dim SampleCount As Long
Dim SampleTime As Integer
Dim Counter As Long
Dim ADData(999) As Double
Dim WorkSheet As Integer

Private Sub cmdEnd_Click()
    Dim FileName As String
    FileName = CurDir + "\" + txtFileName.Text + ".xls"
    ExcelSheet.SaveAs FileName
    ExcelSheet.Application.Quit
    Set ExcelSheet = Nothing
    End
End Sub

Private Sub cmdSet_Click()
    With ExcelSheet
        If WorkSheet > 1 Then
            .WorkSheets.Add
        End If
        .Application.Visible = True
        .Parent.Windows(1).Visible = True
        Dim N As Integer
        Dim XName1 As String, XName2 As String
        If SampleCount = 0 Then Exit Sub
        .WorkSheets(1).range("A1").Value = "Ƚ¼ö"
        .WorkSheets(1).range("B1").Value = "ÀúÀå"
        For N = 0 To SampleCount - 1
            XName1 = "A" + Format(N + 2)
            XName2 = "B" + Format(N + 2)
            .WorkSheets(1).range(XName1).Value = N + 1
            .WorkSheets(1).range(XName2).Value = ADData(N)
        Next N
        WorkSheet = WorkSheet + 1
    End With
End Sub

Private Sub cmdStart_Click()
    If cmdStart.Enabled = False Then Exit Sub
    SampleTime = CInt(txtSample.Text)
    If SampleTime <= 0 Then Exit Sub
    If SampleTime > 1000 Then
        SampleTime = 1000
        txtSample.Text = Format(SampleTime)
    End If
    SampleCount = CLng(txtCount.Text)
    If SampleCount <= 0 Then Exit Sub
    If SampleCount > 1000 Then
        SampleCount = 1000
        txtCount.Text = Format(SampleCount)
    End If
    Counter = 0
    cmdStart.Enabled = False
    cmdStop.Enabled = True
    tmrAD.Interval = SampleTime * 10
End Sub

Private Sub cmdStop_Click()
    tmrAD.Interval = 0
    cmdStart.Enabled = True
    cmdStop.Enabled = False
End Sub

Private Sub Form_Load()
    Set ExcelSheet = CreateObject("Excel.sheet")
    WorkSheet = 1
End Sub

Private Sub Form_Unload(Cancel As Integer)
    Set ExcelSheet = Nothing
End Sub

Private Sub tmrAD_Timer()
   
    ADData(Counter) = Int(99 + Rnd) + 1
    lblValue.Caption = Format(ADData(Counter))
    Counter = Counter + 1
    lblCount.Caption = Format(Counter)
    If Counter >= SampleCount Then
        tmrAD.Interval = 0
        cmdStart.Enabled = True
        cmdStop.Enabled = False
    End If
End Sub

Sorry! sliencing word...because im korean...
I want to content this program..have a nice day...
Avatar of gsmotherman

ASKER

Im not sure what your trying to show me?
My code is working fine on some machines, and not working on others, so I believe that its a missing file, or a version problem.  I need to open an existing Excel (95 and/or 97) workbook and fill a combo with a list of its sheet names.

Thanks for trying,
Gary
ASKER CERTIFIED SOLUTION
Avatar of ohayden
ohayden

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial