Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 macro: Dialogbox for saving a file as type Microsoft Ex
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 macro: Dialogbox for saving a file as type MicrosoftEx
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 \ On Feb 18, 5:38*pm, Guus wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 macro: Dialogbox for saving a file as type Microsof
Excuse me....
Your suggestion, I cannot get it working. IS there no solution like Application.Dialogs(xlDialogSaveAs).Show ? Thank you in advance. Guus "Avi" wrote: 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 \ On Feb 18, 5:38 pm, Guus wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 macro: Dialogbox for saving a file as type Microsof
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 macro: Dialogbox for saving a file as type Microsof
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 "Jerry Khann" wrote: Hello "Guus" a écrit dans le message de news: ... 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(MyFileNam e , xlWorkbookDefault) If etat = True Then MsgBox ActiveWorkbook.FullName & " has been saved." End If Regards -- Jerry Khann Adresse invalide: retirer le bouchon _O_ et .invalid |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007: Saving a file with a Macro | Excel Discussion (Misc queries) | |||
Microsoft Excel 2007, Sharing Violation when saving file | Excel Discussion (Misc queries) | |||
Saving Excel 2007 file in 2003 creates very large file | Excel Discussion (Misc queries) | |||
Microsoft Excel Viewer 2003 Supported file type | Excel Discussion (Misc queries) | |||
Saving Charts with Microsoft Office 2007 | Excel Discussion (Misc queries) |