Help withExcel VBA code

[Maclain]

Good afternoon,

Apologies for this being excel and not access, I was hoping someone could point me in the right direction.

I'm trying to break up a save and create pdf module to check for the existence of the file it's about to create. I've managed to get a cell to display TRUE or FALSE if the file exists or not.

Now i'm trying to add in an If statement in the VBA to display a msg box saying the file exists. I'm not very confident with VBA, but I've fumbled around a little:

Sub SAVEREPORTANDPDF()

Dim FileName As String
Dim Path As String
Dim filecheckl As String

filecheck1 = Range("AU11").Value 'Sets the value of FileCheck1

'Checks the value of File exists in the form
If filecheck1 = "true" Then MsgBox "File Already Exists"
Else

'save the excel file
Path = "R:\" '
FileName = Range("AU10").Value & ".xlsm"
ActiveWorkbook.SaveAs Path & FileName, xlOpenXMLWorkbookMacroEnabled

'now create the pdf
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=Range("AU15").Value _
    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=True
    
End If
 
End Sub

 

[arnelgp]

you got it already.

you can also use the Dir function to test if the file already exists without needing the boolean cell:


Path = "R:\"
FileName = Range("AU10").Value & ".xlsm"

If Dir(Path & FileName) <> "" 'file exist
' msgbox here
Else
' save the workbook
' import as pdf
End If

 

[Maclain]

It throws me an else without if error:

Sub SAVEREPORTANDPDF()

Dim FileName As String
Dim Path As String

Path = "R:\"
FileName = Range("AU10").Value & ".xlsm"

If Dir(Path & FileName) <> "" Then MsgBox "File Exists"

[COLOR="Red"]Else[/COLOR]

'save the excel file
Path = "R:\" '
FileName = Range("AU10").Value & ".xlsm"
ActiveWorkbook.SaveAs Path & FileName, xlOpenXMLWorkbookMacroEnabled

'now create the pdf
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=Range("AU15").Value _
    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=True
    
End If
 

End Sub

 

[arnelgp]

move the Msgbox down on its on line:

If Dir(path & filename) <> "" then
Msgbox ....

Else
....
End If

 

[Grumm]

You cannot mix block syntax and one line syntax :
https://msdn.microsoft.com/en-us/library/office/gg251599.aspx

When the there are more than one line between the if else it is best to fully use the blocks

 

[Maclain]

tidied up code if any use to anyone in the future:

Sub SAVEREPORTANDPDF()

Dim FileName As String
Dim Path As String

Path = "R:\"
FileName = Range("AU10").Value & ".xlsm" ' sets the filename to the value of cell AU10 and adds the extension


If Dir(Path & FileName) <> "" Then 'checks for existance of file you are about to create
MsgBox "File already exists. Please use correct revision number or investigate duplicate file." 'warns user file already exists

Else

'start to save the excel file
Path = "R:\" '
FileName = Range("AU10").Value & ".xlsm" 'sets the filename as the value of cell AU10 and sets the extension
ActiveWorkbook.SaveAs Path & FileName, xlOpenXMLWorkbookMacroEnabled 'selects the file format

'now create the pdf. the Filename range selects the cell contents as the filename, note this is an absolute path and extension in this case.
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=Range("AU15").Value, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
    
End If
End Sub

 

[arnelgp]

you can further trim the code by removing redundant variables.
i commented ('\\), those code candidate for deletion.

Sub SAVEREPORTANDPDF()

Dim FileName As String
Dim Path As String

Path = "R:\"
FileName = Path & Range("AU10").Value & ".xlsm" ' sets the filename to the value of cell AU10 and adds the extension


If Dir(FileName) <> "" Then 'checks for existance of file you are about to create
MsgBox "File already exists. Please use correct revision number or investigate duplicate file." 'warns user file already exists

Else

'start to save the excel file
'\\Path = "R:\" '
'\\FileName = Range("AU10").Value & ".xlsm" 'sets the filename as the value of cell AU10 and sets the extension
'\\ActiveWorkbook.SaveAs Path & FileName, xlOpenXMLWorkbookMacroEnabled 'selects the file format
ActiveWorkbook.SaveAs FileName, xlOpenXMLWorkbookMacroEnabled 'selects the file format

'now create the pdf. the Filename range selects the cell contents as the filename, note this is an absolute path and extension in this case.
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=Range("AU15").Value, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
    
End If
End Sub

 

[Maclain]

Thanks for this guys.

I'm now trying to work in a handler to overwrite an existing file based on the Ignore button being pressed only now it wont save unless the file exists...

Sub SAVEREPORTANDPDF()

Dim FileName As String
Dim Path As String
Dim iret As Integer

Path = "R:\"
FileName = Range("AU10").Value & ".xlsm" ' sets the filename to the value of cell AU10 and adds the extension

If Dir(Path & FileName) <> "" Then 'checks for existance of file you are about to create
iret = MsgBox("This file already exists. Please use correct revision number or check for duplicates. Click Ignore to overwrite the existing file.", vbAbortRetryIgnore) 'warns user file already exists

ElseIf iret = "5" Then 'processes the ignore and overwrite

'start to save the excel file
Path = "R:\" '
FileName = Range("AU10").Value & ".xlsm" 'sets the filename as the value of cell AU10 and sets the extension
ActiveWorkbook.SaveAs Path & FileName, xlOpenXMLWorkbookMacroEnabled 'selects the file format

'now create the pdf. the Filename range selects the cell contents as the filename, note this is an absolute path and extension in this case.
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=Range("AU15").Value, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

Else
End If
End Sub

 

[Maclain]

I cant get my head around these if statements..

If the file doesn't exist it doesn't get past checking for the file existence.

If it does, it throws me the message box but doesnt continue with the file creation when i click ignore.

I'm sure this is messy, but works:

Sub SAVEREPORTANDPDF()

Dim filename As String
Dim path As String
Dim Cust As String
Dim pdfpath As String
Dim Iret As Integer

path = "R:\"
filename = Range("AU10").Value & ".xlsm" ' sets the filename to the value of cell AU10 and adds the extension
Cust = Range("B11").Value
pdfpath = "X:\"

If Len(Dir(pdfpath & Cust, vbDirectory)) = 0 Then 'create directory for new customers
MkDir (pdfpath & Cust)
End If

If Dir(path & filename) = "" Then 'checks for existance of file you are about to create
Call saveexcelcreatepdf
Exit Sub
Else
Iret = MsgBox("This file already exists. Please use correct revision number or check for duplicates. Click Ignore to overwrite the existing file.", vbAbortRetryIgnore + vbInformation) 'warns user file already exists
If Iret <> "5" Then 'processes user input
Exit Sub
Else
Call saveexcelcreatepdf

End If
End If
End Sub

Sub saveexcelcreatepdf()
Dim path As String
Dim filename As String

path = "R:\"
filename = Range("AU10").Value & ".xlsm" 'sets the filename as the value of cell AU10 and sets the extension
ActiveWorkbook.SaveAs path & filename, xlOpenXMLWorkbookMacroEnabled 'selects the file format

'now create the pdf. the Filename range selects the cell contents as the filename, note this is an absolute path and extension in this case.
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:=Range("AU15").Value, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

End Sub