Saving in Excel....again!

[WeeBeng]

I have posted this question with regards to saving to excel in VB before.
I want to use a common dialogue box to choose the file to save to excel instead of its default save dialogue box. However, the default save dialogue box supplied by excel whenever when I code Xlapp.save keeps coming up .The solution which was to use Xlapp.displayalerts= False where Xlapp is the excel object does not seem to work.Moreover I found that the file saved to was not what was selected in my common dialogue box but rather the default save dialogue box supplied by excel.I have tried to override this by using
Xlapp.save("cdbox.filename") but it does not work.

What should I do to prevent the default save dialogue box from appearing and ensuring the file is saved to what i choose in the common dialogue box?
Below is my code:


private Sub Command1_Click()

Dim excel_app as Object
Dim excel_sheet as Object
Dim new_value as string
Dim first_row as Integer
Dim first_col as Integer
Dim num_rows as Integer
Dim num_cols as Integer

' Create the Excel application.
set excel_app = CreateObject("Excel.Application")

' Uncomment this line to make Excel visible.
' excel_app.Visible = true

' Open the Excel spreadsheet.
excel_app.Workbooks.Open ("c:\windows\desktop\beng\output.xls")

' Check for later versions.
If Val(excel_app.Application.Version) >= 8 then
set excel_sheet = excel_app.ActiveSheet
else
set excel_sheet = excel_app
End If

' get and display the bounds.
first_row = excel_sheet.UsedRange.Row
first_col = excel_sheet.UsedRange.Column
num_rows = excel_sheet.UsedRange.Rows.Count
num_cols = excel_sheet.UsedRange.Columns.Count

MsgBox "Rows: " & Format$(first_row) & _
" - " & Format$(first_row + num_rows - 1) & vbCrLf & _
"Cols: " & Format$(first_col) & _
" - " & Format$(first_col + num_cols - 1)

excel_sheet.cells(num_rows, 1).Value = "Location"
excel_sheet.cells(num_rows, 2).Value = " Max Received signals"
excel_sheet.cells(num_rows, 4).Value = "Received signals"
excel_sheet.cells(num_rows, 6).Value = " Min Received signals"
excel_sheet.cells(num_rows, 8).Value = "Simulated signals"


' excel_sheet.cells(1, 2).WordWrap true
'excel_sheet.cells(1, 4).WordWrap true
'excel_sheet.cells(1, 6).WordWrap true
'excel_sheet.cells(1, 8).WordWrap true




' Comment the rest of the lines to keep
' Excel running so you can see it.

' Close the workbook without saving.
excel_app.DisplayAlerts = false
cdbox.ShowSave

excel_app.save ("cdbox.filename")
excel_app.ActiveWorkbook.Close false
'cdbox is the name of the common dialogue box
' Close Excel.
excel_app.Quit
set excel_sheet = nothing
set excel_app = nothing


End Sub

[GungaDin]

Have you tried SaveAs? I didn't know you could use Save for an Excel.Application. SaveAs will work for the Workbook.

eg.
excel_app.ActiveWorkbook.SaveAs [filename]

Hope this helps,

Nathan.