VB6 & MS Excel - Runtime error '1004'

Nitin Shah
Nitin Shah used Ask the Experts™
on
I have VB 6 application which read excel file as input file and generate excel .csv file as output, This is working fine on windows-xp, windows 7. But when i run application in windows 8.1 it is giving error as

Run-time error '1004':
Method '~' of object '~' failed.

I am using MS Excel 2013.

what could be reason for this.

Thanks in advance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software Team Lead
Commented:
quick check if there's any Missing References in your Macro?
how your code looks like when you define the Excel objects?

instead of using:
Dim xlsApp As Excel.Application
Set xlsApp = New Excel.Application
...

Open in new window

try use:
Dim xlsApp As Object
Set xlsApp = CreateObject("Excel.Application")
...

Open in new window

instead, that could help.
GrahamSkanRetired
Top Expert 2012

Commented:
We will need to see the code and to know where it fails,

If you are running from a compiled .exe, switch to running the source code in the VBA IDE to see the failure point.

Author

Commented:
Dear GrahamSkan,

I am not able to install vb6 on windows 8.1 profession.

Thanks
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Dear Ryan Chong,

following is my code.

Option Explicit
'************************************************************
    Dim XLSBK                    As New Excel.Application
    Dim XLwsh                    As New Excel.Worksheet
'************************************************************
    Dim exRow As Long, exCol As Integer, SelRowPerFile As Integer, SelPerFileNo As Integer
    Dim InpRow As Long, InpCol As Integer, InpData As String, InpError As String, ErrSw   As Integer
'************************************************************
    Dim mvExcel As New Excel.Application
    Dim mvWSht As New Excel.Worksheet
    Dim fileName As String, SaveFileName As Double
'************************************************************


''command button to open excel file

Private Sub OpenExcelDataBase()
'**********************************************************************
 
    Dim fs
    Set fs = CreateObject("scripting.filesystemobject")
    SelExcelFileName = Trim(ctlSELEXECLFILENAME.Text)
    If Trim(Len(SelExcelFileName)) = 0 Then
       MsgBox "Select Excel file", vbCritical, Me.Caption
       Exit Sub
    End If
'**********************************************************************
    If Not fs.FileExists(SelExcelFileName) Then
        MsgBox "Warning - Not a valid Excel file name ", vbCritical
        Exit Sub
    End If
'**********************************************************************
    XLSBK.DefaultFilePath = SelExcelFileName
'**********************************************************************
    XLSBK.Workbooks.Open SelExcelFileName, , , , , , , , , True
    XLSBK.Visible = False
'**********************************************************************
    If Trim(Len(Trim(ctlEXCELNO.Text))) = 0 Then
       ctlEXCELNO.Text = "0"
    End If
'**********************************************************************
    If Trim(Len(Trim(ctlEXECLHEADER.Text))) = 0 Then
       ctlEXECLHEADER.Text = "0"
    End If
'**********************************************************************
    SelExcelNo = Trim(ctlEXCELNO.Text)
    SelExcelHeaderNo = Trim(ctlEXECLHEADER.Text)
    If SelExcelHeaderNo = 0 Then
       SelExcelHeaderNo = 1
    Else
        SelExcelHeaderNo = SelExcelHeaderNo + 1
    End If
    If SelExcelNo = 0 Then
       SelExcelNo = 1
    Else
       SelExcelNo = SelExcelNo ' + 1
    End If
    XLSBK.Sheets(SelExcelNo).Activate
    Set XLwsh = XLSBK.Sheets(SelExcelNo)
    Dim x As Integer
    x = XLSBK.Sheets.Count
    RowCount = XLSBK.Sheets(SelExcelNo).UsedRange.Rows.Count
    ctlRowCount = Format(RowCount, "0")
'**********************************************************************
   
End Sub
Top Expert 2011
Commented:
Hi, do as Ryan propose first
Dim xlsApp As Object
Set xlsApp = CreateObject("Excel.Application")

and then check to see if you invoke the application with the workfile name:
SET APPLICATION=GETOBJECT(,"EXCEL.APPLICATION")
myworkfile = "Financial-01_GG.xls"
Set myapp = Application.Workbooks(myworkfile)

Open in new window

Then do anything in Excel

Substitute APPLICATION with your EXCEL (i.e. XLSBK)
myworkfile with your variable for the workflie
and myapp with the combination of the two
Top Expert 2011

Commented:
Good comments and suggestions made and author did not get back to provide feedback

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial