Solved

Excel & VB - Open method failed!

Posted on 1998-07-10
3
671 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
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
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 utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

867 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now