Excel Save as (1 Viewer)

[isladogs]

I have NEVER used Call with .Filters.Clear or .Filters.Add in this context. Access doesn't complain.
In fact I hardly ever use Call in any of my code

This is the note in the link @Gasman helpfully provided in post #18

A run-time error occurs if the Filters property is used in conjunction with the Clear, Add, or Delete methods when applied to a Save As FileDialog object. For example, Application.FileDialog(msoFileDialogSaveAs).Filters.Clear will result in a run-time error.

 

[Gasman]

Wow, I just realised that was not your code @Gismo
My apologies.
I have never seen the word Call on any of the filter examples I have googled.

 

[Minty]

Interesting - Yes it is late-bound, and I'm guessing I have only ever used it as a file picker for template files, not to SaveAs, as part of the file dialog.

I normally save all created export files with a descriptive name and the date in _yyyymmdd format, so can't say I would have seen the error.

 

[Gasman]

Interesting - Yes it is late-bound, and I'm guessing I have only ever used it as a file picker for template files, not to SaveAs, as part of the file dialog.

I normally save all created export files with a descriptive name and the date in _yyyymmdd format, so can't say I would have seen the error.

Well I changed the test from 4 to 2 and it still falls over at the Filters.Add ?

Suffice to say, there is enough there to work with for the o/p, even if it is not that generic?

This seems to work on just a quick test?

        If lngType <> 2 And lngType <> 4 Then
            'Reset then add filter patterns separated by tildes (~) where
            '  multiple extensions are separated by semi-colons (;) and the
            '  description is separated from them by a comma (,).
            '  Example strPattern :
            '  "MS Access,*.ACCDB; *.MDB~MS Excel,*.XLSX; *.XLSM; *.XLS"
            .filters.Clear
            For Each varEntry In Split(strPattern, "~")
                .filters.Add Split(varEntry, ",")(0), Split(varEntry, ",")(1)
            Next varEntry
        End If
        'Set some default settings
        .InitialFileName = strInitialDir
        .AllowMultiSelect = False
        .InitialView = 2                          'msoFileDialogViewDetails
        'Only return a value from the FileDialog if not cancelled.
        If .Show Then strGetFileFolderName = .SelectedItems(1)

filters did not change to Filters even when I made fDialog a FileDialog object?

 

[Isaac]

filters did not change to Filters even when I made fDialog a FileDialog object?

It does for me.

To truly early bind:

- Reference set to Microsoft Office ##.# Object Library
- Variable declared as Office.FileDialog

then filters changes to Filters

 

[Gasman]

Ah, I just changed it to filedialog and intellisense changed it to FileDialog, so I thought it had been recognised.?
I already had Office 12 Library in my references.

Even after changing it to Office.FileDialog, they still stay at .filter?

 

[Gismo]

Hi All,

Thank you for the assistance although it did not make any sense to me and I could not get the code to work
All I did was to comment out the call code parts

How would I incorporate the below code to prefill the save as block with the query name and the lookup control?

-Registration is a control on my form

DoCmd.OutputTo acOutputQuery, "CS Orders - Spares for Orders QRY", "Excel97-Excel2003Workbook(*.xls)", "Spares for Orders" & " - Registration -" & " " & Forms![CS Orders Detail - Main]![RegCBO] & ".xls", True, "", , acExportQualityPrint

Private Sub ExcelSparesforOrders__ExcelSparesforOrders_Click()

Dim StrFileName As String
Dim qryName As String
Dim strSaveFile As String

StrFileName = strGetFileFolderName(, 2, "Excel")
qryName = "CS Orders - Spares for Orders QRY"

DoCmd.TransferSpreadsheet acExport, 10, qryName, strSaveFile, True, , True
End Sub

 

[Gasman]

https://www.google.com/search?q=pre...j33i10i160.15609j0j4&sourceid=chrome&ie=UTF-8

 

[Isaac]

