Runtime error 5 while running vba code

[chris_huh]

I have a macro that goes through lots of data and creates charts from it, exporting each set as a pdf. This has been working fine until recently, when it selects a specific data set it throws back a Runtime 5 error on this line of code:

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
filepath & "\" & Sheets("1MonthlyProfile").Cells(5, 31).Value & ".pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False

The only thing i have changed is the contents of cell(5, 31) but that change hasn't caused any problems while looking at the other datasets. It only seems to be a problem with this specific set of data, which admittedly, doesn't actually have any data in it.

Any ideas what this might be?

 

[Andrew Poulsom]

Are you saying that the active sheet is completely blank?

 

[chris_huh]

Well the active sheets (there are two) have all the charts and tables that should be there, just there is no data in them.

Maybe a bit more explanation:

The two active sheets contain several charts showing electricity or gas usage for a specific building for a specific month. So the three variables are Building, Date and Utility type (ie gas or electric). The idea is to create a pdf for each building's gas and electricity usage at the beginning of every month. So i have created a macro which lets you press a button and it will go through first changing to electric and then going through each building, and then doing gas for each building. The Date is set before you press the button.

I have just built this spreadsheet and am going through retrospectively creating last years sheets. Several months worth have worked no problem (other than it takes an hour or so to create them all), but now whenever the macro gets round to creating the pdf for Sheffield it comes back with the error:

"Run-time error: 5
Invalid procedure call or argument"

The only thing i have changed is to add the date to Cell(5, 31), so the date will be included in the pdf filename, but this works fine for the other buildings. Do you think it could be something as simple as the Sheffield building name being too long now (it includes an address so is quite long at 90 characters, and added to the path it makes it about 245).

thanks

 

[Andrew Poulsom]

I guess it could be an issue with the length of the path. What would the path be? Can you export that data as pdf with that filename manually?

 

[chris_huh]

Hmm, it may have been the filename length. I renamed what it would be called and ran the macro and it worked fine. So it seems to have been fixed now.

thanks