Saving a pdf report by selecting a location

Sam Summers

Sam Summers

Registered User.
Local time
Today, 00:37
Joined
Sep 17, 2001
Messages
939
Hi guys,

I am nearly there with this but cant quite get the last bit done.

The two separate sections work i.e. the file location opens and the file save section works well too but I want to allow the user to simply choose the location for the file as named already.

Code: 
Private Sub TOMainPdf_Click()
On Error GoTo TOMainPdf_Click_Err

Dim MyPath As String
Dim MyFilename As String

Dim f As Object
Set f = Application.FileDialog(msoFileDialogFilePicker)
Dim varFile As Variant
f.Show
With f
    .AllowMultiSelect = False
     For Each varFile In .SelectedItems
        MsgBox varFile
     Next varFile
End With

Me.JobNo.SetFocus

MyPath = "C:\Users\Sam Summers\Documents\Work Databases\Hatch\"
MyFilename = Me.JobNo.Text & "-" & "Transocean Main Rpt" & "-" & Day(Now) & "-" & Month(Now) & "-" & Year(Now) & ".pdf"

DoCmd.OutputTo acOutputReport, "TransoceanMainRpt", acFormatPDF, MyPath & MyFilename, False


TOMainPdf_Click_Exit:
    Exit Sub

TOMainPdf_Click_Err:
    MsgBox Error$
    Resume TOMainPdf_Click_Exit

End Sub

Many thanks in advance
 
Code: 
  ''starting here with your code 
MyPath = "C:\Users\Sam Summers\Documents\Work Databases\Hatch\"
vPath = UserSaveDir (myPath)
getDirName vPath, MyPath, f
 MyFilename = Me.JobNo.Text & "-" & "Transocean Main Rpt" & "-" & Day(Now) & "-" & Month(Now) & "-" & Year(Now) & ".pdf"
 DoCmd.OutputTo acOutputReport, "TransoceanMainRpt", acFormatPDF, MyPath & MyFilename, False
 
TOMainPdf_Click_Exit:
    Exit Sub
 TOMainPdf_Click_Err:
    MsgBox Error$
    Resume TOMainPdf_Click_Exit
End Sub
  
 '---------------
Public Function UserSaveDir(Optional pvPath)
'---------------
Dim strTable As String
Dim strFilePath As String
Dim sDialog As String, sDecr  As String, sExt As String
 '===================
'YOU MUST ADD REFERENCE : Microsoft Office xx.0 Object Library, in vbe menu, TOOLS, REFERENCES
'===================
With Application.FileDialog(msoFileDialogSaveAs)
    .AllowMultiSelect = False
    .Title = "Save file"
    .ButtonName = "Save As"
    '.Filters.Clear
    '.Filters.Add "All Files", "*.*"
    .InitialFileName = pvPath
    .InitialView = msoFileDialogViewList    'msoFileDialogViewThumbnail
    
    UserSaveDir= .Show
End With
End Function
 
'given filepath, passes back: Dir name , filename
'---------------
Public Sub getDirName(ByVal psFilePath, ByRef prvDir, Optional ByRef prvFile)
'---------------
    'psFilePath: full file path given
    'prvDir : directory name output
    'prvFile: filename only output