Check out Excel vba's method GetSaveAsFileName. I believe you can prefill a default. You'll have to put some effort of your own into learning things incrementally, and reading through Microsoft's documentation on each method, property, object, etc.

Good luck with your project.

 

[Gismo]

I looked at the suggested link

I still get an error on below code for "The action or method requires a file Name argument"

DoCmd.TransferSpreadsheet acExport, 10, qryName, strSaveFile, True, , True

When I change it to below code, It actually exports the spreadsheet but does not auto populate the file name and I do net get the error

It shouldns save before a new location has been selection

DoCmd.TransferSpreadsheet acExport, 10, qryName, "Test", True, , True

 

[Gasman]

So, you need to get the filename from the filedialog?
You pass in whatever folder and name you want in the first parameter?, second parameter should be 2 as you are using SaveAs
Then you take the result of the filedialog and put that into your transferspreadsheet?

 

[Gismo]

Just can not get it to work as I want

I tried a few different methods, goggled a few
Unfortunately, We had a drop in network and the file was corrupted, so I don't have a backup of all my tests

I need your assistance please, maybe this one is above my knowledge

 

[Gasman]

StrFileName = strGetFileFolderName(, 2, "Excel")

DoCmd.TransferSpreadsheet acExport, 10, qryName, strSaveFile, True, , True

Can you now see what is wrong with your code?

 

[Gismo]

Nope, sorry, I dont, not sure what I am missing here

 

[isladogs]

Keep staring at those two lines. @Gasman has given you all the clues you need to fix it in posts #31 & #33.

 

[Gismo]

second line does not refer to the getfile folder name?

I have played around to bring the StrFile name into the do transferspreadsheet equation but I had even more error messages

I still get the file name argument error

 

[Gasman]

Its is not called strFile though is it , it is strFileName ?

You have to get the words EXACTLY right. You and I would know that you meant strFileName, but a computer will not?

Also just telling us 'but I had even more error messages' does nothing to allow us to help you?

Sit back, take a breath and try and work out the actual steps you need to take?

 

[Gismo]

Its is not called strFile though is it , it is strFileName ?

You have to get the words EXACTLY right. You and I would know that you meant strFileName, but a computer will not?

Also just telling us 'but I had even more error messages' does nothing to allow us to help you?

Sit back, take a breath and try and work out the actual steps you need to take?

Yes, I do have that exactly

Public Function strGetFileFolderName(Optional strInitialDir As String = "", Optional lngType As Long = 4, Optional strPattern As String = "All Files,*.*") As String
'StrInitialDir = where the filedialog starts browsing
'lngType e.g. 'msoFileDialogFilePicker = 3, msoFileDialogFolderPicker =4, msoFileDialogOpen=1,msoFileDialogSaveAs=2

Dim fDialog As Object
Dim vFile As Variant, varEntry As Variant


strGetFileFolderName = ""

Set fDialog = Application.FileDialog(lngType)

With fDialog
.Title = "Browse for "
Select Case lngType
Case 1 'msoFileDialogOpen
.Title = .Title & "File to open"
Case 2 'msoFileDialogSaveAs
.Title = .Title & "File to SaveAs"
Case 3 'msoFileDialogFilePicker
.Title = .Title & "File"
Case 4 'msoFileDialogFolderPicker
.Title = .Title & "Folder"
End Select

Select Case strPattern
Case "Excel"
strPattern = "MS Excel,*.XLSX; *.XLSM; *.XLS"
Case "Access"
strPattern = "MS Access,*.ACCDB"
Case "PPT"
strPattern = "MS Powerpoint,*.PPTX; *.PPTM"
End Select


If lngType <> 4 Then
'Reset then add filter patterns separated by tildes (~) where
' multiple extensions are separated by semi-colons ( and the
' description is separated from them by a comma (,).
' Example strPattern :
' "MS Access,*.ACCDB; *.MDB~MS Excel,*.XLSX; *.XLSM; *.XLS"

