Repost: Not show Excel in procedure

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hiya, I posted this query a while back and got some helpful tips from TC. Have been a way for a while so I thought I'd re-post it with further detail

I am running a procedure in Access that will create and open 4 recordsets... it will then copy a master Excel workbook to a target file, open this target file and go through 4 different sheets copying the 4 recordsets respectively. It then also emails the file (using the XLWorkbook.SendMail method) to an address held in a variable

I managed to get this to work beautifully - but only by making Excel visible. Here is the relevant part of my code

FileCopy Master, TargetFil

Set XLWorkbook = GetObject(TargetFile

With XLWorkboo
.Application.Visible = True '/////////////////////////////////////////////////////////
.Parent.windows(1).Visible = True '/////////////////////////////////////////////////////////
.Sheets(2).Range("A9").CopyFromRecordset rstre
.Sheets(2).Range("A2") = "Returned to host since " & Format([Forms]![External Reporting]![txtstdate],
"dddd d mmmm yyyy"
.Sheets(3).Range("A9").CopyFromRecordset rstpreo
.Sheets(3).Range("A2") = "Returned to host since " & Format([Forms]![External Reporting]![txtstdate],
"dddd d mmmm yyyy"
.Sheets(4).Range("A8").CopyFromRecordset rstb
.Sheets(4).Range("A2") = "Returned to host since " & Format([Forms]![External Reporting]![txtstdate],
"dddd d mmmm yyyy"
.Sheets(5).Range("A8").CopyFromRecordset rsti
.Sheets(5).Range("A2") = "Discharged since " & Format([Forms]![External Reporting]![txtstdate],
"dddd d mmmm yyyy"

End Wit

I want the procedure to run without showing Excel on screen (I want to have a form open in Access saying, "Please wait whilst the report is emailed" - or something). However, by removing the code (lines above with loads of ///// symbols) that makes Excel visible, I get the following error

run-time error '-2147417851 (80010105)
Method 'CopyFromRecordset' of object 'Range' failed

Does anybody know how I can run this part of the procedure without Excel showing up on screen

Thank you for any suggestions

TC, thanks for your help earlier - it was very useful and I am using the application quit method that you suggested. I hope the info I have given here will make this problem clearer to you

Muchas! Basil
 
Hiya

I won't waffle this time... promise! Using Office 97 on Win 2000

I've finally found a way to keep Excel hidden. Instead of using 'Set XLWorkbook = GetObject(TargetFile)' to open the workbook, if I use

Set XLObject = CreateObject("Excel.Application"
Set XLWorkbook = XLObject.Workbooks.Open(TargetFile

It seems to allow me to copyfromrecordset without making Excel visible (which it didn't before). However, it now gives me a new problem
Even though I set XLWorkbook = Nothing before quitting XLObject, it seems to play havoc with Excel from that point on

Although no error message is shown, after running the procedure if I try and open any Excel Workbook it does open up, but Excel will only show it's toolbars and be transparent where the workbook should be displayed (it is not hidden)

Is anybody aware of this problem/what may have caused it - it MUST be something to do with the new method I have taken for opening/quitting Excel through the Access module

Please help..

Basi

----- Basil wrote: ----

Hiya, I posted this query a while back and got some helpful tips from TC. Have been a way for a while so I thought I'd re-post it with further detail

I am running a procedure in Access that will create and open 4 recordsets... it will then copy a master Excel workbook to a target file, open this target file and go through 4 different sheets copying the 4 recordsets respectively. It then also emails the file (using the XLWorkbook.SendMail method) to an address held in a variable

I managed to get this to work beautifully - but only by making Excel visible. Here is the relevant part of my code

FileCopy Master, TargetFil

Set XLWorkbook = GetObject(TargetFile

With XLWorkboo
.Application.Visible = True '/////////////////////////////////////////////////////////
.Parent.windows(1).Visible = True '/////////////////////////////////////////////////////////
.Sheets(2).Range("A9").CopyFromRecordset rstre
.Sheets(2).Range("A2") = "Returned to host since " & Format([Forms]![External Reporting]![txtstdate],
"dddd d mmmm yyyy"
.Sheets(3).Range("A9").CopyFromRecordset rstpreo
.Sheets(3).Range("A2") = "Returned to host since " & Format([Forms]![External Reporting]![txtstdate],
"dddd d mmmm yyyy"
.Sheets(4).Range("A8").CopyFromRecordset rstb
.Sheets(4).Range("A2") = "Returned to host since " & Format([Forms]![External Reporting]![txtstdate],
"dddd d mmmm yyyy"
.Sheets(5).Range("A8").CopyFromRecordset rsti
.Sheets(5).Range("A2") = "Discharged since " & Format([Forms]![External Reporting]![txtstdate],
"dddd d mmmm yyyy"

End Wit

I want the procedure to run without showing Excel on screen (I want to have a form open in Access saying, "Please wait whilst the report is emailed" - or something). However, by removing the code (lines above with loads of ///// symbols) that makes Excel visible, I get the following error

run-time error '-2147417851 (80010105)
Method 'CopyFromRecordset' of object 'Range' failed

Does anybody know how I can run this part of the procedure without Excel showing up on screen

Thank you for any suggestions

TC, thanks for your help earlier - it was very useful and I am using the application quit method that you suggested. I hope the info I have given here will make this problem clearer to you

Muchas! Basil
 
Additional point - after the new procedure runs, if I go into windows task manager - I generally see that Excel is still in the processes bit. If I end the process from task manager and re-open the workbook - it works

Is there a way that I can get rid of the Excel process through VBA - coz .Quit just doesn't do it for me

Since the Excel instance will never be made visible, leaving the user to manually quit is not an option

Baz
 
Think I got it...
have to type this after xlobject.Quit:

Set XLObject = Nothing

Otherwise Excel.exe remains in the processes.

It's actually taken nearly a month to sort out all this stuff!!!! I ain't even excited that I got it now coz I know something will go Microsoft weird on me again shortly.
 
Back
Top