Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 416
  • Last Modified:

How to close an Excel file

I have opened an Excel file with:
    Set myxl = CreateObject("Excel.Application")
    Set wb = myxl.Workbooks.Open(fid)

What commands do I need to save and close it?
0
jc31415
Asked:
jc31415
  • 12
  • 6
  • 3
  • +1
1 Solution
 
JuergenHartlCommented:
wb.Close true, fid
0
 
jc31415Author Commented:
I want to save it back to the same name I opened. In that case do I need "fid". Also what does the "true" operand mean? Does it mean to save it?
0
 
jc31415Author Commented:
I tried your command, and something is still wrong. When I loop back to open another excel file I got an error on the following command:

   Set wb = myxl.Workbooks.Open(fid)

After the above close, excel.exe is still showing in the Windows Task Manager.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
JuergenHartlCommented:
the command does not close excel it just closee the workbook!
in order to close Excel use

myxl.Quit
0
 
jc31415Author Commented:
I already had myxl.quit.
After the myxl.quit I show a msgbox, and open Windows Task Manager. It shows excel.exe active. When I reply to the msgbox it tries to open another excel file and gets the error I mentioned above.
0
 
flyers_patCommented:
wb.SaveCopyAs fid
wb.close false 'False is for cancel the changes since we already saved it
myxl.application.quit
set wb=nothing
set myxl = nothing


the savecopyas will overwrite any existing file in the same directory unless this file is open at the moment

if you want to make sure to not get any error from excel, insert:

myxl.displayalerts=false
0
 
jc31415Author Commented:
I added the above code, and I got an error on:

wb.savecopyas fid

Run Time Error 1004
Cannot Access 'myExcel.xls'

Where 'myExcel.xls' is the file I opened and updated.

I tried to save it back as the same name I opened. I don't want to keep changing the name. Can't I keep it as the same name?
0
 
jc31415Author Commented:
I tried saving it as a new name and got:

Run Time Error 1004
SaveCopyAs method of Workbook class failed.
0
 
EDDYKTCommented:
myxl.ActiveWorkbook.SaveAs FileName:=fid
myxl.ActiveWorkbook.Close
myxl.Quit
Set myxl  = Nothing
0
 
jc31415Author Commented:
Even though my program now runs, there is still an excel.exe entry left in windows task manager each time I run it. If I open an excel file by clicking on the name in windows explorer the data part of excel display is missing, it is filled with whatever happens to be on the screen.
0
 
EDDYKTCommented:
You can use

dim  myxl as object
0
 
jc31415Author Commented:
I added the above command as follows:

    Dim myxl As Object
    Set myxl = CreateObject("Excel.Application")
    Set wb = myxl.Workbooks.Open(fid)
0
 
jc31415Author Commented:
I forgot to say, even with this addition I still have the same problem described above.
0
 
EDDYKTCommented:
show all your code
0
 
jc31415Author Commented:
Here is my code:

Private Sub cmdExit_Click()
    End
End Sub

