Excel 2007 macro: Dialogbox for saving a file as type Microsoft Ex

[Guus]

Hello,
I just have upgrated from Excel 2003 to Excel 2007.

I had in Excel 2003 a macro who import a text file, and saved it as an
Excelfile.

1. The macro opens a textfile using a dialogbox
2. The macro has to show a dialogbox, where the filename (without extension)
will be the same as the textfile, but the suggested FileType has to be
'Excel'.
3. Because the macro opens a textfile, the following VBA-code needs an extra
argument.

Application.Dialogs(xlDialogSaveAs).Show File

My question:
Who can help me to complete the just mentioned VBA-code to fill the "Save as
Type" in the dialogbox as 'Excel Workbook (*.xlsx)"

Thank you in advance,
Guus

 

[Avi]

Please refer to the following code to have custom save as. (Source:
Excel Help)
Sub Main()

'Declare a variable as a FileDialog object.
Dim fd As FileDialog

'Create a FileDialog object as a File Picker dialog.
Set fd = Application.FileDialog(msoFileDialogFilePicker)

'Declare a variable to contain the path
'of each selected item. Even though the path is aString,
'the variable must be a Variant because For Each...Next
'routines only work with Variants and Objects.
Dim vrtSelectedItem As Variant

'Use a With...End With block to reference the FileDialog object.
With fd

'Add a filter that includes GIF and JPEG images and make it
the first item in the list.
.Filters.Add "Images", "*.gif; *.jpg; *.jpeg", 1

'Use the Show method to display the File Picker dialog box and
return the user's action.
'If the user presses the button...
If .Show = -1 Then

'Step through each string in the FileDialogSelectedItems
collection.
For Each vrtSelectedItem In .SelectedItems

'vrtSelectedItem is aString that contains the path of
each selected item.
'You can use any file I/O functions that you want to
work with this path.
'This example displays the path in a message box.
MsgBox "Selected item's path: " & vrtSelectedItem

Next vrtSelectedItem
'If the user presses Cancel...
Else
End If
End With

'Set the object variable to Nothing.
Set fd = Nothing

End Sub


\

 

[Guus]

Excuse me....
Your suggestion, I cannot get it working.

IS there no solution like
Application.Dialogs(xlDialogSaveAs).Show ?

Thank you in advance.
Guus

 

[Jerry Khann]

Hello

Excuse me....
Your suggestion, I cannot get it working.

IS there no solution like
Application.Dialogs(xlDialogSaveAs).Show ?

MyFileName = "MyFile.xlsx" ' Or a result of an expression like a
question to the user
BackState = Application.Dialogs(xlDialogSaveAs).Show(MyFileName ,
xlWorkbookDefault)
If etat = True Then
MsgBox ActiveWorkbook.FullName & " has been saved."
End If

Regards

 

[Guus]

The following VBA-code seems to be Excel-2007-proof !

Sub FileSaveXL2007()
Dim FName As Variant
Dim FileFormatValue As Long

'STEL EXCEL-VERSIE VAST
If Val(Application.Version) < 12 Then
'Using Excel97-2003
FName = Application.GetSaveAsFilename(InitialFileName:="", _
FileFilter:=" Excel 2000-2003 Workbook (*.xls),
*.xls,", _
FilterIndex:=2, Title:="Save File in Excelversion")
FileFormatValue = -4143
Else
'The option which Excel-2007 proof is
FName = Application.GetSaveAsFilename(InitialFileName:="", _
FileFilter:=" Excel Macro Free Workbook (*.xlsx),
*.xlsx," & _
" Excel Macro Enabled Workbook
(*.xlsm), *.xlsm," & _
" Excel 2000-2003 Workbook (*.xls),
*.xls," & _
" Excel Binary Workbook (*.xlsb),
*.xlsb", _
FilterIndex:=2, Title:="Save File in Excelversion")
If FName <> False Then
Select Case LCase(Right(FName, Len(FName) - InStrRev(FName, ".",
, 1)))
Case "xls": FileFormatValue = 56
Case "xlsx": FileFormatValue = 51
Case "xlsm": FileFormatValue = 52
Case "xlsb": FileFormatValue = 50
Case Else: FileFormatValue = 0
End Select

'Save the File in the Version which belongs to the chosen extension
If FileFormatValue = 0 Then
MsgBox "Sorry, this is an unknown File Format"
Else
ActiveWorkbook.SaveAs FName, _
FileFormat:=FileFormatValue, CreateBackup:=False
End If
End If
End If
End Sub

With lot of thanks to the Excel-experts, going around on the internet.
Guus