Solved

Excel & VB - Open method failed!

Posted on 1998-07-10
3
687 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:gsmotherman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 1

Expert Comment

by:chdy
ID: 1465440
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...
0
 

Author Comment

by:gsmotherman
ID: 1465441
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
0
 

Accepted Solution

by:
ohayden earned 150 total points
ID: 1465442
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,
Hayden
0

Featured Post

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question