Excel Save as

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.
Click to expand...
 
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.
 
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.
 
Minty said:
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.
Click to expand...
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?
Code: 
        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?
 
Gasman said:
filters did not change to Filters even when I made fDialog a FileDialog object?
Click to expand...

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
 
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? :)
 
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
Click to expand...

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
Click to expand...

1625736003194.png
 
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.
 
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
Click to expand...

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
Click to expand...

1625813938004.png
 
So, you need to get the filename from the filedialog? :unsure:
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?
 
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 :cautious:
 
Code: 
StrFileName = strGetFileFolderName(, 2, "Excel")

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

Can you now see what is wrong with your code?
 
Nope, sorry, I dont, not sure what I am missing here
 
Keep staring at those two lines. @Gasman has given you all the clues you need to fix it in posts #31 & #33.
 
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
 
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?
 
Gasman said:
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?
Click to expand...
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
Click to expand...

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
Click to expand...

I will look at this again
 
FWIW, I just tried your code (amended to work though) :) and I successfully exported a query to the Excel file.

Just a quick test.
Code: 
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 ?
Code: 
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?
 
Gasman said:
FWIW, I just tried your code (amended to work though) :) and I successfully exported a query to the Excel file.

Just a quick test.
Code: 
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 ?
Code: 
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?
Click to expand...
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

1626086307195.png


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

1626086351579.png


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
Click to expand...

The error remains in the DoCmd.TransferSpreadsheet

Debug.Print StrFileName also gives zero output
 
Last edited:
You must log in or register to reply here.

Similar threads

B
Importing from Excel
Replies
7
Views
379
AndT
A
S
Access table protection from connections and DBreplications
Replies
13
Views
165
isladogs
isladogs
B
Open Excel after transfering a query
Replies
7
Views
125
arnelgp
arnelgp
Rodeo Clown
Access import function converts text field to scientific notation
Replies
9
Views
423
cheekybuddha
cheekybuddha
M
Append Access Database with Excel Table, While Excel is open and Access closed...
Replies
5
Views
220
CJ_London
C

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom