Excel Save as

[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

 

[Gasman]

Well if you Cancel, you will not have a file to export to?, so you will need to test you have a filename?

You are not supplying an initial folder, never mind an initial filename?

So pass the full pathname of the file you want to start with as the first parameter.

 

[Gismo]

Well if you Cancel, you will not have a file to export to?, so you will need to test you have a filename?

You are not supplying an initial folder, never mind an initial filename?

So pass the full pathname of the file you want to start with as the first parameter.

Are you referring to "C:\Temp\" ?

strFileName = strGetFileFolderName("C:\Temp\", 2, "Excel")

Is the point of file save as not to select a new location?

I dont know how to pass the full pathname

It seems to work fine when I save
Still no auto fill fine name unfortunately

 

[Gasman]

You supply NOTHING! to the function, so how is Access meant to know where you want to be?

Do you ever try anything yourself to see what works and what does not?

I have just used a file name, no folder path with it, just a filename as the first parameter, and it opens in My Documents folder with the name of the filename I supplied in the parameter?

You still need to decide what to do when no filename is supplied?