Still a problem

I had posted a problem with an automation error. Its been bugging me so I decided to play some more. This is an error when I m trying to save an excel file. If I do the following:

Code:

---


'turn on the hourglass
'cause this could take some time...
Screen.MousePointer = vbHourglass

objXL.DisplayAlerts = False                      'don't show any alerts
objXL.Visible = False                            'hide the sheet

objXL.Application.ScreenUpdating = True          'allow updates

    If (WorkSheetExists(Me.txtTPos.Text, objXL)) Then
        'do nothing
        'sheet exists already so we just need to add the components
    Else
        'create the sheet
        Call CreateWorkSheet(Me.txtTPos.Text, objXL)
        objXLWrkBk.Save
    End If

Call InsertComponents(Me.txtTPos.Text, objXL)    'now insert the components
   
    'objXLWrkBk.Close SaveChanges:=True              'save the changes
objXL.Quit                                        'quit excel

---

It quits the excel application but ASKS me if I want to save...I dont want it to ask me this. So I used the .DisplayAlerts and set it to false with no luck. Also I tried the one with the ' in front of it the:

Code:

---

    'objXLWrkBk.Close SaveChanges:=True              'save the

---

This used to work in my previous access application but it keeps giving me an automation error. So I had to comment it out. Basically I want to save this excel file and quit. But its not working!!!!

Please help if possible.

Jon

I think to avoid the Save dialog, you use the Save As method of the workbook to save it first. Then subsequent calls to Save use the same filename. If you haven't saved your workbook once, you need to call Save As one time, and pass it the path and File Name you want to save it as.

Quote:

---

Originally posted by seaweed
I think to avoid the Save dialog, you use the Save As method of the workbook to save it first. Then subsequent calls to Save use the same filename. If you haven't saved your workbook once, you need to call Save As one time, and pass it the path and File Name you want to save it as.

---

In help:

SaveAs Method


Saves changes to the sheet (Syntax 1) or workbook (Syntax 2) in a different file.

I dont want to save to a different file that is why I dont want saveas.

Jon

So if it's an existing file then that's true, but if it's a new file then you need to do SaveAs first. From help:

Quote:

---

The first time you save a workbook, use the SaveAs method to specify a name for the file

---

Is this an exisiting file you're working with? Then you should be able to do Workbook.Save

Yes this is an existing file...I continue to get error messages with this:

Class does not support automation or expected interface

VB Code:

Screen.MousePointer = vbHourglass
 
objXL.DisplayAlerts = False                      'don't show any alerts
objXL.Visible = False                            'hide the sheet
 
objXL.Application.ScreenUpdating = True          'allow updates
 
    If (WorkSheetExists(Me.txtTPos.Text, objXL)) Then
        'do nothing
        'sheet exists already so we just need to add the components
    Else
        'create the sheet
        Call CreateWorkSheet(Me.txtTPos.Text, objXL)
        objXLWrkBk.Save
    End If
 
Call InsertComponents(Me.txtTPos.Text, objXL)    'now insert the components
    
    objXLWrkBk.Save
     'objXLWrkBk.Close SaveChanges:=True               'save the changes
     objXL.Quit                                        'quit excel

Wierd. My last suggestion is to try:

VB Code:

Dim objTmpWrkbk As Excel.Workbook
    
    For Each objTmpWrkbk In objXL.Workbooks
        objTmpWrkbk.Save
    Next
    objXL.Quit

but your error suggest something else might be wrong.

Can I cuss on here?

Cause ur faaaaaaarking awesome :)

Thanks d00d :)

Jon

:) Glad it helped! :)

Hey seaweed one other problem.
I have a button to delete a sheet...after I delete it I save the excel file and quit. But everytime I try to delete...I get a message saying the selected cells have values are u sure you want to delete...
I dont want this message...I just want it to delete:

Heres the code:

Code:

---

Public Function DeleteCalcWorksheet(calcSheet As String, Pos As String)
On Error GoTo Err_Handler
'function deletes a work sheet in the calculation sheet
'of Microsoft Excel.

Dim objXL As Excel.Application
Dim objXLWrkBk As Excel.Workbook
Dim objXLWrkSht As Excel.Worksheet

Set objXL = CreateObject("Excel.Application")
objXL.Visible = False
objXL.DisplayAlerts = False
Set objXLWrkBk = objXL.Workbooks.Open(calcSheet)
   
If (WorkSheetExists(Pos, objXL)) Then
    Set objXLWrkSht = objXLWrkBk.Worksheets(Pos)
    objXLWrkSht.Delete
    objXLWrkBk.Close SaveChanges:=True
    objXL.Quit
Else
    'work sheet not found
    'do nothing
End If

Done:
Set objXLWrkSht = Nothing
Set objXLWrkBk = Nothing
Set objXL = Nothing
Exit Function

Err_Handler:
MsgBox Err.Description, vbCritical, "Error #: " & Err.Number
Resume Done
   

End Function

---

thanlks jon

Setting DisplayAlerts to False should take care of it, but it looks like you are doing that already. You might try setting it again just before the call and see if that helps(?).

Tried that no luck :(

This is weird now its working...
Tomorrow it wont...
Well C how it goes.

Thanks,
Jon