Run Time error -2147024773 (8007007b)

[josros60]

Hi,

have following code giving the error 2147024773 (8007007b) and highlighting this line in the code:

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:=NewFN, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

complete code:

Sub SaveInvoiceBothWaysAndClear()
    Dim NewFN As Variant
    Call posttoregister
    ' Create the PDF First
    NewFN = "C:\Users\jose.rossi\Desktop\Excel Files\Contras\West Telecom\RHICHOM NETWORKS" & Range("A78").Value & ".pdf"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:=NewFN, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    'Next, Save the Excel File
    ActiveSheet.Copy
    NewFN = "C:\Users\jose.rossi\Desktop\Excel Files\Contras\West Telecom\RHICHOM NETWORKS" & Range("A78").Value & ".xlsx"
    ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
    ActiveWorkbook.Close
    ' Increment the invoice number
    Range("K2").Value = Range("K2").Value + 1
    ' Clear out the invoice fields
    Range("C8:C10").ClearContents
    Range("A16:K20").ClearContents
   
End Sub

Another thing happening if disable that line just to save as excel file giving the error 1004 and highlight this line:

ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook

thank you

 

[severynm]

What is in Range("A78").Value and does it contain any invalid characters for a file name? A cursory search for that error code uncovers that is usually the issue for this error code.

 

[josros60]

Hi,

is a text formula in that cell want it to be part of the file name.

="Contra"&" # "&K2&"  in the amount of USD "&TEXT(CONTRA!$C$23,"$#,##0.00")&", "&TEXT(CONTRA!$J$26,"MMM-DD-YYYY")&" for "&C10&""""

 

[severynm]

Hi,

is a text formula in that cell want it to be part of the file name.

="Contra"&" # "&K2&"  in the amount of USD "&TEXT(CONTRA!$C$23,"$#,##0.00")&", "&TEXT(CONTRA!$J$26,"MMM-DD-YYYY")&" for "&C10&""""

Sorry I dont think I was clear enough. I meant to ask what are some examples that the formula would evaluate to.

 

[josros60]

The formula result is this:

"CONTRA-RHICOM NETWORKS # 2256 USD $2,500.00, Jun-18-2021 for (JUNE 2021)"

thank you

 

[severynm]

Well that's a valid filename, so that's not the issue. You should obviously have permission to save in the folder because it's on your desktop, and based on your code above NewFN should be well under the windows max filename length. I'm also assuming a file with that same name is not open when you are trying to save it.

If you add Debug.Print NewFN to your code, what is the output? I suspect you might be missing a "\" at the end of your RHICHOM NETWORKS folder.

 

[josros60]

Thank you, it worked.

that what was missing "\".

 

[smozgur]

Thank you, it worked.

that what was missing "\".

Next time, please mark the post that solved the problem. It is #6 for this question as you confirmed, so I switched the marked solution.

 

[josros60]

Next time, please mark the post that solved the problem. It is #6 for this question as you confirmed, so I switched the marked solution.

Thanks

 

[smozgur]

Thanks