none
IF File eksist then Nothing.. RRS feed

  • Question

  • A great Help from Starain chen WHO have made this macro working

    Help to this Macro The macro save as PDF file from given 4 cells WORKS perfectly....

    but ive need a Function wich stop saving If File Eksist

    Sub saveaspdf()
    Dim FilePath As String
    Dim fileName As String
    Dim fileSaveName As Variant
    Dim cellNames() As String
    Dim item As Variant
     Application.DisplayAlerts = False


         template_file = ActiveWorkbook.FullName
      
         fileSaveName = Application.GetSaveAsFilename( _
          InitialFileName:="C:\temp\E6 and E8 and R6 and R7.pdf", _
          fileFilter:="Pdf Files (*.pdf), *.pdf")
         
          If fileSaveName = False Then
              Exit Sub
          End If
          'remove .pdf
    FilePath = Left(fileSaveName, InStrRev(fileSaveName, ".pdf") - 1)
    'get file name E.g. E6 and E8 and R6 and R7
        fileName = Right(FilePath, Len(FilePath) - InStrRev(FilePath, "\"))
    'based on and to split the file name, the result will be an array with E6,E8,R6,R7 values
        cellNames = Split(fileName, "and")
        Dim Str As String
    'get the correspoding value from cells
        For Each item In cellNames
            Str = Str & Range(item) & Chr(32)
       
        Next
        FilePath = Left(FilePath, InStrRev(FilePath, "\")) & Str
        ActiveWorkbook.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            fileName:=FilePath, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=False
     
        
          file_name_saved = ActiveWorkbook.FullName
          MsgBox "PDF FIL OPRETTET MED SUCCES : " & vbCr & vbCr
          
      
          Application.DisplayAlerts = True

    End Sub


    • Edited by Henrik-1 Friday, September 26, 2014 4:36 AM
    Friday, September 26, 2014 4:32 AM

All replies

  • You have got a quick overview of the Dir() function in here

    And a tutorial on reading *.txt files from VBA - just scroll down to the section that checks if file exists.

    Friday, September 26, 2014 7:04 AM
  • HM HM nice BUT WHERE SHOULD I PUT it in my Macro from Starain chen that's give me pain in head alot...

    Henrik-1

    Friday, September 26, 2014 6:18 PM
  • Try:

    FilePath = Left(FilePath, InStrRev(FilePath, "\")) & Str
    If Dir(FilePath) <> "" Then Exit Sub

    or:

    FilePath = Left(FilePath, InStrRev(FilePath, "\")) & Str
    If Dir(FilePath) = "" Then
      ActiveWorkbook.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        fileName:=FilePath, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False     
      file_name_saved = ActiveWorkbook.FullName
      MsgBox "PDF FIL OPRETTET MED SUCCES : " & vbCr & vbCr
    Else
      MsgBox "PDF FIL OPRETTET MED NO SUCCES : " & vbCr & vbCr
    End If


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Saturday, September 27, 2014 10:40 AM
  • Sorry BUT it Aint works

    Henrik

    Monday, September 29, 2014 4:19 AM