Component Request Pending

[northcurlcurl]

Hi guys,

I'm trying to print out an excel spreadseet, using VB, but I get an error message saying "Component Request Pedning". Everytime I try to do that, I'm forced to hit ctrl+alt+del and close Excel.

I dont' know whether this is relevent to the problem I have with the print out but, I'm struggling to close Excel. Even though after I set Excel object to Nothing, I can see Excle is stil running in the Processes tab in Windows Task Manager.

Could you give me some ideas to fix this problem please!

 

[AvatarZA]

I haven't tried to test this theory but maybe try declaring whatever you are using as new.

I think the syntax is summing like this

Dim test as new Whatever

I am a but slow at the moment cannot remember what you declare the excel object as, but try that if it does not work i will try tomorrow with summing else.

Maybe put the code here.

 

[northcurlcurl]

Thanks AvatarZA,

Here is my code...

Dim objExcel As New Excel.Application
Dim intRow, intCol, intCnt, intCashOutLstR, intRegisterLstR As Integer

With objExcel
On Error GoTo errHandler
Application.DisplayAlerts = False
.Workbooks.Add

With .Workbooks(1)
.Worksheets("Sheet1&quot.PageSetup.Orientation = xlLandscape
.SaveAs ("DailyWsht" & Day(PrintDate) & Month(PrintDate) & Year(PrintDate) & ".xls&quot
.Worksheets("Sheet1&quot.PrintOut , , , True
.Worksheets("Sheet1&quot.PrintPreview
End With
.Quit
End With
Application.DisplayAlerts = True
Set objExcel = Nothing

I get the "Component..." error message when I try to execute Worksheet.PrintOut or Worksheet.PrintPreview.

Do you have any ideas to fix this prob?

 

[johnwm]

You need to kill the workbooks collection and the App to end excel.
In Declarations:

Dim appWorld As Excel.Application
Dim wbWorld As Excel.Workbook

'... Do your thing
'...

Then call Cleanup

Sub CleanUp()
    ' This should force an unload of Microsoft Excel,
    ' providing no other applications or users have it loaded.
    Dim W As Workbook
    For Each W In Workbooks
        W.Close savechanges:=False
    Next W
    Set appWorld = Nothing
    Set wbWorld = Nothing
End Sub

As far as printing is concerned, do you have a default printer set on the machine, connected and ready? Let me know if this helps
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'There are 10 kinds of people in the world: those who understand binary, and those who don't.'

 

[northcurlcurl]

Thanks for your help, johnwm.

Insead of using With statement all the time, I've created an Excel.Workbook object to manage a workbook. It kind of worked. But if I add this code, Application.DisplayAlerts = False, Excel.exe would be still shown in the Task Manager, even after I set both Excel.Application and Excel.Workbook object to Nothing. It's a strange problem.

With the printing, as soon as I write someting in a cell, Worksheets("Sheet1&quot.PrintPreview will generate the error, Component Request Pending. By the way, the default printer is connected properly. Could you please give me a help????

Sub PrintInExcel(ByVal PrintDate As Date, _
ByRef rsBanking As ADODB.Recordset, _
ByRef rsDelivery As ADODB.Recordset, _
ByRef rsRegister As ADODB.Recordset, _
ByRef rsCashOut As ADODB.Recordset, _
ByRef rsBankingTotal As ADODB.Recordset, _
ByRef rsDeliveryTotal As ADODB.Recordset, _
ByRef rsRegisterTotal As ADODB.Recordset, _
ByRef rsCashoutTotal As ADODB.Recordset, _
ByRef rsCIH As ADODB.Recordset)


Dim objExcel As New Excel.Application
Dim objWkb As New Excel.Workbook

Set objWkb = objExcel.Workbooks.Add
With objWkb
With .Worksheets("Sheet1&quot
.Cells(1, 1) = "Daily Worksheet"
End With

.SaveAs ("DailyWsht" & Day(PrintDate) & Month(PrintDate) & Year(PrintDate) & ".xls&quot
.Worksheets(&quot;Sheet1&quot.PrintPreview <-This part is the problem!!!
.Close
End With

Set objWkb = Nothing
Set objExcel = Nothing
End Sub