Error message when running macro

[jj1234]

I am trying to run this macro to automatically print out a range of documents in a specified range but for some reason l am getting a an error - "Something went wrong". I have been told it is something to do with the path name not being correct. I cannot work out where in the coding the path name goes. I would appreciate it if someone could tell me where this goes in the coding:

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
(ByVal hwnd As Long, _
ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long

Private Const SW_SHOWNORMAL = 1

Public Sub PrintOneFile(fn As String)
Dim lngResult As Long
lngResult = ShellExecute(0&, "Print", fn, 0&, 0&, SW_SHOWNORMAL)
If lngResult <= 32 Then
MsgBox "Something went wrong.", vbExclamation
End If
End Sub

Sub PrintFiles()
Dim lngRow As Long
For lngRow = 747 To 758
PrintOneFile Range("J" & lngRow)
Next lngRow
End Sub

 

[xlbo]

this isn't really VBA although it is being invoked within VBA - the vast substance of this code is actually an API call to the Windows environment

The only thing I can tell you is that it is picking up fn (file name?) from column J - 1 row at a time so have a look at what is in columnJ, rows 747 to 758 as a starting point

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[jj1234]

I have checked these columns and there is a Word document in these columns which needs to be printed out.
Is there any other checks l need to do?

 

[xlbo]

I just told you that was the only thing I could tell you.

Have you checked the name of the document ?? does it exist ?? does it have a file path in that cell as well?? does that file path exist ??

what is in cell J747 ???

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[jj1234]

I have checked cell j747 and there is a document in there to be printed out.
I don't know where to check where the path name exits.

 

[xlbo]

throw me a bone here - I have grasped that much already

If I ask you a question it is because without the answer, I cannot help much so please answer my questions this time - we'll start off nice and simple:

what is the actual text in cell J747?


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[jj1234]

Is is a hyperlink to the following document:

Letter of Transmittal\LoT 0747 Mk3 EMC report.doc

 

[xlbo]

that's probably the reason for the error then - I would imagine it is looking for a file path rather than a hyperlink

I do not know eactly what the API call is doing but it seems logical that it is printing the file - to do that, it needs to know where the file is. Hence it needs a file path.

More than that, I cannot tell you as VBA is not really the issue here...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[rmikesmith]

I'm guessing that what is being displayed in the worksheet cells is a descriptive name and not the file path to the actual document. You need to check that. If that is the case, here is a modified version of your print procedure to try. I've assigned an intermediate variable to the value representing the file/path string so you can single-step through the code and varify that an actual path/filename combination is being passed to the ShellExecute function.

Sub PrintFiles()
Dim FName As String

    Dim lngRow As Long
    For lngRow = 1 To 1
      FName = Range("A" & lngRow).Hyperlinks(1).Address
      PrintOneFile FName
    Next lngRow
End Sub

Regards,
Mike