Saveas Dialog Box With .xlsx As Filetype

[Manish_05]

I have crossposts at these forums:


http://www.utteraccess.com/forum/Sav...-t2017675.html

Dear friends,

For last two days i am searching several Forums to get an option to show SaveAs dialog after clicking a button in a form. In some forums i have got some answers but there i was not able to fix the filetype to ".xlsx", which is my main concern (as i have to export a db to excel 2007 format).

Point wise if i want to tell you my problem then it would be:-
- Click on the button, which should popup a SaveAs dialog window (where user can select the location to save the file)
- In SaveAs dialog window filetype should be fixed to ".XLSX"
- The selected path and file name (entered by the user) should then get populated in the text box

Please see the attachment for your help, where i have created a form with the button and the text box.

SaveAs with Filetype.zip

Thanks & Regards,
Manish

[ItsMe]

I use the Clear method and the Add method to the Application.FileDialog object's Filter property.

Here is an example for JEPEGs
.Filters.Clear
.Filters.Add "JPEGs", "*.jpg"

I imagine Worksheets would be something like
.Filters.Clear
.Filters.Add "Excel", "*.xlsx"

[Manish_05]

I use the Clear method and the Add method to the Application.FileDialog object's Filter property.

Here is an example for JEPEGs
.Filters.Clear
.Filters.Add "JPEGs", "*.jpg"

I imagine Worksheets would be something like
.Filters.Clear
.Filters.Add "Excel", "*.xlsx"

Thanks ItsMe for your reply,

Sorry to say, but i am not clear how to start with the hint which you have posted. If you dont mind then can you please upload some sample file/code. Sorry if i am asking extra support from you.

[ItsMe]

Not a problem. I have a fire here I need to put first but I can link to this previously uploaded DB. It has stuff in it you are not asking about (Attachment Fields) but it illustrates the creation of and use of a FileDialog object. You need a reference so check the references list in the sample, Tools>References from within the VBA editor.

I think this is the one....

[Manish_05]

Not a problem. I have a fire here I need to put first but I can link to this previously uploaded DB. It has stuff in it you are not asking about (Attachment Fields) but it illustrates the creation of and use of a FileDialog object. You need a reference so check the references list in the sample, Tools>References from within the VBA editor.

I think this is the one....

Thanks ItsMe, I will try to use your code. Hope it will solve my problem. Thanks a lot for sharing the attachment.

[ItsMe]

Let me know how it goes. The reference you need is Microsoft Office XX.X Object Library.

The line of code that creates the object is
Application.FileDialog(msoFileDialogFilePicker)

The example in sub procedure cmdPicker_Click should get you going....

[Manish_05]

Let me know how it goes. The reference you need is Microsoft Office XX.X Object Library.

The line of code that creates the object is
Application.FileDialog(msoFileDialogFilePicker)

The example in sub procedure cmdPicker_Click should get you going....

Sure brother, and thanks for the quick hints

[Manish_05]

Let me know how it goes. The reference you need is Microsoft Office XX.X Object Library.

The line of code that creates the object is
Application.FileDialog(msoFileDialogFilePicker)

The example in sub procedure cmdPicker_Click should get you going....

The code in my Access file was correct except one small mistake, which was noticed by an online friends in another forum, below is the mistake which i was doing.

- The Export Specification in the TransferDatabase() Method was inconsistent with the Extension for the Filename.

Sub ExportTable()
  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "FinalTable", Forms![Form1]![Text17].Value
End Sub

- namely

acSpreadsheetTypeExcel9 <> *.xlsx

- So had two options:

1. Change the Excel Type via acSpreadsheetTypeExcel9 to a higher Version that supports the *.xlsx Extension.
2. Change all occurrences of .xlsx in the Code (4) to .xls for maximum portability.

- I used the first one and changed the code to

Sub ExportTable()
  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "FinalTable", Forms![Form1]![Text17].Value
End Sub

Here is the link of the forum for my other friends
http://www.utteraccess.com/forum/Sav...-t2017675.html