Pick a Directory folder 2

[sterlecki]

thread705-1380756

Using PHV's PickFolder function (see thread above) I've created the following code:

Private Sub cmd_PrintProg_Click()
'Perfoms Print report routines based on the option group selections

On Error GoTo Err_cmd_PrintReport_Click

    Dim stDocName As String
    Dim stDocName2 As String
    Dim varItem As Variant
    Dim strList As String
    Dim stLinkCriteria As String
    Dim intanswer
    Dim FileName As String
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strStartDir As String
    
    
    
    
    With Me.frame_ChooseReport
Select Case [frame_ChooseReport]
         
    
 Case 1
    stDocName = "rpt_WellHeader_Pinedale"
    If Me.lbo_PrintSelectWells.ItemsSelected.Count = 0 Then
    MsgBox ("No Items Selected for Printing")
    Else
    
    Call PickFolder(strStartDir)
    Debug.Print strStartDir
    For Each varItem In Me.lbo_PrintSelectWells.ItemsSelected
    
    
        FileName = Me.lbo_PrintSelectWells.Column(1, varItem) & " " & Me.lbo_PrintSelectWells.Column(2, varItem)
        strList = ",'" & Me.lbo_PrintSelectWells.Column(0, varItem) & "'"
        strList = Mid$(strList, 2)
        stLinkCriteria = "[API] IN (" & strList & ")"
        Debug.Print stLinkCriteria; FileName
        DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
        [highlight]
'DoCmd.OutputTo acReport, stDocName, acFormatPDF, "U:\Pinedale Exchange\PROGS\swt_ProgDump\" & "_" & FileName & ".pdf", False, "" 
[/highlight]
         DoCmd.OutputTo acReport, stDocName, acFormatPDF, strStartDir & " \ " & FileName, False, ""
    
        DoCmd.Close acReport, stDocName, acSavePrompt
        Next varItem
        
 
    End If

When I use the highlighted line directing the output to the U: drive it works fine. The resultant files are placed in that directory and individually named as .pdf files.

When I use the line below which if I understand things correctly is supposed to take the strStartDir path that the user selects and concatenate that with the file name and send the pdf to that directory. Can anyone see what I'm doing wrong?

 

[sterlecki]

Sorry the line of code that doesn't work should read as follows:

[COLOR=red yellow]
DoCmd.OutputTo acReport, stDocName, acFormatPDF, strStartDir & " \ " & FileName & ".pdf", False, ""[/color]

I posted an edited line that didn't work either.

 

[PHV]

Replace this:
Call PickFolder(strStartDir)
with this:
strStartDir = PickFolder(0)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MazeWorX]

check your "\" in your code post there are spaces


HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[sterlecki]

PHV
When I replace
Call PickFolder(strStartDir)
with this:
strStartDir = PickFolder(0) as suggested I get an error
"Object variable or With block variable not set"

MazeWorX
thanks for catching the spaces but it had no positive effect for output. The file is not being created.

could it be that prior to the DoCmd.OutputTo command that I need to create a string variable

Call it strFileDir where

strFileDir = strStartDir & "\" & FileName &".pdf"
then use the statement

DoCmd.OutputTo acReport, stDocName, acFormatPDF, strFileDir, False, ""

However a debug.print returns only \Mesa 10A1-19.pdf
Without a directory path hence no file created.

 

[PHV]

"Object variable or With block variable not set"
Which line of code is highlighted when in debug mode ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sterlecki]

Only and error message box on the form. Nothing highlighted in Debug on the code.

 

[PHV]

Did you try to step your code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sterlecki]

The error occurs upon selection from the "Choose Directory" display.

 

[sterlecki]

When I stepped through it failed just after this statement from your PickFolder function

PickFolder = f.Items.item.Path

 

[PHV]

So, please, post the code of YOUR PickFolder function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sterlecki]

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", 16 + 32 + 64, strStartDir)
If (Not f Is Nothing) Then
  PickFolder = f.Items.item.Path
  'PickFolder = ("My Computer")
  
End If
Set f = Nothing
Set SA = Nothing
End Function

I believe this was your original code from the aforementioned Thread

 

[PHV]

The error occurs upon selection from the "Choose Directory" display
What did you select before pressing the OK button ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

Anyway, I'd use this:
strStartDir = PickFolder([!]17[/!])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sterlecki]

Good News. The following code works with a few caveats:

    With Me.frame_ChooseReport
Select Case [frame_ChooseReport]
         
    
 Case 1
    stDocName = "rpt_WellHeader_Pinedale"
    If Me.lbo_PrintSelectWells.ItemsSelected.Count = 0 Then
    MsgBox ("No Items Selected for Printing")
    Else
    
    'Call PickFolder(strStartDir)  original code changed to
   [Highlight] 
    strStartDir = PickFolder(0)
[/highlight]
    Debug.Print strStartDir
    For Each varItem In Me.lbo_PrintSelectWells.ItemsSelected
    
    
        FileName = Me.lbo_PrintSelectWells.Column(1, varItem) & " " & Me.lbo_PrintSelectWells.Column(2, varItem)
        strList = ",'" & Me.lbo_PrintSelectWells.Column(0, varItem) & "'"
        strList = Mid$(strList, 2)
        stLinkCriteria = "[API] IN (" & strList & ")"
        Debug.Print stLinkCriteria; FileName
        DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
        'DoCmd.OutputTo acReport, stDocName, acFormatPDF, "U:\Pinedale Exchange\PROGS\swt_ProgDump\" & "_" & FileName & ".pdf", False, ""
        
        DoCmd.OutputTo acReport, stDocName, acFormatPDF, strStartDir & "\" & FileName & ".pdf", False, ""
        
    
        DoCmd.Close acReport, stDocName, acSavePrompt
        Next varItem
        
    

    End If

the error: "Object variable or With block variable not set"
only seems to occur if you choose desktop for the file location

Fixes included eliminating spaces in file path and changing to
strStartDir = PickFolder(0) as highlighted
AND DO NOT CHOOSE DeskTop as File Location

thanks PHV

 

[sterlecki]

strStartDir = PickFolder(17)

After making this change and choosing Desktop it saves the file to there as well.

Many thanks Case Closed

 

[sterlecki]

PHV

I have noticed that when the pick folder screen displays for the user to choose the location for the output, should the user change his mind and click cancel the routine continues to run and output the report.

I'm confused as to whether there should be some sort of cancelled event in the "PickFolder function" or in my code.

Your Thoughts?

 

[MazeWorX]

yes you have a couple of choices you could look at the cancel event of the pickfolder() or you could test for data in strStartDir. Really you should check both

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>