'.filters.Clear
For Each varEntry In Split(strPattern, "~")
'.filters.Add Split(varEntry, ",")(0), Split(varEntry, ",")(1)

Next varEntry
End If
'Set some default settings
.InitialFileName = strInitialDir
.AllowMultiSelect = False
.InitialView = 2 'msoFileDialogViewDetails
'Only return a value from the FileDialog if not cancelled.
If .Show Then strGetFileFolderName = .SelectedItems(1)

End With

ExitHere:
Exit Function

HandleErrors:
MsgBox "Error: " & Err.Description & " (" & Err.Number & ")"
Resume ExitHere
End Funcion

Private Sub ExcelSparesforOrders__ExcelSparesforOrders_Click()

Dim StrFileName As Variant
Dim qryName As String
Dim strSaveFile As String

StrFileName = strGetFileFolderName(, 2, "Excel")
qryName = "CS Orders - Spares for Orders QRY"


DoCmd.TransferSpreadsheet acExport, 10, qryName, strSaveFile, True, , True
'DoCmd.TransferSpreadsheet acExport, 10, "CS Orders - Spares for Orders QRY", "C:\Users\e498309\Test.xls", True, StrFileName, True
'DoCmd.TransferSpreadsheet acExport, 10, qryName, strSaveFile, True, , True
End Sub

I will look at this again

 

[Gasman]

FWIW, I just tried your code (amended to work though) and I successfully exported a query to the Excel file.

Just a quick test.

Sub TestSaveAs()
Dim strFileName As String, strQryName As String

strFileName = strGetFileFolderName("C:\Temp\", 2, "Excel")
Debug.Print strFileName
strQryName = "qryTable1"

DoCmd.TransferSpreadsheet acExport, 10, strQryName, strFileName, True, , True
End Sub

However I had previously amended strGetFileFolderName as below, as the docs says filters will not work with SaveAs ?

If lngType <> 2 And lngType <> 4 Then

so it opens to the correct folder, but shows all files, so the "Excel" parameter is not used?

 

[Gismo]

FWIW, I just tried your code (amended to work though) and I successfully exported a query to the Excel file.

Just a quick test.

Sub TestSaveAs()
Dim strFileName As String, strQryName As String

strFileName = strGetFileFolderName("C:\Temp\", 2, "Excel")
Debug.Print strFileName
strQryName = "qryTable1"

DoCmd.TransferSpreadsheet acExport, 10, strQryName, strFileName, True, , True
End Sub

However I had previously amended strGetFileFolderName as below, as the docs says filters will not work with SaveAs ?

If lngType <> 2 And lngType <> 4 Then

so it opens to the correct folder, but shows all files, so the "Excel" parameter is not used?

Sorry, yes, I did change the IngType as per your suggestion
Just remembered I did not change it back again after I had the file corruption

I do get the browse for file to saveas but the file name does not appear in the File name field

And on Cancel, I still get the File name argument error

Sorry, this one seems to get me and I am sure I am missing something stupid

Private Sub ExcelSparesforOrders__ExcelSparesforOrders_Click()

Dim StrFileName As String
Dim StrQryName As String
Dim StrSaveFile As String

StrFileName = strGetFileFolderName(, 2, "Excel")
StrQryName = "CS Orders - Spares for Orders QRY"


'DoCmd.TransferSpreadsheet acExport, 10, StrQryName, StrSaveFile, True, , True
DoCmd.TransferSpreadsheet acExport, 10, StrQryName, StrFileName, True, , True
'DoCmd.TransferSpreadsheet acExport, 10, "CS Orders - Spares for Orders QRY", "C:\Users\e498309\Test.xls", True, StrFileName, True
'DoCmd.TransferSpreadsheet acExport, 10, qryName, strSaveFile, True, , True
End Sub

The error remains in the DoCmd.TransferSpreadsheet

Debug.Print StrFileName also gives zero output