Private Sub cmdRun_Click()
    fid = "WeeklyParts.xls"
    Path = "C:\Documents and Settings\jerryc\My Documents\My Files\vb\MYstatus"
    Call readitw(Path & "\" & fid)
    Close #1
    Close #2
    Close #3
    Close #4
    MsgBox "done"
End Sub

Private Sub readitw(fid)
    Dim myxl As Object
    Set myxl = CreateObject("Excel.Application")
    Set wb = myxl.Workbooks.Open(fid)
    myxl.Sheets("sheet1").Select
    Open "file1.txt" For Output As #1
    Open "file2.txt" For Output As #2
    Open "file3.txt" For Output As #3
    Open "file4.txt" For Output As #4
    For i = 2 To 1000
        partNumber = myxl.Cells(i, 1).Value
        If partNumber = Null Then Exit For
        ar = partNumber
        partNumber = Right(partNumber, 5)
        submitter = myxl.Cells(i, 2).Value
        Version = myxl.Cells(i, 3).Value
        ddd = myxl.Cells(i, 4).Value
        Status = myxl.Cells(i, 5).Value
        fixVersion = myxl.Cells(i, 6).Value
        If fixVersion = Null Then fixVersion = ""
        subsystem = myxl.Cells(i, 7).Value
        Description = myxl.Cells(i, 8).Value
        superceeded = myxl.Cells(i, 9).Value
        If superceeded = Null Then
            superceeded = ""
        ElseIf Len(superceeded) < 5 Then
            superceeded = ""
        Else
            superceeded = Right(superceeded, 5)
        End If
        associated = myxl.Cells(i, 10).Value
        Keywords = myxl.Cells(i, 11).Value
        If Status <> "Broken" & Status <> "Scrap" Then
            If Keywords Like "*document*" Then
                Key = "document"
            Else
                Key = ""
            End If
            Data = ar & Chr(1) & submitter & Chr(1) & Version & Chr(1) & ddd
            Data = Data & Chr(1) & Status & " " & fixVersion & " " & superceeded
            Data = Data & " " & Key & Chr(1) & Description & Chr(1) & "" & Chr(1)
            Print #3, Data
        Else
            Print #1, Data
        End If
        If associated <> Null Then
            Print #4, partNumber & " " & associated
        End If
    Next i
    myxl.Application.DisplayAlerts = False
    myxl.ActiveWorkbook.SaveAs FileName:=fid
    myxl.ActiveWorkbook.Close
    myxl.Quit
    Set myxl = Nothing
    End Sub
0
 
JuergenHartlCommented:
I ran you app as posted and after it executed  
  Set myxl = Nothing
Excel also disappeared in taskmanger like it supposed to. Of course I used a different Excel File.

In you excel file, are there any macros started (auto_xxx) ?

0
 
jc31415Author Commented:
There are macros, but none are started.
0
 
EDDYKTCommented:
I cannot reproduce it too on my computer

i recommend you try to comment out some lines and retest and see which line cause the problem



I also change the line

myxl.Application.DisplayAlerts = False
to
myxl.DisplayAlerts = False
myxl.ActiveWorkbook.SaveAs FileName:=fid
myxl.ActiveWorkbook.Close
set wb = nothing
myxl.Quit
Set myxl = Nothing



ie

Private Sub cmdExit_Click()
    End
End Sub

Private Sub cmdRun_Click()
    fid = "WeeklyParts.xls"
    Path = "C:\Documents and Settings\jerryc\My Documents\My Files\vb\MYstatus"
    Call readitw(Path & "\" & fid)
    Close #1
    Close #2
    Close #3
    Close #4
    MsgBox "done"
End Sub

Private Sub readitw(fid)
    Dim myxl As Object
    Set myxl = CreateObject("Excel.Application")
    Set wb = myxl.Workbooks.Open(fid)
    myxl.Sheets("sheet1").Select
'    Open "file1.txt" For Output As #1
'    Open "file2.txt" For Output As #2
'    Open "file3.txt" For Output As #3
'    Open "file4.txt" For Output As #4
'    For i = 2 To 1000
'        partNumber = myxl.Cells(i, 1).Value
'        If partNumber = Null Then Exit For
'        ar = partNumber
'        partNumber = Right(partNumber, 5)
'        submitter = myxl.Cells(i, 2).Value
'        Version = myxl.Cells(i, 3).Value
'        ddd = myxl.Cells(i, 4).Value
'        Status = myxl.Cells(i, 5).Value
'        fixVersion = myxl.Cells(i, 6).Value
'        If fixVersion = Null Then fixVersion = ""
'        subsystem = myxl.Cells(i, 7).Value
'        Description = myxl.Cells(i, 8).Value
'        superceeded = myxl.Cells(i, 9).Value
'        If superceeded = Null Then
'            superceeded = ""
'        ElseIf Len(superceeded) < 5 Then
'            superceeded = ""
'        Else
'            superceeded = Right(superceeded, 5)
'        End If
'        associated = myxl.Cells(i, 10).Value
'        Keywords = myxl.Cells(i, 11).Value
'        If Status <> "Broken" & Status <> "Scrap" Then
'            If Keywords Like "*document*" Then
'                Key = "document"
'            Else
'                Key = ""
'            End If
'            Data = ar & Chr(1) & submitter & Chr(1) & Version & Chr(1) & ddd
'            Data = Data & Chr(1) & Status & " " & fixVersion & " " & superceeded
'            Data = Data & " " & Key & Chr(1) & Description & Chr(1) & "" & Chr(1)
'            Print #3, Data
'        Else
'            Print #1, Data
'        End If
'        If associated <> Null Then
'            Print #4, partNumber & " " & associated
'        End If
'    Next i
    myxl.DisplayAlerts = False
    myxl.ActiveWorkbook.SaveAs FileName:=fid
    myxl.ActiveWorkbook.Close
    set wb = nothing
    myxl.Quit
    Set myxl = Nothing
    End Sub


0
 
jc31415Author Commented:
The following code leaves an excel.exe entry in the windows task manager:

Private Sub readitw(fid)
    Dim myxl As Object
    Set myxl = CreateObject("Excel.Application")
    Set wb = myxl.Workbooks.Open(fid)
    'myxl.Sheets("sheet1").Select
    myxl.DisplayAlerts = False
    myxl.ActiveWorkbook.SaveAs FileName:=fid
    myxl.ActiveWorkbook.Close
    Set wb = Nothing
    myxl.Quit
    Set myxl = Nothing
    End Sub

In order to not have an excel.exe in windows task manager I had to change it to:

Private Sub readitw(fid)
    Dim myxl As Object
    Set myxl = CreateObject("Excel.Application")
    'Set wb = myxl.Workbooks.Open(fid)
    'myxl.Sheets("sheet1").Select
    myxl.DisplayAlerts = False
    'myxl.ActiveWorkbook.SaveAs FileName:=fid
    'myxl.ActiveWorkbook.Close
    Set wb = Nothing
    myxl.Quit
    Set myxl = Nothing
    End Sub

So, I can't even open the fid.
0
 
EDDYKTCommented:
may be just do

Private Sub readitw(fid)
    Dim myxl As Object
    Set myxl = CreateObject("Excel.Application")
    myxl.Workbooks.Open(fid)
    myxl.Sheets("sheet1").Select
    myxl.DisplayAlerts = False
    myxl.ActiveWorkbook.SaveAs FileName:=fid
    myxl.ActiveWorkbook.Close
    myxl.Quit
    Set myxl = Nothing
End Sub
0
 
jc31415Author Commented:
I tried the above code, and it still leaves an entry in windows task manager.
0
 
EDDYKTCommented:
The above code works for me

Why don't you just create a dummy xls and test from there

ie

call  readitw("c:\book1.xls")

if that works then something wrong on your xls file
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 12
  • 6
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now