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

ben_1977

New Member
Joined
Sep 8, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,489
Office Version
  1. 2019
Platform
  1. Windows
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

Rich (BB code):
strDate = Format(VBA.Date, "yyyy.mm.dd")

Hope Helpful

Dave
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,717
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Sep 8, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • SnipImage (001).jpg
    SnipImage (001).jpg
    21.7 KB · Views: 3
  • SnipImage 2.JPG
    SnipImage 2.JPG
    12.5 KB · Views: 3

Watch MrExcel Video

Forum statistics

Threads
1,128,055
Messages
5,628,338
Members
416,311
Latest member
S991102

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top