VBA to Create Floder and Save report

[tomtom1126]

Dear All,

Please help !

Form A
Form Main Manual
Report A

Form A (field "seal no") or Table A (field "seal no")

I want to crate the the button in (Form Main manual) to :

1. If folder name (using the data by "Seal no") not exist, Create it.
2. Save the "Report A" into the "Seal no" just craeted.

Thanks
Tom

 

[tomtom1126]

Sorry one more step :

3. send e-mail with the report attach.

thanks
Tom

 

[Minty]

What have you tried so far - it's difficult to offer assistance without seeing your existing attempt...

 

[Grumm]

You can use this to check if the folder exists :

sUploadPath = CurrentProject.path & FormA.Seal_no //or any patch you want to use here.
    
    If Dir(sUploadPath, vbDirectory) = "" Then
        MkDir sUploadPath
    End If

This will just create the folder if it does not exists.

 

[arnelgp]

sUploadPath = CurrentProject.path & "\" & FormA.Seal_no //or any patch you want to use here.

 

[tomtom1126]

Thanks Grumm & Arnelgp

How about the report save into the created new folder and the repot as attach into the email.

Flanks for your help

Tom

 

[tomtom1126]

Dear All,

Below No luck for me.
sUploadPath = CurrentProject.path & "\" & FormA.Seal_no //or any patch you want to use here.

I try this code, it's work to create the seal_no folder but it can work only on the Form "A" but not working on the From "Main Manual".

Others, I need to save the report into this new folder, can anyone help please.

Const strParent = "d:\test\"
Dim strStudentID As String
Dim strFolder As String
Dim fso As Object
' Get student ID from control
strStudentID = Me.seal_no
' Full path
strFolder = strParent & strStudentID
' Create FileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject")
' Check whether folder exists
If fso.FolderExists(strFolder) = False Then
' If not, create it
fso.CreateFolder strFolder
End If

Regards
Tom

 

[Grumm]

To save your report you can use this :

DoCmd.OutputTo acOutputReport, "ReportNAme", acFormatPDF, strFolder & strReportName, False

This will save a PDF version of the file. The 'False' can be changed to 'True' if you want that the file is opened after the save.

PS : sorry for that mistake arnelgp. I copied this code from my project and there i need to create that folder at the same level of the currentProject path.

 

[tomtom1126]

Dear Grumm,

Thanks for your advise, I figure it out by the below code to crate the new folder and save the report into the folder.

But, it can only work on the Form "A" but not working on the Form "Manual" due to the seal_no is control by Form "A" but not Form "Manual". so can you help to fix it ? by the way how to send those report by using outlook email as attach?

Thanks so much!

Const strParent = "d:\test\"
Dim strStudentID As String
Dim strFolder As String
Dim fso As Object
' Get student ID from control
strStudentID = Me.seal_no
' Full path
strFolder = strParent & strStudentID
' Create FileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject")
' Check whether folder exists
If fso.FolderExists(strFolder) = False Then
' If not, create it
fso.CreateFolder strFolder

'DoCmd.OutputTo acOutputReport, "rptPreviewMain", "PDF Format (*.pdf)", "d:\bbo-cy\access test\VVD file\" & seal_no & ".pdf", False
DoCmd.OutputTo acOutputReport, "rptPreViewMain", "PDF Format (*.pdf)", "d:\bbo-cy\access test\VVD file\" & Me.seal_no & "\" & vvd & "_Main" & ".pdf", False
DoCmd.OutputTo acOutputReport, "rptPreViewCostHKG", "PDF Format (*.pdf)", "d:\bbo-cy\access test\VVD file\" & Me.seal_no & "\" & vvd & "_CostHKG" & ".pdf", False
DoCmd.OutputTo acOutputReport, "rptPreViewCostSZP", "PDF Format (*.pdf)", "d:\bbo-cy\access test\VVD file\" & Me.seal_no & "\" & vvd & "_CostSZP" & ".pdf", False
DoCmd.OutputTo acOutputReport, "rptPreViewSpcl", "PDF Format (*.pdf)", "d:\bbo-cy\access test\VVD file\" & Me.seal_no & "\" & vvd & "_Spcl" & ".pdf", False