Dim i As Integer, sDir As String
 i = InStrRev(psFilePath, "\")          'not available in '97
If i > 0 Then
  prvDir = Left(psFilePath, i)
  prvFile = Mid(psFilePath, i + 1)
End If
End Sub
 
Hi Ranman,

I have been playing about with it to get it working and it nearly does.

The save file window comes up with the pdf filename but the save as section is 'all files' and when you click on save as, the file is not saved?

Here is the code I have:

Code: 
Private Sub TOMainPdf_Click()
On Error GoTo TOMainPdf_Click_Err

Dim MyPath As String
Dim MyFileName As String
Dim dlgSaveAs As FileDialog
 Dim strFilePath As String
 Dim strFileName As String
 Dim GetSaveFileName As String

Me.JobNo.SetFocus

    Dim Dialog As FileDialog
    Set Dialog = Application.FileDialog(msoFileDialogSaveAs)
    With Dialog
        .InitialFileName = Me.JobNo.Text & "-" & "Transocean Main Rpt" & "-" & Day(Now) & "-" & Month(Now) & "-" & Year(Now) & ".pdf"
        .FilterIndex = 2
        .Title = "Save As"
        If .Show <> 0 Then
            GetSaveFileName = .SelectedItems(1)
        End If
        'DoCmd.OutputTo acOutputReport, "TransoceanSubSectionRpt", acFormatPDF, MyPath & MyFileName, False
    End With
    

TOMainPdf_Click_Exit:
    Exit Sub

TOMainPdf_Click_Err:
    MsgBox Error$
    Resume TOMainPdf_Click_Exit

End Sub
  
 '---------------
Public Function UserSaveDir(Optional pvPath)
'---------------
Dim strTable As String
Dim strFilePath As String
Dim sDialog As String, sDecr  As String, sExt As String

With Application.FileDialog(msoFileDialogSaveAs)
    .AllowMultiSelect = False
    .Title = "Save file"
    .ButtonName = "Save As"
    .InitialFileName = pvPath
    .InitialView = msoFileDialogViewList    'msoFileDialogViewThumbnail
    
    UserSaveDir = .Show
End With
End Function
 
'given filepath, passes back: Dir name , filename
'---------------
Public Sub getDirName(ByVal psFilePath, ByRef prvDir, Optional ByRef prvFile)
'---------------

Dim i As Integer, sDir As String
 i = InStrRev(psFilePath, "\")          'not available in '97
If i > 0 Then
  prvDir = Left(psFilePath, i)
  prvFile = Mid(psFilePath, i + 1)
End If
End Sub
 

Attachments

  • Screenshot_1.png
    Screenshot_1.png
    87.1 KB · Views: 622
the dialog returns the full path/file name the user entered.
but you don't want that,
you want YOUR file, but their path, so when user picks a file , I use
getDirName , to strip out the folder name
then attach your filename to it.
 
I cant get this to work at the moment.

Its beyond my level :)
 
This may sound a bit draconian, but you might be better saving the files in a fixed network location, that is backed up and available for all to see.
You can then store this location is a system table and look it up - so that if you ever need to change it, you only change the location in one place - the system table field holding your file save location.
This also would mean you could check if the report had already been saved by anybody that day and give them the option to review it or overwrite it ?
 
I know what you mean Minty.

This particular little DB will only be installed on individual laptops to be used for an inspection and then once the reports are completed and filed the DB will be emptied ready for the next inspection.

I have already created a pdf save button with a file location hard coded and this works fine but I thought it might be slicker to give someone the option to save somewhere of their choice?

But otherwise I will use my current functioning method which works great
 
Sam

You are getting off the rails when you use msoFileDialogFilePicker. You want to select a folder, not a file.

Try this
function SelectFolder(Optional MyDefaultFolder as string) as string
with application.filedialog(msoFileDialogFolderPicker)
.initialfileName = MyDefaultFolder
.allowmultiselect =False
.Show
.SelectFolder=.selectedItems(1)
.end with
end function
 
Hi Cronk, and thank you to everybody but I couldn't get any of it to work. Nearly but not quite. Its beyond my current ability so I have settled for this and I will have to hard code in the location everytime prior to deploying the DB

Code: 
Private Sub TOMainPdf_Click()
On Error GoTo TOMainPdf_Click_Err

Dim MyPath As String
Dim MyFileName As String

MyPath = "C:\Users\Sam Summers\Documents\Work Databases\Hatch\"

 Me.JobNo.SetFocus
 MyFileName = Me.JobNo.Text & "-" & "Transocean Main Rpt" & "-" & Day(Now) & "-" & Month(Now) & "-" & Year(Now) & ".pdf"
 DoCmd.OutputTo acOutputReport, "TransoceanMainRpt", acFormatPDF, MyPath & MyFileName, False

TOMainPdf_Click_Exit:
    Exit Sub

TOMainPdf_Click_Err:
    MsgBox Error$
    Resume TOMainPdf_Click_Exit

End Sub
 
If you want to use the database path on the users machine you can get it from

Code: 
    Dim sAppath as String
    sAppPath = CurrentProject.path

Which means you don't need to hard code it.
 
Hi again Minty,

I have continued trying a few things and came across the code below at https://bytes.com/topic/access/answers/886621-output-report-pdf-format-save-send-email

Code: 
Private Sub TOMainPdf_Click()
On Error GoTo TOMainPdf_Click_Err

Dim MyFileName As String
Dim fd As FileDialog
Dim strFolder As String

 Me.JobNo.SetFocus
 MyFileName = Me.JobNo.Text & "-" & "Transocean Main Rpt" & "-" & Day(Now) & "-" & Month(Now) & "-" & Year(Now) & ".pdf"
 
 Set fd = Application.FileDialog(msoFileDialogFolderPicker)

 If fd.Show = -1 Then
     strFolder = fd.SelectedItems.Item(1)
 Else
     Exit Sub
 End If

 DoCmd.OutputTo acOutputReport, "TransoceanMainRpt", acFormatPDF, strFolder & MyFileName, False

 Set fd = Nothing


TOMainPdf_Click_Exit:
    Exit Sub

TOMainPdf_Click_Err:
    MsgBox Error$
    Resume TOMainPdf_Click_Exit

End Sub

Which works well apart from it doesn't save it where I select but it does one folder above and I don't understand the code to figure out why?
 
Sam Summers said:
Which works well apart from it doesn't save it where I select but it does one folder above and I don't understand the code to figure out why?
Click to expand...

What does a Debug.Print of strFolder show you compared with what you clicked on ??
 
I'm not quite sure how to use the Debug.Print but this is what I tried.

What opens up is the folder that has the folder where I want to place it (Hatch) in it, but when I open the Hatch folder and click OK, my pdf is saved in the Work Databases folder?

Code: 
Private Sub TOMainPdf_Click()
On Error GoTo TOMainPdf_Click_Err

Dim MyFileName As String
Dim fd As FileDialog
Dim strFolder As String

 Me.JobNo.SetFocus
 MyFileName = Me.JobNo.Text & "-" & "Transocean Main Rpt" & "-" & Day(Now) & "-" & Month(Now) & "-" & Year(Now) & ".pdf"
 
 Set fd = Application.FileDialog(msoFileDialogFolderPicker)

 If fd.Show = -1 Then
     strFolder = fd.SelectedItems.Item(1)
 Else
     Exit Sub
 End If
Debug.Print strFolder
 DoCmd.OutputTo acOutputReport, "TransoceanMainRpt", acFormatPDF, strFolder & MyFileName, False

 Set fd = Nothing


TOMainPdf_Click_Exit:
    Exit Sub

TOMainPdf_Click_Err:
    MsgBox Error$
    Resume TOMainPdf_Click_Exit

End Sub
 

Attachments

  • Screenshot_1.png
    Screenshot_1.png
    28.4 KB · Views: 323
While your form is open keep the VBA window open you will see in the Immediate window (Press Ctrl G to bring it up) the result of any debug.print statements that are fired.
It is by far the most useful debugging tool - get into the habit of using it to see what your code values actually are !
attachment.php
 

Attachments

  • DebugWindow.jpg
    DebugWindow.jpg
    96.8 KB · Views: 1,318
that's great Minty!

I will use that from now on!

Ok, well strangely enough it displays: C:\Users\Sam Summers\Documents\Work Databases\Hatch

but the file is being saved in C:\Users\Sam Summers\Documents\Work Databases ??
 
Have you tried adding a \ to the path it returns :)
 
