[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to Handle Error in Printing At Excel ?

Posted on 2007-09-28
14
Medium Priority
?
202 Views
Last Modified: 2010-04-23
Hi,

I have an app that print to excel directly by:

Open Excel using:

Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Add
    Set XLSheet = xlBook.Worksheets(1)

    xlApp.Visible = True

Then printing data using:

strCellRange = Get_CellRange(Const_FromCol, intRow, Const_ToCol, intRow)
.Range(strCellRange).Select

.Cells(intRow, Const_ColNo).Value = "'" & rstSourceData!AccNo

When user open another excel sheet then it will cause error.
When user try to stop the printing then it will cause error.

My question is how to handle that error.

Thank you.
0
Comment
Question by:emi_sastra
  • 7
  • 7
14 Comments
 
LVL 33

Expert Comment

by:Jeroen Rosink
ID: 20016982
When the user opens another workbook or sheet while the macro is running it will cause an error because excel cannot deal with different instances of excel which the macro is not controlling.

Also since you use the select statement, that statement is will try to select a sheet and soesn't allow manual interference.

The error which might raise when the user stops the printing process is also the result of a manual interference of the user. Since Excel is wating for some kind of respons that the print assignment ended succesfully.

I suggest to avoid manual interference of the user. If those actions are necessary then build them in your macro.

I hope this explains the error a bit.

regards,
Jeroen
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 20029340
Hi,
Could you be more specific how to do it?

Thank you.
0
 
LVL 33

Expert Comment

by:Jeroen Rosink
ID: 20029357
Hello,
Im guessing you need further explanation about multiple instances of Excel.
What I ment with this is that in Excel you are able to open multiple workbooks in the same excel application. You see them under the windows menu item.

It is also possible to run multiple excel instances of the application (start excel more then once using the Excel icon.
If you open in both instances a workbook then they are not visible in both instances.

If you Run a macro on one instance and allow intercation of the user on another workbook or worksheet then this should be mostly possible when another instance is running.
Interaction of users while a macro is running will make a macro go into errors unless you build in some pause statements.


If the macro is sent a print job then it might wait for the response from the printer that the worksheet is free for other actions. If the user cancels the print job using the Printer dialog then this call is not send back to excel, which might the macro result into errors.

The main thing here is that interactions of users during running a macro will results into erros as this should not be intended behaviour.

I hope this explains it a bit further.

regards,
Jeroen
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 1

Author Comment

by:emi_sastra
ID: 20029372
Hi,

How to handle for user interruption (try to stop, close the excel or open another excel) ?

How to do it it macro ? Is it possible to open a recordset and let the macro do the printing, so the user could continue its job without hanging at the printing event?

Could you please provide simple code that works for me?

Thank you.

0
 
LVL 33

Expert Comment

by:Jeroen Rosink
ID: 20029379
I doubt there is a way to allow user interaction.
Normally the print job will not take that much time. So if it is OK to let the user wait for a few seconds then you might prevent the user to do things in excel.

I noticed that you also didn't post the actual print statement in the example mention in this Q.
like:
ActiveWindow.SelectedSheets.PrintOut Copies:=1

or in your example:
xlApp.SelectedSheets.PrintOut Copies:=1

Jeroen
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 20029396
Hi,

Logically thinking it should a way to handle the user interruption for stopping or close the excel.
This kind of interruption could be handle using error handling at the printing procedure by asking the user really want to stop or not?

Another interruption is user try to open another instance of excel.
This kind of interruption could be handle using macro. Like a mentioned before,  the visual basic open the recordset and the macro do the printing.

I have never try to make macro and have no idea how to do it.

If we could insert the macro from visual basic after I open a new excel sheet and command the macro to do the job then the problem is solved.

Thank you.



0
 
LVL 33

Expert Comment

by:Jeroen Rosink
ID: 20029423
To stop interaction on the main workbook while the macro is running you might or add a progress bar to it. The easiest way is warning the user to wait untill the macro is finsihed.
This can be simply done by adding a message box in the beginning and the end like:
Msgbox "Printing process is started, no interaction is now allowed!"

Msgbox "Pring process ended!"

for adding macro's in a new workbook you will go into an approach which needs some references set which should be done on the excel application of the user it self like trusting access to Visual Basic Project (menu: Tools>>Macros>>Security>>Trusted Publishers)
for code examples you might take a look at:
http://www.cpearson.com/excel/vbe.aspx

preventing a user not to start another instance will take more effort in coding, as you disable some OS functions which might be hard to restore when a macro is going into error.

If you don't want any error message to be shown no matter the result you might think of adding a line line like this in the beginning of the code:
On Error Resume

related to showing a message while printing job is running and asking to the user really to stop: there is no Event in Excel which can trigger this event.

A workaround might be to prompt the user that printing will start and therefor the excel application will be made hidden.
Application.hidden = true

Another option could be showing a userform which will be shown during the interfaction and released after the job is done. As when the userform is shown no actions can be taken on the excel workbook as long as the userform is not in Modeless mode.

0
 
LVL 1

Author Comment

by:emi_sastra
ID: 20030120
Hi,

Me : Thanks for the link.

Me : Sometimes user wants to cancel the printing as they had wrongly choose parameter or something else. They absolutely the app allow to do it.

You : Preventing a user not to start another instance will take more effort in coding, as you disable some OS functions which might be hard to restore when a macro is going into error.

Me : Well, I thought when  I use macro then the problem is solved even the user open another instance of excel. I though when using macro, it has its own matter nothing to do with another instance of excel.  I think I was wrong.

You : related to showing a message while printing job is running and asking to the user really to stop: there is no Event in Excel which can trigger this event.

Me: I think when the user try to stop the printing, then it will back to the printing procedure of the macro.
In the beginning procedure of the macro we could write "On Error Goto ErrHandler" and that handler show message to the user to select choice, "Cancel or Continue".

There is an event called "BeforeCloseEvent" in Excel which might help the interaction.

How to pass recordset that open by visual basic to the macro or I should open the recordset inside the macro?

Thank you.





0
 
LVL 33

Expert Comment

by:Jeroen Rosink
ID: 20030261
I suggest to open the rcordset to be opened inside the macro and not on an Workbook_open event.

the BeforeCloseEvent can be used, even the BeforePrint.

here an example which goes into the ThisWorkbook code pane:


Private Sub Workbook_BeforePrint(Cancel As Boolean)

iReply = MsgBox("Do yo realy want to print?," _
          & " terminate", vbYesNo + vbQuestion)
      If iReply = vbYes Then
        Call YOURPRINTMACRO 'Enter here the name of the macro which should do the print job
      Else
        Cancel = True
      Exit Sub
      End If
End Sub

0
 
LVL 1

Author Comment

by:emi_sastra
ID: 20030344
HI,

Before_Print is event to print to printer or preview. Our target right now is to print to excel sheet.
I think it is suitable using BeforeClose.

By the way, could you provide a simple vb code how to do it.

1. Open the excel sheet. I already know it.
    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Add
    Set XLSheet = xlBook.Worksheets(1)

    xlApp.Visible = True

I don't know how to do it below:

2. Insert procedure ("Open Recordset and "Print_To_Excel") from Visual Basic into excel module.
3. Run the macro in Visual Basic.

Thank you.


   

0
 
LVL 33

Accepted Solution

by:
Jeroen Rosink earned 2000 total points
ID: 20030409
2. to insert a procedure you might take samples mentioned in:
http://www.cpearson.com/excel/vbe.aspx

here the example code:
    Sub AddModuleToProject()
        Dim VBProj As VBIDE.VBProject
        Dim VBComp As VBIDE.VBComponent
       
        Set VBProj = ActiveWorkbook.VBProject
        Set VBComp = VBProj.VBComponents.Add(vbext_ct_StdModule)
        VBComp.Name = "YourModule"
    End Sub

Sub AddProcedureToModule1()
        Dim VBProj As VBIDE.VBProject
        Dim VBComp As VBIDE.VBComponent
        Dim CodeMod As VBIDE.CodeModule
        Dim LineNum As Long
        Const DQUOTE = """" ' one " character

        Set VBProj = ActiveWorkbook.VBProject
        Set VBComp = VBProj.VBComponents("YourModule")
        Set CodeMod = VBComp.CodeModule
       
        With CodeMod
            LineNum = .CountOfLines + 1
            .InsertLines LineNum, "Public Sub OpenRecordset()"
            LineNum = LineNum + 1
            .InsertLines LineNum, "YOURCODE" 'keep in mind that for each line of code you need to use the insert line command
            LineNum = LineNum + 1
            .InsertLines LineNum, "End Sub"
        End With
   
    End Sub
Sub AddProcedureToModule1()
        Dim VBProj As VBIDE.VBProject
        Dim VBComp As VBIDE.VBComponent
        Dim CodeMod As VBIDE.CodeModule
        Dim LineNum As Long
        Const DQUOTE = """" ' one " character

        Set VBProj = ActiveWorkbook.VBProject
        Set VBComp = VBProj.VBComponents("YourModule")
        Set CodeMod = VBComp.CodeModule
       
        With CodeMod
            LineNum = .CountOfLines + 1
            .InsertLines LineNum, "Public Sub Print_To_Excel()"
            LineNum = LineNum + 1
            .InsertLines LineNum, "YOURCODE" 'keep in mind that for each line of code you need to use the insert line command
            LineNum = LineNum + 1
            .InsertLines LineNum, "End Sub"
        End With
   
    End Sub

keep in mind that most of the virusscanner might complain about inserting Modules in workbooks. I even think since you are running Excel using the creatObject command to create a new module and the macro in it might become difficult since Excel has to run because in security the Trust Access to Visual basic must be checked (tools>>Macros>>Security>>Trusted publishers.)

Using the before close event only makes sence when you set the print assingnment overthere as then no user interaction can be done. Still no other excel application can be used during this command. Mainly you initial problem wont be solved.

To call a macro from another workbook can be done using this command:
Application.Run "C:\YourWorkbook.xls!" & myMacro

The beforeclose event looks like something:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call YouRMacro1
Call YouRMacro2
End Sub

perhaps setting the: xlApp.Visible = False

the user doesn't know that excel app is running and task can be executed. If the user should do some additional steps I suggest to automate those as well.

regards,
Jeroen
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 20030442
Thank you very much.

It help a lot.
0
 
LVL 33

Expert Comment

by:Jeroen Rosink
ID: 20030569
Glad it helped. I hope you can use some pieces/ideas mentioned. I think you have a huge job to do, controlling Excel from another application and still be flexibel.
regards,
Jeroen
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 20030678
Hi,

I think I already have some figures to continue to solve my problem. I have to try it first.

If I have a deadlock then I will ask again.

Thank you.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question