MsgBox "Floder Create and report save on it"

Else
MsgBox "Floder Already Exist, Please check again"

End If

 

[Grumm]

I don't know where you putted this code. I assume behind a button on the forum "A".
I also noticed that you use to save the file in an other directory than the one you are testing. No idea what or why. So you create a folder and save the date in an other place...
You can use acFormatPDF instead of that string format you use.
Anyway, if you want this code to be used in different forms, make a procedure of it with the StudentID as parameter.
I will post code when I have some spare time.

Sub SaveFile(strStudentID As String)
    Const strParent = "d:\test\"
    Dim strFolder As String
    Dim fso As Object
    ' Get student ID from control
    ' Full path
    strFolder = strParent & strStudentID
    ' Create FileSystemObject
    Set fso = CreateObject("Scripting.FileSystemObject")
    ' Check whether folder exists
    If fso.FolderExists(strFolder) = False Then
        ' If not, create it
        fso.CreateFolder strFolder
        
        'DoCmd.OutputTo acOutputReport, "rptPreviewMain", "PDF Format (*.pdf)", "d:\bbo-cy\access test\VVD file\" & seal_no & ".pdf", False
        DoCmd.OutputTo acOutputReport, "rptPreViewMain", "PDF Format (*.pdf)", "d:\bbo-cy\access test\VVD file\" & strStudentID & "\" & vvd & "_Main" & ".pdf", False
        DoCmd.OutputTo acOutputReport, "rptPreViewCostHKG", "PDF Format (*.pdf)", "d:\bbo-cy\access test\VVD file\" & strStudentID & "\" & vvd & "_CostHKG" & ".pdf", False
        DoCmd.OutputTo acOutputReport, "rptPreViewCostSZP", "PDF Format (*.pdf)", "d:\bbo-cy\access test\VVD file\" & strStudentID & "\" & vvd & "_CostSZP" & ".pdf", False
        DoCmd.OutputTo acOutputReport, "rptPreViewSpcl", "PDF Format (*.pdf)", "d:\bbo-cy\access test\VVD file\" & strStudentID & "\" & vvd & "_Spcl" & ".pdf", False
        
        MsgBox "Floder Create and report save on it"
    
    Else
        MsgBox "Floder Already Exist, Please check again"
    
    End If
End Sub

Something like this.
Then you can use

SaveFile "ID153"

behind any button you want. (The sub can be added in a module.)
I also recommend you to read this : http://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba?rq=1
It explains you that the Dir method is faster than using an object Fso. Both do the same. But the first one is a lot faster. (For me speed is important.)

 

[Minty]

You need to learn to use Google a little bit, it's good that you are now showing us your code though.
Is the Form A open when trying to run this from Form Manual? If so you can get the value from From A still you just need to refer to it correctly. Something like Forms!A!Seal_no

As for the email bit there are numerous threads on here about this here is a starter;
http://www.access-programmers.co.uk/forums/showthread.php?t=214158

 

[tomtom1126]

Dear Minty,

Thanks for your remind, actually I google a lot and the below code is learn fm others suggestion.

For the reefer I fixed it, because the form "Main manual" didn't set record source, now is clear in this part.

By the way, I am the new beginer for VBA, your link sample a bit complicated for me. Sorry about that.

The outstanding not fix for me is the reports send out by outlook email (total 4) attachment. Below code work good but only one report attach.

How I can add the others as attach in one email?

DoCmd.SendObject acSendReport, "rptPreviewMain", acFormatPDF,

Thanks again!

Tom

 

[Minty]

Tom - I understand you are a beginner, however the reason for giving you that link is so that you can add multiple attachments to the same email. You can't achieve this using the SendObject method.

The Outlook Object method shown in the link is slightly more complicated but persevere with it and you will have a valuable tool at your disposal. It allows you to specify many more parameters than is possible with SendObject.