run time error 1004

[paintfrk775]

Hello all ,

I have been having this issue on one computer, every time i go print using the macro i get a run time 1004.

The other computers i run the macro on works with no issues.

i have attached my code to see if i missing something please let me know if there is any solutions or any issues with my code.




' this code sits in this workbook
' it adds plus one from the last time it saves


Private Sub Workbook_Open()
Range("j1").Value = Range("j1").Value + 1
Range("j1").Font.Color = vbRed
End Sub












Sub Submit_info()
'
' Will save P.O. template save as new P.O. print make into PDF and close


'
Dim strFilename As String
Dim strpath As String
Application.DisplayAlerts = False


strpath = "f:\purchase_orders\"
strFilename = Range("j1").Value





ActiveWorkbook.Save

'Remove all code from ThisWorkbook code module

ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule.DeleteLines 1, _
ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule.CountOfLines


'will create PDF

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
strpath & strFilename, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
False


'will print out white copy


ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False

'will save as with p.o. #

ActiveWorkbook.SaveAs strpath & strFilename, _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False


'Will write out yellow


ActiveSheet.Unprotect
ActiveSheet.Shapes.AddTextEffect(msoTextEffect3, "Your text here", "+mn-lt", 54 _
, msoFalse, msoFalse, 339.0575590551, 160.7104724409).Select
Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = "-YELLOW-"
With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 8). _
ParagraphFormat
.FirstLineIndent = 0
.Alignment = msoAlignCenter
End With
With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 8).Font
.Bold = msoFalse
.Caps = msoNoCaps
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Shadow.Type = msoShadow21
.Shadow.Visible = msoTrue
.Shadow.Style = msoShadowStyleOuterShadow
.Shadow.Blur = 5
.Shadow.OffsetX = 0
.Shadow.OffsetY = 0
.Shadow.RotateWithShape = msoFalse
.Shadow.ForeColor.RGB = RGB(0, 0, 0)
.Shadow.Transparency = 0.3000000119
.Shadow.Size = 100
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(255, 255, 255)
.Fill.Transparency = 0
.Fill.Solid
.Size = 54
.Line.Visible = msoTrue
.Line.ForeColor.RGB = RGB(255, 255, 255)
.Line.Transparency = 0
.Line.Weight = 1.45
.Line.DashStyle = msoLineSolid
.Line.Style = msoLineSingle
.Name = "+mn-lt"
.Spacing = 0
End With
Selection.ShapeRange.IncrementLeft -154.5
Selection.ShapeRange.IncrementTop 16.5
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
Selection.Delete

'will write out pink



ActiveSheet.Shapes.AddTextEffect(msoTextEffect3, "Your text here", "+mn-lt", 54 _
, msoFalse, msoFalse, 339.0575590551, 160.7104724409).Select
Selection.ShapeRange.TextFrame2.TextRange.Font.Size = 96
Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = "-PINK-"
With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 6). _
ParagraphFormat
.FirstLineIndent = 0
.Alignment = msoAlignCenter
End With
With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 6).Font
.Bold = msoFalse
.Caps = msoNoCaps
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Shadow.Type = msoShadow21
.Shadow.Visible = msoTrue
.Shadow.Style = msoShadowStyleOuterShadow
.Shadow.Blur = 5
.Shadow.OffsetX = 0
.Shadow.OffsetY = 0
.Shadow.RotateWithShape = msoFalse
.Shadow.ForeColor.RGB = RGB(0, 0, 0)
.Shadow.Transparency = 0.3000000119
.Shadow.Size = 100
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(255, 255, 255)
.Fill.Transparency = 0
.Fill.Solid
.Size = 96
.Line.Visible = msoTrue
.Line.ForeColor.RGB = RGB(255, 255, 255)
.Line.Transparency = 0
.Line.Weight = 1.45
.Line.DashStyle = msoLineSolid
.Line.Style = msoLineSingle
.Name = "+mn-lt"
.Spacing = 0
End With
Selection.ShapeRange.IncrementRotation 312.6789166667
Selection.ShapeRange.IncrementLeft -231.7500787402
Selection.ShapeRange.IncrementTop 135.75
ActiveWindow.SmallScroll Down:=6
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
Selection.Delete
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True

