Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

vb6, excel Run-time error 1004 method 'close' of object 'workbooks' failed

Posted on 2016-11-14
7
Medium Priority
?
150 Views
Last Modified: 2016-11-15
I have following code in VB6 application where i open file for reading and storing values in dimension. i close the file and open file again for updating values. but while closing it give error  Run-time error 1004  Method 'Close' of object 'Workbooks' failed'

following is my code

    Dim XLSBK              As New Excel.Application
    Dim XLwsh              As New Excel.Worksheet

    Dim fs
    Set fs = CreateObject("scripting.filesystemobject")
    SelParamFileName = Trim(ctlSELPARAMFILENAME.Text)
    If Trim(Len(SelParamFileName)) = 0 Then
       MsgBox "Select Excel Parameter file", vbCritical, Me.Caption
'       cmdPARAMSEARCH.SetFocus
       Exit Sub
    End If
'**********************************************************************
    If Not fs.FileExists(SelParamFileName) Then
        MsgBox "Warning - Not a valid Excel Parameter file name ", vbCritical
        Exit Sub
    End If
'**********************************************************************
    XLSBK.DefaultFilePath = SelParamFileName
'**********************************************************************
    XLSBK.Workbooks.Open SelParamFileName, , , , , , , , , True
    XLSBK.Visible = False
     For Ctr = 1 to 20
                XLSBK.Cells(Ctr, 11) = "updateremark"
      next        
 
     XLSBK.Workbooks.Close ( ON THIS LINE SYSTEM GOT HANG AND GIVE MESSAGE RETRY OR SWITCH TO - WHEN PRESS CLT+ALT+DEL
       TO TERMINATE PROCESS - ERROR 1004 )



Thanks in advance .
0
Comment
Question by:nitin_s_shah
[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
  • 2
  • 2
7 Comments
 
LVL 49

Expert Comment

by:Martin Liss
ID: 41886637
XLSBK.Workbooks(SelParamFileName).Close
0
 

Author Comment

by:nitin_s_shah
ID: 41886657
Dear Martin Liss

XLSBK.Workbooks(SelParamFileName).Close  

is giving error - Subscript out range
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 41886665
Try
XLSBK.Workbooks(1).Close
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 53

Accepted Solution

by:
Ryan Chong earned 2000 total points
ID: 41887250
or you can define a Excel.Workbook object , like:

Dim XLSworkbook              As New Excel.Workbook
...

set XLSworkbook = XLSBK.Workbooks.Open(SelParamFileName, , , , , , , , , True)
    XLSworkbook.Visible = False
Set XLwsh = XLSworkbook.WorkSheets(1)
     For Ctr = 1 to 20
                XLwsh.Cells(Ctr, 11) = "updateremark"
      next        
     XLSworkbook.Save
     XLSworkbook.Close
Set XLwsh = Nothing
Set XLSworkbook = Nothing
....

Open in new window

0
 

Author Comment

by:nitin_s_shah
ID: 41887798
Dear Ryan Chong

It is working file but at instruction

XLSworkbook.Save - It give message ' A file namned 'RESUME.XLW' already exists in this location. do want to replace it?
yes - no - cancel


when i say YES it is saving in same file. but i could not find file name in my computer -  'RESUME.XLW'

thanks sir
0
 
LVL 53

Assisted Solution

by:Ryan Chong
Ryan Chong earned 2000 total points
ID: 41889060
if problem persists... you may try use SaveAs instead:
XLSworkbook.SaveAs SelParamFileName

Open in new window

I'm not too sure about the .xlw issue, but it seems linked to excel workspace?
0
 

Author Closing Comment

by:nitin_s_shah
ID: 41889247
Thanks Ryan Chong,


It is working fine.

Thanks for kind prompt reply.

Thanks
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

721 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