Continue macro

alring · Mar 6th 2006

Hi

I have a macro that prints alot of other documents. All the documents is listed in Excel. The macro looks into some cells and print the document that is written in those cells. My problem is that the last document that the macro prints maybe isn't there. Meaning that the document maybe isn't made yet. How can I get the macro to see if the document is there? And if it's there then print the document. And if it's not there then a mesage box should come telling that the document can't been printed and then end the macro?

Alring

Wigi · Mar 6th 2006

Re: Continue macro

Can you post your code to see the structure and also to put the code into it? Makes it easier to provide an answer.

Wigi

alring · Mar 6th 2006

Re: Continue macro

Hi Wigi

This is a short code. the right one is very long because I open about 20 Word documents. But it's the last part where I want to open bNameAndPath that where I want to macro to see if the file (Range A10) is there and then print it. If it's not there then come with a message box saying it's not there and then end the macro.

Code

Sub excelToWord_click()
    'Open the word document
    aNameAndPath = Range("A7")
    Set wdApp = CreateObject("Word.Application")
    wdApp.documents.Open Filename:=aNameAndPath
    wdApp.Visible = True
    'copy cells
    Worksheets(1).Range("A1:D4").Select
    Selection.Copy
    Range("A9").Select
    'insert cells
    With wdApp.ActiveDocument.Sections(1)
        .Headers(1).Range.inlineshapes(1).Activate
        Range("A1:D4").Select
        ActiveSheet.Paste
    End With
    'restore Word
    If wdApp.ActiveWindow.View.SplitSpecial <> 0 Then
        wdApp.ActiveWindow.Panes(2).Close
    End If
    If wdApp.ActiveWindow.ActivePane.View.Type = 1 _
    Or wdApp.ActiveWindow.ActivePane.View.Type = 2 Then
        wdApp.ActiveWindow.ActivePane.View.Type = 3
    End If
    wdApp.WordBasic.AcceptAllChangesInDoc
    wdApp.ActiveDocument.PrintOut , Copies:=1
    wdApp.ActiveWindow.Close SaveChanges:=False
    
    Application.CutCopyMode = False
    
    bNameAndPath = Range("A9")
    Filename = Range("A10")
    Workbook.Open Filename:=aNameAndPath & Filename
    ActiveWindow.SelectedSheets.PrintOut Copies:=intPrintJobs, Collate:=True
    ActiveWindow.Close SaveChanges:=False
    
End Sub

Display More

Alring

alring · Mar 7th 2006

Re: Continue macro

Hi

I have changed the last part of the code. It's the part after:

Code

Application.CutCopyMode = False

I have changed it to:

Code

'   Open Toolslist
    bNameAndPath = Range("A9")
    filesearch = Range("D1") & _
    "NKT" & _
    Range("D2")
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(bNameAndPath)
    Set fc = f.Files
    For Each filename In fc
        If Left(filename.Name, Len(filesearch)) = filesearch Then
            ActiveWorkbook.FollowHyperlink bNameAndPath & filename.Name
            Exit For
        End If
    Next
    ActiveWindow.SelectedSheets.PrintOut Copies:=intPrintJobs, Collate:=True
    ActiveWindow.Close SaveChanges:=False

Display More

The path for the file is in A9 and the filename comes from D1 and D2. and it's working as it should.
But I still need to put in a message box that will come if the file don't exist. I have tried to put the code for the message box in after "Exit For" but then I have to press OK 18 times before the message box disapear again.
So if some one knows where I can write the code for the message box then I will be gratefull.
Alring

alring · Mar 17th 2006

Re: Continue macro

Hi there

I got it not. I have changed the code total after trying alot of different things. So now it's working. The code look like this now.

Code

'   Search file and open if exist
    bNameAndPath = Range("A9")
    f = Range("D1") & "NKT" & Range("D2")
Set fs = Application.filesearch
With fs
    .LookIn = bNameAndPath
    .filename = f
    If .Execute > 0 Then
        Workbooks.Open filename:=bNameAndPath & f
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    ActiveWindow.Close SaveChanges:=False
    Else
        MsgBox "Document do not exist."
    End If
End With

Display More

Regards
Alring

Continue macro

Participate now!

Share