Solved

Excel & VB - Open method failed!

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

705 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

17 Experts available now in Live!

Get 1:1 Help Now