none
Run time error -2147417848 (80010108) with a simple macro to print pdf RRS feed

  • General discussion

  • I've used the below macro for several years with no issues, it archives a pdf print of the page to sharepoint yet today it generates the the above error every time, and requires a complete re-start to get going again.

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
            "https://cxxxxxxxt.sharepoint.com/Shared Documents/xx/xxx/filename" & [MakeFileName] & ".pdf", _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
            :=False, OpenAfterPublish:=False

    The [MakeFileName] function is:

    Public Function MakeFileName()

    'Description.......: Makes file name from the date
    'Accepts...........: Nothing
    'Returns...........: A string like 2005_12_25_Hour_Minute

    'Major change list.:

    Dim strDay As String
    Dim strMonth As String
    Dim strYear As String

    '-- Generate a yyyymmdd date string

    strYear = CStr(Year(Date))

    '-- Add leading zeroes if needed for month and day

    strMonth = CStr(Month(Date))
    If Len(strMonth) = 1 Then
       strMonth = "0" & strMonth
    End If

    strDay = CStr(Day(Date))
    If Len(strDay) = 1 Then
       strDay = "0" & strDay
    End If

    strHour = CStr(Hour(Now))
    If Len(strHour) = 1 Then
       strHour = "0" & strHour
    End If

    strMin = CStr(Minute(Now))
    If Len(strMin) = 1 Then
       strMin = "0" & strMin
    End If

    strSec = CStr(Second(Now))
    If Len(strSec) = 1 Then
       strSec = "0" & strSec
    End If

    MakeFileName = "_" & strYear & "_" & strMonth & "_" & strDay & "_" & strHour & "Hr_" & strMin & "Min"

    End Function

    Wednesday, August 23, 2017 2:05 AM

All replies

  • You can simplify the function to

    Public Function MakeFileName()
        MakeFileName = Format(Now, "_yyyy_mm_dd_hh""Hr""_nn""Min""")
    End Function

    Does the error occur if you hold down the left Ctrl key when you start Excel and answer Yes to the question if you want to run Excel in Safe Mode?


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, August 23, 2017 4:33 AM
  • Sorry for the delay.

    I've changed to your much simpler file name function thank you.

    If I open in safe mode and attempt to run the macro, with new filename function, I get Runtime 1004, document not saved. The document may be open, or an error may have been encountered when saving.

    However the only way I could get the sheet to run in safe mode was to download it to C drive and open from there, rather than opening from sharepoint/cloud, which is where its trying to save to. Or is this what we were trying to test?

    Sunday, September 10, 2017 9:55 PM
  • I'm afraid I can't help you with that.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, September 11, 2017 5:25 AM