Invoice Population Macro Error, been working fine for years. now on Excel 365 (might be coincidence...?)

[ben_1977]

Hi,

I have a monthly report I run which outputs single sheet "Invoices" from my workbook that is populated by a months worth of sales. The macro goes through a list of names, pulls the details for that person, populates the pivot table on the invoice, then saves as both a single excel sheet and a pdf. up until today it has worked fine. the code is, and the error occurs when it reaches the date line. It states "Compile error, can't find project or library":

Sub Save_Sheets_To_New_Books() 'INVOICES

Const strWbPath As String = "D:\Accounts\EA Letters\2020 09 September\"

Dim strDate As String 'todays date

'save the sheets to new books within the active folder and print them

Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual

strDate = Format(Date, "yyyy.mm.dd") '(Date, "yyyy.mm.dd") THIS IS WHERE THE ERROR OCCURS the word DATE is highlighted

ActiveSheet.Copy

Call DeleteNamedRanges

Call PrintAreaAndPasteSpecial

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=strWbPath & Name_of_Artist & "_" & strDate & ".pdf" _

, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _

:=False, OpenAfterPublish:=False

ActiveWorkbook.SaveAs FileName:=strWbPath & Name_of_Artist & "_" & strDate & ".xlsx"

Application.DisplayAlerts = True

'ActiveSheet.PrintOut Copies:=1, Collate:=True 'remove the comment if you want to print it out as well

ActiveWorkbook.Close False

Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = True

End Sub

Please could any offer any assistance.

Many Thanks

 

[dmt32]

Hi welcome to forum

the application may have lost the reference to an object or type library resulting in the error
Try following to resolve:

Open a module in Design view or press ALT+F11 to switch to the Visual Basic Editor.
On the Tools menu, click References.
Clear the check box for the type library or object library marked as “Missing:”

If this fails then try following

strDate = Format(VBA.Date, "yyyy.mm.dd")

Hope Helpful

Dave

 

[Joe4]

Welcome to the Board!

It sounds like you have not have all the correct references/libraries selected.
In the VB Editor, go to Tools -> References and see which libraries you have selected.
I believe you should have at least the 4 following ones selected:
- Visual Basic For Applications
- Microsoft Excel 16.0 Object Library
- Microsoft Office 16.0 Object Library
- OLE Automation

If any are missing, find them on the list and select them, and then try running your code again.

 

[ben_1977]

Hi folks,
Thank you so much, unticking the missing references definitely stopped the error from popping up.
Unfortunately it's thrown up a formatting issue now which it didn't seem to have in the past. The single sheet is supposed to have the totaled values on the table, but the table has gone and the details are simply numeric and not in currency. I've added some examples of what it used to look like with the formatting, and one from today where the table has lost all it's formatting.
Can you offer anymore words of wisdom? CAn I give you any additional info?
Many Thanks