'will close work book

ActiveWorkbook.Close True

End Sub

 

[Joe4]

Welcome to the Board!

Whenever something works on some computers, but not others, the first thing I usually ask myself is "what is different between those users and computers"?

Perhaps they have different versions of Office or Excel.

A lot of times, it may be drive mappings. And I see that you have this line of code here:

[COLOR=#333333]strpath = "f:\purchase_orders\"[/COLOR]

So, on the computer where it is NOT working, does the user have an F drive mapped?
And do they have access to the "purchase_orders" folder?

 

[paintfrk775]

Yes f drive is mapped out and he has permission to the folder

 

[Joe4]

Does it give you the debug option and highlight the offending line of code?

Are you able to step through your code line-by-line using F8?
If so, which line causes the error to pop-up?

Also, you may want to take a look at the following:
On a computer where it works, go into the VB Editor and take a look to see which references are selected (under Tools -> References). Make a note of all of them.
Then on the computer where it doesn't work, do the same thing, and see if any are missing.

 

[paintfrk775]

When i go and debug on the problem computer it will highlight every line as i move down i also have tried commenting out the highlighted line and running the macro again with still no luck.

Everyone in the office is on the same version of excel. but different operating systems could that possibly play a role in the issue?

The excel file its self is stored on a network drive(F) and when needed is pulled from the F drive to create a PO and is saved back onto the F drive in the purchase_order folder

 

[Joe4]

i also have tried commenting out the highlighted line

So is it highlighting a specific line? If so, which one?

Everyone in the office is on the same version of excel. but different operating systems could that possibly play a role in the issue?

Possibly. It would probably in the references issue I mentioned in my previous email. Have you taken a look at that to see if there were any missing references?

 

[paintfrk775]

the highlighting will start at the create PDF section and will go all the way down the end sub.


Reference issue i have the same ones marked as the issue computer

 

[Joe4]

A few things to check.

- What is cell J1 (which is used to build the file name)?

- Does that computer have the ability to write files out to a PDF (usually found in the print options)?

- Is there any data on the sheet to be printed out?

- Does someone else already have a file open with the same name as the file you are trying to save?

 

[paintfrk775]

- What is cell J1 (which is used to build the file name)?

Cell J1 is the PO number that is the file name that is used to save the file and also how we look up PO

- Does that computer have the ability to write files out to a PDF (usually found in the print options)?

We can manualy save a the excel file to a PDF so would he have the ability to write a file to a PDF

- Is there any data on the sheet to be printed out?

Yes there is information that need to be printed so we have a hard copy of the purchase order

- Does someone else already have a file open with the same name as the file you are trying to save?

No only one person can open it any given time

 

[Joe4]

Regarding the last question, I am not talking about the Excel file with the VBA code, I am talking about the PDF that is being created.
Let's say that the process is trying to create a PDF named "Bob.PDF".
However, if a file already exists out there named "Bob.PDF" and somebody has it open, that could be a problem. But it looks like that would not return the 1004 error, but some other error instead.

I think the first thing we want to do is make sure the person can really write a PDF file out to that location. So let's just isolate that part of the code. Here is a simple macro code you can use to test that:

Sub MyTest()

    Dim myFileName As String

    myFileName = "C:\Temp\Test.pdf"
    'myFileName = "F:\purchase_orders\Test.pdf"
    
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=myFileName, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
        
    MsgBox "Test Successful!"

End Sub

You want to run two tests:

1. Test to see if they can write a PDF out to their hard-drive. Pick some drive on their C drive that they have access to (I used "C:\Temp\" directory - if they do not have one, either create it or pick a different folder on their C drive that they can write to). If that works, then they have the ability to create PDF files via VBA code.

2. In the second test, comment out the first line of VBA code I have for the myFileName variable, and uncomment out the second line I have, setting the file path to the F drive. If that does not work (and the first one does), then they do not have the ability to write to that folder.