Microsoft Visual Basic Runtime error 1004: Document not saved

Question

Hello,

I've got a issue with my Macros in Excel.

It worked just fine before and it still works on another pc, however it does not on my main pc.

When I try to run it, it throws an error and highlights this area when I press Debug:

Selection.ExportAsFixedFormat Type:=xlTypePDF, fileName:= _

        filePath, Quality:=xlQualityStandard, _

        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _

        False

Maybe my main pc is suddenly missing a piece of software?

I am totally lost.

Thanks in advance,

Thijs

Answer 1

Replace the function with all (!) code below and try again. What happens?

Andreas.

Public Function generatePDFReport(ReportType As String, sheet As Worksheet, sDate As String) As String
  Dim filePath As String
  Dim shipName
  Dim Where As Range

  sDate = Replace(sDate, "\", "_")
  shipName = ActiveWorkbook.Worksheets("Voyage info").Range("MV").Value
  filePath = CreateObject("WScript.Shell").specialfolders("MyDocuments")
  filePath = filePath & "\" & shipName & "_" & ReportType & "_" & Replace(sDate, "-", "_") & ".pdf"
  filePath = ValidFileName(filePath)

  If ReportType = "Pool Declaration Report" Then
    'sheet.Activate
    'sheet.Range("B1:F30").Select
    Set Where = sheet.Range("B1:F30")
  Else
    'sheet.Activate
    'sheet.Range("B8:F44").Select
    Set Where = sheet.Range("B8:F44")
  End If

  'Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    filePath, Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
    False
  Where.ExportAsFixedFormat Type:=xlTypePDF, Filename:=filePath

  generatePDFReport = filePath
End Function

Private Function ValidFileName(ByVal FName As String, _
    Optional ByVal ReplaceChar As String = "") As String
  'Return a filename without invalid chars
  'Avoid CON, PRN, AUX, NUL, COM1 to COM9, LPT1 to LPT9 as filename
  Const InvalidChars = "\/:*?""<>|"
  Dim i As Integer, p As Long
  Dim Digit As String
  For i = 1 To Len(InvalidChars)
    Digit = Mid$(InvalidChars, i, 1)
    p = InStr(FName, Digit)
    Do While p > 0
      Mid$(FName, p, 1) = vbNullChar
      p = InStr(FName, Digit)
    Loop
  Next
  For i = 1 To 31
    Digit = Chr$(i)
    p = InStr(FName, Digit)
    Do While p > 0
      Mid$(FName, p, 1) = vbNullChar
      p = InStr(FName, Digit)
    Loop
  Next
  ValidFileName = Replace(FName, vbNullChar, ReplaceChar)
End Function

Answer 2

Hi Andreas,

It is working again!

Thank you very much for taking your time to help me, I really appreciate it.

Thijs

Answer 3

Please never use SELECT, SELECTION, ACTIVECELL, it is slow and error prone. Always refer to the objects directly.

I don't know what is selected at this point, customize the referred range:

Range("A1:S100").ExportAsFixedFormat Type:=xlTypePDF, fileName:= _
filePath

Andreas.

Hi Andreas,

Thank you for helping me.

I just want to tell you I didn't create this Macros and I only have some basic knowledge of programming.

I do not understand why it stopped working on the main pc, but it still works on another pc without giving errors. It's the same file... maybe it doesn't have anything to do with programming?

Anyway, this is the complete function of generatePDFReport:

Public Function generatePDFReport(ReportType As String, sheet As Worksheet, sDate As String) As String

  Dim filePath As String

  Dim tabs As Integer

  Dim text As String

  Dim shipName

  sDate = Replace(sDate, "", "_")

  shipName = ActiveWorkbook.Worksheets("Voyage info").range("MV").Value

  filePath = CreateObject("WScript.Shell").specialfolders("MyDocuments")

  filePath = filePath & "" & shipName & "_" & ReportType & "_" & Replace(sDate, "-", "_") & ".pdf"

  If ReportType = "Pool Declaration Report" Then

    sheet.Activate

    sheet.range("B1:F30").Select

  Else

    sheet.Activate

    sheet.range("B8:F44").Select

  End If

  Selection.ExportAsFixedFormat Type:=xlTypePDF, fileName:= _

        filePath, Quality:=xlQualityStandard, _

        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _

        False

  generatePDFReport = filePath

End Function

Thanks,

Thijs

Answer 4

Please never use SELECT, SELECTION, ACTIVECELL, it is slow and error prone. Always refer to the objects directly.

I don't know what is selected at this point, customize the referred range:

Range("A1:S100").ExportAsFixedFormat Type:=xlTypePDF, fileName:= _
filePath

Andreas.