Where do I put that in this?

Code: 
Private Sub TOMainPdf_Click()
On Error GoTo TOMainPdf_Click_Err

Dim MyFileName As String
Dim fd As FileDialog
Dim strFolder As String

 Me.JobNo.SetFocus
 MyFileName = Me.JobNo.Text & "-" & "Transocean Main Rpt" & "-" & Day(Now) & "-" & Month(Now) & "-" & Year(Now) & ".pdf"
 
 Set fd = Application.FileDialog(msoFileDialogFolderPicker)

 If fd.Show = -1 Then
     strFolder = fd.SelectedItems.Item(1)
 Else
     Exit Sub
 End If
Debug.Print strFolder
 DoCmd.OutputTo acOutputReport, "TransoceanMainRpt", acFormatPDF, strFolder & MyFileName, False

 Set fd = Nothing


TOMainPdf_Click_Exit:
    Exit Sub

TOMainPdf_Click_Err:
    MsgBox Error$
    Resume TOMainPdf_Click_Exit

End Sub
 
See below in red - and see that I reprint the debug so I can see works.
Code: 
Private Sub TOMainPdf_Click()
On Error GoTo TOMainPdf_Click_Err

Dim MyFileName As String
Dim fd As FileDialog
Dim strFolder As String

 Me.JobNo.SetFocus
 MyFileName = Me.JobNo.Text & "-" & "Transocean Main Rpt" & "-" & Day(Now) & "-" & Month(Now) & "-" & Year(Now) & ".pdf"
 
 Set fd = Application.FileDialog(msoFileDialogFolderPicker)

 If fd.Show = -1 Then
     strFolder = fd.SelectedItems.Item(1)
 Else
     Exit Sub
 End If
 Debug.Print strFolder
[COLOR="Red"] strFolder = strFolder & "\"
 Debug.Print strFolder[/COLOR]
 DoCmd.OutputTo acOutputReport, "TransoceanMainRpt", acFormatPDF, strFolder & MyFileName, False

 Set fd = Nothing


TOMainPdf_Click_Exit:
    Exit Sub

TOMainPdf_Click_Err:
    MsgBox Error$
    Resume TOMainPdf_Click_Exit

End Sub
 
You must log in or register to reply here.

Similar threads

A
Combining multiple reports into a PDF export
Replies
2
Views
363
KitaYama
K
essaytee
Why messagebox appears then closes on successful run of DoCmd.OutputTo....pdf
Replies
5
Views
224
Mark_
M
D
Solved File exist button in continous form
2
Replies
26
Views
668
dk_bhn
D
K
How can I add this code to the storage location of my file before scanning and then convert it to a PDF file?
2
Replies
25
Views
999
The_Doc_Man
The_Doc_Man
oxicottin
Solved Import from excel and delete unwanted data
Replies
14
Views
555
oxicottin
oxicottin

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom