Prompt Export Save Location (1 Viewer)

Switch

Registered User.
Local time
Today, 09:42
Joined
Feb 16, 2012
Messages
16
Hi there, i have an export to txt file on my form which uses the following code (sorry yet to hit post code to do it properly) :(
Code:
Private Sub redlineBtn_Click()
On Error GoTo Err_redlineBtn_Click
    Dim jobID As Long
    
    jobID = Me.jobNumber
    DoCmd.OpenReport "rpt_redlineText", acViewPreview, , "jobNumber = " & jobID
    Reports!rpt_redlineText.Visible = False
    DoCmd.OutputTo acReport, "rpt_redlineText", acFormatTXT, siteName & " (" & siteID & ") " & "Redline Log.txt", True, "", 0
    Beep
    MsgBox "Export Complete!", vbInformation, "Complete"
    DoCmd.Close acReport, "rpt_redlineText"

Exit_redlineBtn_Click:
    Exit Sub

Err_redlineBtn_Click:
    MsgBox Err.Description
    Resume Exit_redlineBtn_Click
End Sub

This does work however it defaults to the desktop - i can't set a static location as that location will vary from record to record

Any ideas on how to get it to prompt for a save location instead??

Thanks in advance
 
Last edited by a moderator:

Switch

Registered User.
Local time
Today, 09:42
Joined
Feb 16, 2012
Messages
16
Seems to be what i'm looking for...any ideas on how to use it in my code?
 

Switch

Registered User.
Local time
Today, 09:42
Joined
Feb 16, 2012
Messages
16
Tried using it, how i thought it should be done but i get the error
"Method 'FileDialog' of object '_Application' failed"
 

Switch

Registered User.
Local time
Today, 09:42
Joined
Feb 16, 2012
Messages
16
This is my code with the error
Code:
Private Sub redlineBtn_Click()

On Error GoTo Err_redlineBtn_Click

    
    Dim jobID As Long
    jobID = Me.jobNumber
    DoCmd.OpenReport "rpt_redlineText", acViewPreview, , "jobNumber = " & jobID
    Reports!rpt_redlineText.Visible = False
    DoCmd.OutputTo acReport, "rpt_redlineText", acFormatTXT, Application.FileDialog(msoFileDialogSaveAs).InitialFileName = siteName & " (" & siteID & ") " & "Redline Log.txt", True, "", 0
    Beep
    MsgBox "Export Complete!", vbInformation, "Complete"
    DoCmd.Close acReport, "rpt_redlineText"

Exit_redlineBtn_Click:
    Exit Sub

Err_redlineBtn_Click:
    MsgBox Err.Description
    Resume Exit_redlineBtn_Click
    

End Sub
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 05:42
Joined
Oct 17, 2012
Messages
3,276
Actually, before we go any further, one question: Which version of Access are you using? The SaveAs file dialog object isn't supported in 2007 and earlier.
 

Switch

Registered User.
Local time
Today, 09:42
Joined
Feb 16, 2012
Messages
16
I'm using 2010

With my code as it is currently

Code:
Private Sub redlineBtn_Click()

On Error GoTo Err_redlineBtn_Click

    
    Dim jobID As Long
    jobID = Me.jobNumber
    DoCmd.OpenReport "rpt_redlineText", acViewPreview, , "jobNumber = " & jobID
    Reports!rpt_redlineText.Visible = False
    DoCmd.OutputTo acReport, "rpt_redlineText", acFormatTXT, Application.FileDialog(msoFileDialogSaveAs).InitialFileName = siteName & " (" & siteID & ") " & "Redline Log.txt", True, "", 0, acExportQualityPrint
    Beep
    MsgBox "Export Complete!", vbInformation, "Complete"
    DoCmd.Close acReport, "rpt_redlineText"

Exit_redlineBtn_Click:
    Exit Sub

Err_redlineBtn_Click:
    MsgBox Err.Description
    Resume Exit_redlineBtn_Click
    

End Sub

It's not asking me for a save location (defaulting to the desktop) and also using the file name of "0" it's also missing the .txt
 
Last edited:

Solo712

Registered User.
Local time
Today, 05:42
Joined
Oct 19, 2012
Messages
828
Hi there, i have an export to txt file on my form which uses the following code (sorry yet to hit post code to do it properly) :(
Code:
Private Sub redlineBtn_Click()
On Error GoTo Err_redlineBtn_Click
    Dim jobID As Long
 
    jobID = Me.jobNumber
    DoCmd.OpenReport "rpt_redlineText", acViewPreview, , "jobNumber = " & jobID
    Reports!rpt_redlineText.Visible = False
    DoCmd.OutputTo acReport, "rpt_redlineText", acFormatTXT, siteName & " (" & siteID & ") " & "Redline Log.txt", True, "", 0
    Beep
    MsgBox "Export Complete!", vbInformation, "Complete"
    DoCmd.Close acReport, "rpt_redlineText"
 
Exit_redlineBtn_Click:
    Exit Sub
 
Err_redlineBtn_Click:
    MsgBox Err.Description
    Resume Exit_redlineBtn_Click
End Sub

This does work however it defaults to the desktop - i can't set a static location as that location will vary from record to record

Any ideas on how to get it to prompt for a save location instead??

Thanks in advance

Hi Switch,
have you checked what the 4th parameter of the "DoCmd OutputTo" looks like, ie the siteName & " (" & siteID & ") " & "Redline Log.txt" ? It does not look like format of a full path. Put the expression in italics into a watch and check via break point just before it executes.

Best,
Jiri
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 05:42
Joined
Oct 17, 2012
Messages
3,276
Sorry, I got really busy at work. I'll respond better once I'm home, but basically, you create a dialog object, set a few parameters, have it query the user via the .Show method, and have it return the path for the file to be saved. Then use DoCmd.OutputTo to save it.

If you want, google "Access VBA File Dialog Save As" and you should get some examples.
 

Switch

Registered User.
Local time
Today, 09:42
Joined
Feb 16, 2012
Messages
16
Hi Switch,
have you checked what the 4th parameter of the "DoCmd OutputTo" looks like, ie the siteName & " (" & siteID & ") " & "Redline Log.txt" ? It does not look like format of a full path. Put the expression in italics into a watch and check via break point just before it executes.

Best,
Jiri

The code
Code:
siteName & " (" & siteID & ") " & "Redline Log.txt"
basically gives the file name as shown in the attached pic, it just defaults to a unusable location for everyone, and there's to many variables to use a fixed one
 

Attachments

  • Capture.PNG
    Capture.PNG
    18.2 KB · Views: 189

Switch

Registered User.
Local time
Today, 09:42
Joined
Feb 16, 2012
Messages
16
Sorry, I got really busy at work. I'll respond better once I'm home, but basically, you create a dialog object, set a few parameters, have it query the user via the .Show method, and have it return the path for the file to be saved. Then use DoCmd.OutputTo to save it.

If you want, google "Access VBA File Dialog Save As" and you should get some examples.

Almost there it seems with the code below

Code:
Private Sub redlineBtn_Click()

On Error GoTo Err_redlineBtn_Click

    Dim intChoice As Integer
    Dim jobID As Long
    jobID = Me.jobNumber
    DoCmd.OpenReport "rpt_redlineText", acViewPreview, , "jobNumber = " & jobID
    Reports!rpt_redlineText.Visible = False
    Application.FileDialog(msoFileDialogSaveAs).InitialFileName = siteName & " (" & siteID & ") " & "Redline Log.txt"
    intChoice = Application.FileDialog(msoFileDialogSaveAs).Show
    If intChoice <> 0 Then
    DoCmd.OutputTo acReport, "rpt_redlineText", acFormatTXT, , True, "", 0, acExportQualityPrint
    Beep
    MsgBox "Export Complete!", vbInformation, "Complete"
    DoCmd.Close acReport, "rpt_redlineText"

Exit_redlineBtn_Click:
    Exit Sub

Err_redlineBtn_Click:
    MsgBox Err.Description
    Resume Exit_redlineBtn_Click
    
End If
End Sub

It opens the Save As Dialog, which is brilliant, however it's dropping the .txt from the file name and i only have "All Files" listed in the drop down, any way around this?
 

Switch

Registered User.
Local time
Today, 09:42
Joined
Feb 16, 2012
Messages
16
Solved :D

Don't really know how i've done it but my end code is this
Code:
Private Sub redlineBtn_Click()

On Error GoTo Err_redlineBtn_Click

    Dim intChoice As String
    Dim jobID As Long
    Dim FileName As String
    jobID = Me.jobNumber
    FileName = siteName & " (" & siteID & ") " & "Redline Log.txt"
    DoCmd.OpenReport "rpt_redlineText", acViewPreview, , "jobNumber = " & jobID
    Reports!rpt_redlineText.Visible = False
    Application.FileDialog(msoFileDialogSaveAs).InitialFileName = FileName
    intChoice = Application.FileDialog(msoFileDialogSaveAs).Show
    If intChoice Then
    DoCmd.OutputTo acReport, "rpt_redlineText", acFormatTXT, FileName, True, "", , acExportQualityPrint
    'siteName & " (" & siteID & ") " & "Redline Log.txt"
    Beep
    MsgBox "Export Complete!", vbInformation, "Complete"
    DoCmd.Close acReport, "rpt_redlineText"

Exit_redlineBtn_Click:
    Exit Sub

Err_redlineBtn_Click:
    MsgBox Err.Description
    Resume Exit_redlineBtn_Click
    
End If

End Sub

This gives me the file name structure i require, lets the user choose the save location & enforces the file type in the name
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 05:42
Joined
Oct 17, 2012
Messages
3,276
Glad to see you got your code working!

Sorry I didn't reply yesterday - family drama - but here's a function I keep in my code collection at home. Basically, it has three optional parameters: Default folder location, default file name, and default title bar, and it returns a string containing either the word CANCEL or the full path for the save location. Then you can just output to that path.
Code:
Public Function SaveFile(Optional ByVal DefaultFolder As String, _
                         Optional ByVal FileName As String, _
                         Optional ByVal SavePrompt As String = "Enter file name and location...") As String

On Error GoTo SaveFile_Err

Dim fdo As FileDialog
Dim SavePath As String

    If DefaultFolder = "" Then
        Dim WshShell As Object          [COLOR="SeaGreen"]'Shell object to allow use of Windows Script Host Shell[/COLOR]
                
       [COLOR="SeaGreen"] 'Determine location of user's 'My Documents' folder.[/COLOR]
        Set WshShell = CreateObject("WScript.Shell")
        SavePath = WshShell.SpecialFolders("MyDocuments")
        
        [COLOR="SeaGreen"]'Note - the Microsoft Scripting Runtime reference must be active for the above section to work.
        'Another option is to use Environ$("USERPROFILE") and then concantenate on either "\Documents\" or "\My Documents\", depending
            'on which version of windows is being used.[/COLOR]
    End If
    
    If Right(DefaultFolder, 1) <> "\" Then
        SavePath = DefaultFolder & "\"
    End If
    
    SavePath = SavePath & FileName
    
    Set fdo = Application.FileDialog(msoFileDialogSaveAs)
    
    With fdo
        .InitialFileName = SavePath
        .AllowMultiSelect = False
        .Title = SavePrompt
        
        If .Show = True Then
            SaveFile = .SelectedItems(1)
        Else
            SaveFile = "CANCEL"
        End If
    End With
    
SaveFile_Exit:
    On Error Resume Next
    If Not fdo Is Nothing Then Set fdo = Nothing
    If Not WshShell Is Nothing Then Set WshShell = Nothing
    Exit Function
    
SaveFile_Err:
    MsgBox "There was an error in procedure SaveFile!" & vbCrLf & vbCrLf & _
           "Error Number:" & vbTab & Err.Number & vbCrLf & _
           "Error Description:" & vbTab & Err.Description, vbCritical
    Resume SaveFile_Exit

End Function
 

Users who are viewing this thread

Top Bottom