0

Excel VBA code that worked previously on windows 7 (Excel 2013) no longer works on windows 10 (excel 365).

Now a run time error is generated.

Run-time error '-2147024773 (8007007b)'

Document not saved

The debugger highlights the 4 rows beginning with the first ActiveSheet.ExportAsFixedFormat all the way to False.

Is there a way to alter my code so this runs on windows 10 / excel 365?

Workbooks("Valuation Grail.xlsm").Save

Dim mydir As String
Dim mydrive As String

'Used activeworkbook.path instead of CurDir() because activeworkbook.path does not change for the same saved workbook
mydir = ActiveWorkbook.Path
mydrive = Left(mydir, 1)

'save original wmu as 2 PDFs
Dim month_end As String
Dim generic_vg As String
Dim archived_vg As String
Dim taa_packet As String

'creates saving format for archived pdf
month_end = Format(WorksheetFunction.EoMonth(Now(), -1), "yyyymmdd")

generic_vg = mydrive & ":\01\spec_folder\01 - DATA\Valuations Report\Valuations Report.pdf"
archived_vg = mydrive & ":\01\spec_folder\01 - DATA\Valuations Report\" & month_end & "-Valuations.pdf"
taa_packet = mydrive & ":\01\spec_folder2\#Packet Assembly\TAA\" & "12 - Valuation Grail.pdf"


'Saves the generic and archived version of valuation report to spce_folder data folder
ThisWorkbook.Sheets(Array("Table", "Table_SS")).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        generic_vg, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        False
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        archived_vg, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        False
ThisWorkbook.Sheets("Summary").Select

'saves the pe tabs to the oshea packet assembly folder
ThisWorkbook.Sheets(Array("PE_Summary", "TAA_SS")).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        taa_packet, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        False
ThisWorkbook.Sheets("Summary").Select
TommyO
  • 37
  • 6
  • What's the error message? – BigBen Mar 02 '21 at 00:55
  • I will suggest, first in a fresh workbook try to export a single sheet. If it is successful then use loop to export multiple sheets. – Harun24hr Mar 02 '21 at 02:06
  • 1
    What does `ActiveWorkbook.Path` return? Are you using OneDrive/SharePoint for file storage? – Tim Williams Mar 02 '21 at 06:39
  • `ActiveWorkbokk.Path` previously returned the full path where the workbook was located including the letter the drive was mapped to (Z, Y, X, etc.). I would then use `mydir` to pull only that letter to use in my file path. Different members of my team would have different letters for the same drive we were all using. – TommyO Mar 02 '21 at 14:44
  • This error usually indicates a problem with the full file name string, so take a look at what's actually in `generic_vg`. – RBarryYoung Mar 02 '21 at 14:54
  • @Tim Williams Thank you for your comment as it helped my figure out the issue. Do you know how to pull just the drive letter as you could in earlier versions of excel/windows? – TommyO Mar 02 '21 at 15:04

1 Answers1

0

ActiveWorkbokk.Path previously returned the full path where the workbook was located including the letter the drive was mapped to (Z, Y, X, etc.). I would then use mydrive to pull only that letter to use in my file path. This method no longer works.

I changed mydrive = "Z" and the code now works.

Ideally I'd like to find a new way to pull that drive letter so members of our team don't have to manually change the mydrive variable each time but this works for now.

TommyO
  • 37
  • 6