[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

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
  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?

1 Solution
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
    Set ExcelSheet = Nothing
End Sub

Private Sub cmdSet_Click()
    With ExcelSheet
        If WorkSheet > 1 Then
        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...
gsmothermanAuthor Commented:
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,
I would remove excel from the machine in question and THEN re-install excel (with the same version of excel as the machine that works!!!)

I'm sure there is a problem with the registry... this has happen to me... and by re-installing it works most of the time... in other cases I had to actually restart from from ground zero and format the drive!!!

Good luck,

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