Save Variable Name report in Variable Name Folder

[setis]

Hi all,



I've been looking into existing threads but I can't figure this out. I'm sure that this will be piece of cake for many of you.



I am trying to save the report into a Folder with the name of the CaseNr.


I can successfully create the folder, but I can't place the file inside it. The file is saved in "Claim Audit\Audit 2017" and it should be in "Claim Audit\Audit 2017\Me.CaseNo"


Thanks a lot in advance!!

Dim FSO As Scripting.FileSystemObject
Set FSO = New Scripting.FileSystemObject

Dim FileName As String
Dim FilePath As String
Dim FolderName As String
Dim FP As String


FolderName = Me.CaseNo
FileName = Me.CaseNo & "-ClaimID" & Me.ClaimID & ".pdf"
FP = "Claim Audit\Audit 2017\"
FSO.CreateFolder (FP & "\" & FolderName)
FilePath = "Claim Audit\Audit 2017\" & FileName

DoCmd.OutputTo acOutputReport, "qryAuditSheetOut", acFormatPDF, FilePath
MsgBox "The Audit Sheet has been saved", vcinformation, "Save Confirmed"

 

[Minty]

You are missing the folder from your final string;

FilePath = "Claim Audit\Audit 2017\"[COLOR="Red"] & FolderName & "\"[/COLOR] & FileName

 

[setis]

You are missing the folder from your final string;

FilePath = "Claim Audit\Audit 2017\"[COLOR=Red] & FolderName & "\"[/COLOR] & FileName

OMG Thank you so much!!

 

[setis]

I am sorry to bother again, but I need to include "if the folder exists, then use it"


But there is obviously something that I'm doing wrong..



The FileName will be unique, the FolderName won't



Please pardon my super amateur level here..

Dim FSO As Scripting.FileSystemObject 
Set FSO = New Scripting.FileSystemObject  

Dim FileName As String 
Dim FilePath As String 
Dim FolderName As String 
Dim FP As String   

FolderName = Me.CaseNo 
FileName = Me.CaseNo & "-ClaimID" & Me.ClaimID & ".pdf" FP = "Claim Audit\Audit 2017"
FP = "Claim Audit\Audit 2017\"

FSO.CreateFolder (FP & "" & FolderName)
 
    If FSO.FolderExists(FolderName) Then
DoCmd.OutputTo acOutputReport, "qryAuditSheetOut", acFormatPDF, FilePath
    End If

FilePath = "Claim Audit\Audit 2017" & FileName  DoCmd.OutputTo acOutputReport, "qryAuditSheetOut", acFormatPDF, FilePath MsgBox "The Audit Sheet has been saved", vcinformation, "Save Confirmed"

 

[Minty]

You are creating the folder before checking for it.
You need to try

   If Not FSO.FolderExists(FolderName) Then
         FSO.CreateFolder (FP & "" & FolderName)
    End If

 

[setis]

You are creating the folder before checking for it.
You need to try

   If Not FSO.FolderExists(FolderName) Then
         FSO.CreateFolder (FP & "" & FolderName)
    End If

It makes sense. However, I am now getting Error 58, the file already exists

 

[Minty]

So is that the end file that already exits?
If so you will need to check for that as well - and maybe give the option to overwrite it? Something like

Dim FSO As Scripting.FileSystemObject 
Set FSO = New Scripting.FileSystemObject  

Dim FileName As String 
Dim FilePath As String 
Dim FolderName As String 
Dim FP As String   

FolderName = Me.CaseNo 
FileName = Me.CaseNo & "-ClaimID" & Me.ClaimID & ".pdf" 
FP = "Claim Audit\Audit 2017\"

If Not FSO.FolderExists( (FP & "" & FolderName) Then

	FSO.CreateFolder (FP & "" & FolderName)
 
End If
FilePath = "Claim Audit\Audit 2017\" &  Foldername & "\" & FileName  

If Len(Dir(FilePath)) > 0 Then
	If MsgBox("This Audit has already been saved." & vbCrLf & "Do you want to overwrite it with an updated copy?" , vbYesNo, "Overwrite Existing File?") = vbYes Then
  		Kill Filepath
	Else
		Exit Sub
	End if
End if	

DoCmd.OutputTo acOutputReport, "qryAuditSheetOut", acFormatPDF, FilePath 

MsgBox "The Audit Sheet has been saved", vcinformation, "Save Confirmed"

 

[setis]

So is that the end file that already exits?
If so you will need to check for that as well - and maybe give the option to overwrite it? Something like

Dim FSO As Scripting.FileSystemObject 
Set FSO = New Scripting.FileSystemObject  

Dim FileName As String 
Dim FilePath As String 
Dim FolderName As String 
Dim FP As String   

FolderName = Me.CaseNo 
FileName = Me.CaseNo & "-ClaimID" & Me.ClaimID & ".pdf" 
FP = "Claim Audit\Audit 2017\"

If Not FSO.FolderExists( (FP & "" & FolderName) Then

    [COLOR=Red]FSO.CreateFolder (FP & "" & FolderName)[/COLOR]
 
End If
FilePath = "Claim Audit\Audit 2017\" &  Foldername & "\" & FileName  

If Len(Dir(FilePath)) > 0 Then
    If MsgBox("This Audit has already been saved." & vbCrLf & "Do you want to overwrite it with an updated copy?" , vbYesNo, "Overwrite Existing File?") = vbYes Then
          Kill Filepath
    Else
        Exit Sub
    End if
End if    

DoCmd.OutputTo acOutputReport, "qryAuditSheetOut", acFormatPDF, FilePath 

 MsgBox "The Audit Sheet has been saved", vcinformation, "Save Confirmed"

Thanks a lot for your help, but I still must have something wrong. Still Error 58.

I deleted the PDF file, but not the folder and I still get the same error, so I believe that it is the already created folder that's not bypassed.



The debug marked the line in red..

 

[Minty]

There is an extra opening bracket in the If line before hand. Maybe my bad.

 

[arnelgp]

you are missing the drive letter on FP variable, eg:

FP = "C:\Claim Audit\Audit 2017"
FP = "D:\Claim Audit\Audit 2017"

 

[setis]

There is an extra opening bracket in the If line before hand. Maybe my bad.

*slow clap* Thank you so much, Sir.