How to give our own filename in Save Dialog box using Excel Automation

[netizen]

Hi! all,
I'm working with VB6 and Ecel 97 object library. I'm able to open up excel sheet using desired automation also giving a filename for the worksheet (using .Name property of worksheet) as well as Overall window name as the same. Please find the code below:
'**********************************************************
Public Sub ShowExcelFile()
On Error GoTo Sub_Err

Dim oXlApp As Object
Dim oXlBook As Object
Dim oXlSheet As Object

Screen.MousePointer = vbHourglass



Set oXlApp = CreateObject("Excel.Application", "&quot
Set oXlBook = oXlApp.Workbooks.Add
Set oXlSheet = oXlBook.Worksheets("Sheet1&quot

oXlApp.DisplayAlerts = False
oXlBook.Worksheets("Sheet2&quot.Delete
oXlBook.Worksheets("Sheet3&quot.Delete
oXlApp.DisplayAlerts = True
oXlSheet.Name = "MyOrderFile1"
oXlBook.Application.ActiveWindow.Caption = _
"MyOrderFile1"

oXlApp.ActiveWindow.DisplayGridlines = Not _
(oXlApp.ActiveWindow.DisplayGridlines)

oXlApp.WindowState = 3
oXlBook.Application.Visible = True

Set oXlSheet = Nothing
Set oXlBook = Nothing
Set oXlApp = Nothing


Sub_Close:
Set oXlSheet = Nothing
Set oXlBook = Nothing
Set oXlApp = Nothing
Screen.MousePointer = vbDefault

Exit Sub

Sub_Err:
MsgBox Err.Description, vbOKOnly, "Open Excel"
GoTo Sub_Close
End Sub

'**********************************************************

so when you run this, you'll see th eexcel opened with MyOrderFile1 as sheet and window name. Now when i click save, it shows a SaveAs dialog and the bworkbook name as Book1 or whatever is the latest book number. My question is how to get the save as name as the same that i gave for the worksheet (i.e. 'MyOrderFile1' instead of 'Book1' in the SaveAs dialo box?)

please let me know.

thanks in advance.

regards,
netizen

 

[johnwm]

If you use Object Browser, select Excel, do a search for SaveAs then select the Workbook listing, you will find the following:

SaveAs([Filename], [FileFormat], [Password], [WriteResPassword], [ReadOnlyRecommended], [CreateBackup], [AccessMode As XlSaveAsAccessMode = xlNoChange], [ConflictResolution], [AddToMru], [TextCodepage], [TextVisualLayout], [Local])

which I guess is pretty comprehensive. Let me know if this helps
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'There are 10 kinds of people in the world: those who understand binary, and those who don't.'

 

[netizen]

Surprisingly, i put this question but didn't get a proper response till today. Here is the answer:

Add a command button to a form and set reference to EXCEL OBJECT LIBRARY (maby EXCEL8.OLB or EXCEL9.OLB or EXCEL10.OLB). Then add following code: THis explains clearly what resolution i was asking for. This is for anyone who's interested:

-----------------------------------------------------
Option Explicit

Dim fileSaveName As Variant
Private WithEvents oXlApp As Excel.Application
Private WithEvents oXlBook As Excel.Workbook
Private WithEvents oXlSheet As Excel.Worksheet

Private Sub Command1_Click()
On Error GoTo Sub_Err


Screen.MousePointer = vbHourglass

Set oXlApp = CreateObject("Excel.Application", "&quot
Set oXlBook = oXlApp.Workbooks.Add
Set oXlSheet = oXlBook.Worksheets("Sheet1&quot

oXlApp.DisplayAlerts = False
oXlBook.Worksheets("Sheet2&quot.Delete
oXlBook.Worksheets("Sheet3&quot.Delete
oXlApp.DisplayAlerts = True
oXlSheet.Name = "LandsafeInvoice_10001" 'This will be formed in our program as per latest invoice...
oXlBook.Application.ActiveWindow.Caption = "LandsafeInvoice_10001" 'This will be formed in our program as per latest invoice...

oXlApp.ActiveWindow.DisplayGridlines = Not _
(oXlApp.ActiveWindow.DisplayGridlines)

oXlApp.WindowState = 3
oXlBook.Application.Visible = True


Sub_Close:
Screen.MousePointer = vbDefault

Exit Sub

Sub_Err:
MsgBox Err.Description, vbOKOnly, "Open Excel"
GoTo Sub_Close
End Sub


Sub CreateAndSave()
'This will be called when you click Save on the workbook
'if you click cancel button, it will come back to workbook. Again if you click Save
'it will show the "Save As" dialog. This will continue until you save the workbook once.

fileSaveName = Application.GetSaveAsFilename(oXlSheet.Name, fileFilter:="Excel Files (*.xls), *.xls&quot
If fileSaveName = "" Or fileSaveName = False Then Exit Sub
oXlBook.SaveAs fileSaveName
End Sub

Private Sub oXlBook_BeforeClose(Cancel As Boolean)
'General cleanup
Set oXlSheet = Nothing
Set oXlBook = Nothing
Set oXlApp = Nothing
fileSaveName = ""
End Sub

Private Sub oXlBook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean)
If fileSaveName = "" Then
CreateAndSave
Cancel = True
Else
Cancel = False
'general clean up
Set oXlSheet = Nothing
Set oXlBook = Nothing
Set oXlApp = Nothing
Exit Sub
End If
fileSaveName = "" 'This should not be removed.
End Sub

---------------------------------------------------------