OutputTo Select Directory name 1

[sterlecki]

I would like to output a report as a snapshot. I would like the file name to be given by the underlying form controls.
The problem that I'm having is when the DoCmd.OutputTo
is configured to include a file name the popup screen that allows you to select the path does not appear.

Is it possible to have that box appear let the user select the directory path but have the file name already configured?

Dim stDocName As String
Dim API As String
Dim WellName As String

API = Forms![frmmstrHeader]![API]
WellName = Forms![frmmstrHeader]![WellName]
stDocName = "rptHeader"
DoCmd.OutputTo acReport, stDocName, acFormatSNP, API & "_" & WellName & ".snp"

I don't want to hard code the path as each user may store the files in their own particular directory.

 

[PHV]

I use the following function:
Function PickFolder(strStartDir As Variant) As String
Dim SA As Object, f As Object
Set SA = CreateObject("Shell.Application")
Set f = SA.BrowseForFolder(0, "Choose a folder", 0, strStartDir)
If (Not f Is Nothing) Then
PickFolder = f.Items.Item.path
End If
Set f = Nothing
Set SA = Nothing
End Function

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

 

[sterlecki]

PHV,

I have not used functions before. I copied your code to a module or should it stay in the module with my other code? Would I then put some code such as:

Call PickFolder
Just before the docmd.outputto statement???

API = Forms![frmmstrHeader]![API]
WellName = Forms![frmmstrHeader]![WellName]
stDocName = "rptHeader"
Call PickFolder
DoCmd.OutputTo acReport, stDocName, acFormatSNP, API & "_" & WellName & ".snp

sorry it's new terrritory for me

 

[PHV]

strDirectory = PickFolder("")

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

 

[sterlecki]

Thanks! This worked fine and I learned something.

Working Code:

Private Sub cmdReport_toFile_Click()
On Error GoTo Err_cmdReport_toFile_Click

Dim stDocName As String
Dim API As String
Dim WellName As String

API = Forms![frmmstrHeader]![API]
WellName = Forms![frmmstrHeader]![WellName]
stDocName = "rptHeader"
strDirectory = PickFolder("")
DoCmd.OutputTo acReport, stDocName, acFormatSNP, API & "_" & WellName & ".snp"
Exit_cmdReport_toFile_Click:
Exit Sub

Err_cmdReport_toFile_Click:
MsgBox Err.Description
Resume Exit_cmdReport_toFile_Click

End Sub

Another Tek-Tips Success Story

 

[PHV]

I'd replace this:
DoCmd.OutputTo acReport, stDocName, acFormatSNP, API & "_" & WellName & ".snp"
By this:
DoCmd.OutputTo acReport, stDocName, acFormatSNP, strDirectory & "\" & API & "_" & WellName & ".snp"

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

 

[sterlecki]

Yes! This works even better.

thanks