TransferSpreadsheet saving file

[Tiffany]

I am using vba code to save some tables into files.
I have hardcoded the filename that the file will be saved as in case they
type the wrong name. This is what I have used:
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)", _
"*.xls")
strSaveFileName = ahtCommonFileOpenSave(OpenFile:=False, _
Filter:=strFilter, _
Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)
If (strSaveFileName <> "") Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "CW-Proj_Type",
"Central Western", True, "CW-Proj_Type"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "CW-Heavy",
"Central Western", True, "CW-Heavy"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"CW-Passenger", "Central Western", True, "CW-Passenger"
MsgBox "File has been saved.", vbInformation
Else
MsgBox "File has not been saved.", vbCritical
End If

I have used the hardcoding of the filename "Central Western", so even if the
user types in cw, it still saves it as Central Western and no mistakes are
made.
But what I am confused about is that when the file is saved, it saves it
with capital .XLS. I have searched everywhere in the functions it is using
and cannot find anything that is in capitals. It worked out before when I
left it as strSaveFileName, the user typed the filename and it saved ok, with
lower case .xls.

 

[Clifford Bass]

Hi Tiffany,

So, is your question "How to get it to use the lower case .xls?"? If
so, how about including the ".xls" in the file names?

I am puzzled, why even bother with prompting for a file name if you are
not going to use it?

Clifford Bass

 

[Tiffany]

Yes, you are correct, I don't need to prompt for the file name, but I do need
to prompt for the directory to save it to.
I'm not a wizz at vb, so just used some code from another macro.
I tried using .xls in the filename but that didn't work.
I have about 10 files that are being saved, and at the moment, the user is
prompted where to save it, and even if they type in "akdfkjah" it will still
save it as the filename I have coded, but that seems silly I know, I just
don't know how to do it any other way!

 

[Clifford Bass]

Hi Tiffany,

Here is some code that will get you oriented on how to ask for a
directory. It also shows how to check for the presence of a file and the
delete it if needed. And it does save the file name with the lower case .xls
on my machine. If it does not on your machine, it may be tied to some
Windows setting somewhere. In which case, other than looking slightly weird,
I would not worrry. This code requires the Microsoft Scripting Runtime
reference in order to use FileSystemObjects. In the VB Editor: Tools menu,
References.

Public Sub SaveAnExcelDoc()

Dim fd As FileDialog
Dim fso As New FileSystemObject
Dim strFullPath As String

Set fd = FileDialog(msoFileDialogFolderPicker)
With fd
.AllowMultiSelect = False
.ButtonName = "Choose"
.Filters.Clear
.Title = "Please choose the folder in which to store the files."
.InitialView = msoFileDialogViewList
If .Show = True Then
strFullPath = .SelectedItems.Item(1) & "\Birthdays.xls"
If fso.FileExists(strFullPath) Then
fso.DeleteFile strFullPath
End If
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"tblBirthdays", _
strFullPath, False
End If
End With
Set fd = Nothing
Set fso = Nothing

End Sub

Hope that helps,

Clifford Bass

 

[Tiffany]

Should I use this code instead of my code as shown in my first post??

 

[Clifford Bass]

Hi Tiffany,

I don't think the open/save dialog you are using allows for choosing a
directory. Although it might with an appropriate flag added somewhere. And
making your users enter a file name you are going to discard is not really
user-friendly. Since Microsoft is providing another way to get open/save
dialogs, I have been jetisoning most of my use of the option you are using in
favor of Microsoft's option. Most, but not all due to Microsoft's variation
not having one or two features.

As adjusted to your specific needs, the code I provided would be what I
would use in your situation. So, in my opinion, yes.

Clifford Bass

 

[Tiffany]

Hi Clifford,
I'm still slightly confused.
This is my whole code:
Function Save_FileName() As String
Dim strFilter As String
Dim lngFlags As Long
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)", _
"*.xls")
strSaveFileName = ahtCommonFileOpenSave(OpenFile:=False, _
Filter:=strFilter, _
Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)
If (strSaveFileName <> "") Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"CW-Proj_Type", "Central Western", True, "CW-Proj_Type"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"FNC-Proj_Type", "Far North Coast", True, "FNC-Proj_Type"
MsgBox "File has been saved.", vbInformation
Else
MsgBox "File has not been saved.", vbCritical
End If

Where do I put your code??

 

[Clifford Bass]

Hi Tiffany,

Just replace the entire innards (as shown below) of your function with
the innards of mine. Then change the file name from "Birthdays" to your the
first file and change the tbeBirthdays to your first table/query. Make a
copy of the part starting with "strFullPath =..." through the "DoCmd...."
line. And then adjust it for your second file and table/query. That should
do it. This code assumes that you want to replace any existing files. If
that is not the case, it will need some changes, depending on quite what you
want to do. Oh, and you might want to add back in the message about the
exports being successful. That would go before the last End If. I don't
think it necessary to report a lack of saving when the user cancels the
process. If you encounter issues, let me know.

Clifford Bass

 

[Tiffany]

I still feel very silly as I'm not totally sure.
I tried using your code in it's own module, but it didn't work.
It had a Compile error:
user-defined type not defined (with fd As FileDialog selected)
When you say the entire contents of my function, what parts is that??
Is that from strFilter?? to DoCmd??

 

[Clifford Bass]

Hi Tiffany,

Using it in its own module is definitely an option. Then you can call
it instead of your current function. For the entire code I meant the part
that starts with "Dim strFilter As String" and ends with the last "End If"
(that you show in your posting).

Oh, I missed something. To use the FileDialog part you need the
"Microsoft Office xx.x Object Library" added to your references. While in
the VB Editor, goto Tools menu and then References and select it. While
there, if you did not already do so, select the "Microsoft Scripting Runtime"
item also.

Hope that gets you where you need to be.

Clifford Bass

 

[Tiffany]

Finally I have it, thanks so much for your help and patience

 

[Clifford Bass]

Hurrah! You are welcome Tiffany.

Good luck!

Clifford Bass