How to Specify the Folder, but Prompt for File Name w/ the OutputTo action ?

[kev100 via AccessMonster.com]

I have a Macro set up that will Output a Query to an xls file.

It all works fine except that I need to specify a certain folder, but Prompt
the user for a file name.

The current OutputTO option only seems to have all-or-nothing (leave it
completely blank to be prompted, or enter the entire path And file name).

Thanks

 

[tina]

AFAIK, you can't do it with the OutputTo macro action. but you *can* use the
RunCode action to run a public function that will output the file with a
user-defined name. to set this up:

create a public module and name it modPublicProcedures. paste the following
code into the module, as

Public Function isOutput()

On Error GoTo isOutput_Err

Dim strFile As String

strFile = Trim(InputBox("Enter the file name:"))

CheckString:
If strFile = "" Then
MsgBox "The output action was cancelled."
Exit Function
ElseIf Right(strFile, 4) <> ".xls" Then
strFile = strFile & ".xls"
End If

DoCmd.OutputTo acOutputQuery, "QueryName", _
acFormatXLS, "C:\SomeFolder\" & strFile, True

isOutput_End:
Exit Function

isOutput_Err:
Select Case Err.Number
Case 2302
strFile = Trim(InputBox("The file name cannot contain " _
& "the following characters:" & vbCr _
& " \ / : * ? " & """" & " < > |" & vbCr _
& vbCr & "Please enter a valid file name."))
Resume CheckString
Case Else
MsgBox Err.Number & " " & Err.Description, , _
"Function Error: isOutput()"
Resume isOutput_End
End Select

End Function

substitute the correct query name and the correct filepath in the DoCmd
action, of course. the code creates a complete string whether the user
includes the .xls extension in the file name or not. also, the code
automatically opens the resulting file in Excel. if you don't want to open
Excel, leave off the True argument at the end of the DoCmd action, as

DoCmd.OutputTo acOutputQuery, "QueryName", _
acFormatXLS, "C:\SomeFolder\" & strFile

in your macro, use the RunCode action, and set the Action Arguments to

FunctionName: isOutput()

hth

 

[kev100 via AccessMonster.com]

Thanks very much for that post !

That should work great and may even apply to a few other similar applications
that are needed.

I appreciated the detial and explanation.

Kev

 

[tina]

you're welcome, hope it works for you!