Creating a 'Save As' dialog to save a query as an Excel file 1

[emik]

Hi guys, this is a tricky one I haven't been able to figure out for some time.

Here is the whole process of what I am trying to do:

-The user clicks "Export"
-The program creates a new folder with a field as the title
-The Save As is defaulted to this new folder
-The Save As filename is defaulted to a certain variable
-If the file exists prompt the user to overwrite

It would be nothing short of a miracle if someone were able to help me with this. Thank you.

 

[abaldwin]

Can you show the code that you have so far?
It would be helpfull to show us what you have and where it is failing.

Andy Baldwin

"Testing is the most overlooked programming language on the books!"

Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

 

[emik]

Hi abaldwin,

At this point I haven't written any code I just have the requirements. I'm going to try coding this today but I remember in the past I was never able to accomplish this. I was hoping someone could lead me in the right direction.

Most of the programming I do is creating recordsets to manipulate the data and functionality on forms, nothing too complicated. This is a little past my level so I'm just doing searches online and in forums.

If/when I get some code written I'll post it.

 

[emik]

Ok I got it working (almost perfected). This is more just to show anyone who might be trying to do this, I'll be coding this throughout the day and should have something solid by then.

For now I'm using this method:

Dim xlApplication As Excel.Application
Set xlApplication = New Excel.Application

strFileName = xlApplication.GetSaveAsFilename(FileName)

DoCmd.OutputTo acOutputQuery, Subject, acFormatXLS, strFileName, False

in the sub I'll be passing the values I need (right now the variables are mixed up because I just did it).

I'm thrilled right now.

 

[JerryKlmns]

Use

* MkDir statement to create a directory or folder. If the drive is not specified, the new directory or folder is created on the current drive.

i.e MkDir strFullPath & strFieldName

*Dir Function which returns a String representing the name of a file, directory, or folder that matches a specified pattern or file attribute, or the volume label of a drive.

i.e
If Dir(strFullPath & strFieldName & strFileName)<>"" Then
If MsgBox("File already exist. Overwrite it ?",vbYesNo, "Exporting File " & strFileName)=vbNo Then Exit Sub
End If
Kill strFullPath & strFieldName & strFileName
End If

You should also check for the folder if it exists or not.

*TransferSpreadsheet method to export a query
i.e
DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel9, "", strFullPath & strFieldName & strFileName,True

The way you are going
Dim xlApplication As Excel.Application
Set xlApplication = New Excel.Application

is automation and you 'll have to control what, where and how you pass data to excel and also exterminate excel succesfully!

Happy coding

 

[emik]

I'm having a problem getting the full path without the file name.

Since the user can change the filename and path I need to get those values after they've made their selection.

So I need the path eg: C:\Program Files
The path + folder name eg: C:\Program Files\Test

This line returns me the full path + filename. Is there a way I can get the filepath without the name (so I can test if my folder exists and then either create it or save the file)?

strFileName = xlApplication.GetSaveAsFilename(DefaultValue)

 

[JerryKlmns]

You could use

* the Split function with this delimeter \ and the last element of the returned array is the file name. Set that to an empty string( ---> "") and then use the Join function to rebuild it without the filename.

* the InStrRev function to get the position of this character \ and the use the Left function along with the Len function

 

[emik]

I'm trying to ask as few questions as possible, but I'm not familiar with the Split function (I figure it would be more straight forward then searching for the \ since there will be more than 1).

I checked around and this is as far as I got:

Dim FileParts () as string
FileParts = Split(FileName, "\")
Get_file_name = UBound(FileParts)

now that works but how can I rebuild the string after? The problem I'm getting is out of range so I tried this

For i = 0 to UBound(FileParts)
path = path + FileParts(i)
Next

But no luck yet. Thank you for all your help so far.

 

[emik]

Using the wrong variable, sorry I got it

 

[emik]

Ok I think I've finished. I'll paste the core operations. Let me know if you see any key issues that need to be fixed. I'm now going to try to default the file type to always be Excel and start error trapping.

'Open the Save as dialog for the user to select the filename and location
FileName = xlApplication.GetSaveAsFilename(ExportFileName)

'Use the split function to get the file path the user entered (without filename)
Dim sParts() As String
sParts = Split(FileName, "\")

'Loop through our array and take everything except the last element (which is our filename)
For i = 0 To UBound(sParts) - 1
'For the first element we do not want to include the \
If i = 0 Then
new_string = sParts(i)
Else
new_string = new_string + "\" + sParts(i)
End If
Next

'name of the file
FileName = sParts(UBound(sParts))
'final path without file
NewFolderPath = new_string & "\" & FolderAsOf
'Final path with file
FinalPathFile = NewFolderPath & "\" & FileName

'Check if the folder exists.
If Dir(NewFolderPath, vbDirectory) <> "" Then
'The folder exists, check the filename
If Dir(FinalPathFile) <> "" Then
'The file name exists, prompt user to overwrite
If MsgBox("File already exist. Overwrite it ?", vbYesNo, "Exporting File " & UBound(sParts)) = vbNo Then
Exit Sub
'Overwrite file
End If
End If
Else
'The path does not exist, so we need to create the new directory to save the file
MkDir (NewFolderPath)
End If

DoCmd.OutputTo acOutputQuery, ExportFile, acFormatXLS, FinalPathFile, False

 

[PHV]

I'm now going to try to default the file type to always be Excel
Dim FileName As Variant
FileName = xlApplication.GetSaveAsFilename(ExportFileName, "Excel Files (*.xls), *.xls")
If FileName = False Then Exit Sub
i = InStrRev(FileName, "\")
NewFolderPath = Left(FileName, i) & FolderAsOf
FinalPathFile = NewFolderPath & Mid(FileName, i)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886