By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,327 Members | 1,840 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,327 IT Pros & Developers. It's quick & easy.

Can't Close Excel Process

P: 40
Hi everyone,

I open an excel document, do some manipulation to it, and then close it through VBA in Access and free the references to it. When it closes, however, the process doesn't end and I have trouble opening it again later.

I've looked at other postings and seen that I should add the following:

System.Runtime.InteropServices.Marshal.ReleaseComO bject(appExcel)
GC.Collect()

I'm new at this, and just stuck those in:

Expand|Select|Wrap|Line Numbers
  1. Dim appExcel As Excel.Application
  2. Dim wbk As Excel.Workbook
  3. Dim wks As Excel.Worksheet
  4. //
  5. Set appExcel = Excel.Application
  6. Set wbk = appExcel.Workbooks.Open(starget)
  7. //
  8.  
  9. wbk.Save
  10. wbk.Close
  11. Set wks = Nothing
  12. Set wbk = Nothing
  13. appExcel.Quit
  14. Set appExcel = Nothing
  15.  
  16. System.Runtime.InteropServices.Marshal.ReleaseComO bject(appExcel)
  17. GC.Collect()

I get the error "Sub or Function not defined" and it highlights "bject". I also get "Variable not defined" for GC. I'm not sure if they go in the Dim statement at the start of my code, and if so, what they are Dimed as.

Any help?

Robin
Sep 5 '07 #1
Share this Question
Share on Google+
32 Replies


Rabbit
Expert Mod 10K+
P: 12,392
You have a space between O and bject.
Sep 5 '07 #2

P: 40
Thanks,

When I removed it gave me the error: Variable not defined, highlighting "System"

You have a space between O and bject.
Sep 5 '07 #3

Rabbit
Expert Mod 10K+
P: 12,392
As far as I can tell from a quick google search, the System object is not a part of Visual Basic for Applications, it is a part of Visual Basic. They are not the same breed. VBA is what Microsoft Office Applications use. I'll see if any of the other experts know how to do this in VBA.
Sep 5 '07 #4

P: 40
Great, thanks. It has been a problem for a number of functions and subs that I have working in our database. We export a lot into different Excel spreadsheets, and I can't seem to figure out how to have the Excel process close completely. As it is now, I have to manually close the process, or I cannot open Excel at all.



As far as I can tell from a quick google search, the System object is not a part of Visual Basic for Applications, it is a part of Visual Basic. They are not the same breed. VBA is what Microsoft Office Applications use. I'll see if any of the other experts know how to do this in VBA.
Sep 5 '07 #5

FishVal
Expert 2.5K+
P: 2,653
Try to instantiate Excel.Application with
Expand|Select|Wrap|Line Numbers
  1. Set appExcel = CreateObject("Excel.Application")
  2.  
instead of
Expand|Select|Wrap|Line Numbers
  1. Set appExcel = Excel.Application
  2.  
Sep 5 '07 #6

Denburt
Expert 100+
P: 1,356
I use your method quite often except I do not use the "System.Runtime.InteropServices.Marshal.ReleaseCom O bject(appExcel)
GC.Collect()"

That appears to be if you are manipulating it from dot net.

I don't know what it is your trying to do so it is hard to try and comment without more answers. Are you trying to manipulate the workbook your currently in or another workbook?

Fish made a valuable suggestion however if you are manipulating cells in a workbook you are in then the following should be more than sufficient.

(Sample Snippet)
Expand|Select|Wrap|Line Numbers
  1. ACnt=1
  2. ACell = Trim(Cells(ACnt, 2))
  3. BoolmyBld4 = True
  4. 'The following line speeds up lengthy process. Don't forget to turn it back on
  5. Application.ScreenUpdating = False
  6. Worksheets(1).Select
  7. Cells(1, 1).Select
  8. Do Until ACell = "Quit2"
  9. ACell="Quit2"
  10. loop
  11. Application.ScreenUpdating = True
  12.  
  13.  
Let us know how it goes.
Sep 5 '07 #7

NeoPa
Expert Mod 15k+
P: 31,661
Check out Application Automation for a template of how to do this.
Sep 6 '07 #8

P: 40
Thanks for the help, but I'm not sure what to use in Denburt's sample. I added the screenupdating property, but I'm not sure what else in there will work for me, forgive my ignorance (I've only been doing this for a couple of months and I'm learning off of an existing database, so the code is often more advanced than my knowledge and I'm constantly playing catchup).

To clarify what I'm doing, from Access I open an existing Excel workbook, add formula, formatting and comments to cells specified by the code, and then save and close the workbook.

I looked at the link NeoPa sent, and it seems I've done everything in it. I'm probably missing something simple. I tried opening the document afterwords with Shell, and that allows me to actually open it (previously I had to actually close down the process before it would let me open the worksheet from Excel). But that doesn't solve the problem of the unclosed Excel process.




I use your method quite often except I do not use the "System.Runtime.InteropServices.Marshal.ReleaseCom O bject(appExcel)
GC.Collect()"

That appears to be if you are manipulating it from dot net.

I don't know what it is your trying to do so it is hard to try and comment without more answers. Are you trying to manipulate the workbook your currently in or another workbook?

Fish made a valuable suggestion however if you are manipulating cells in a workbook you are in then the following should be more than sufficient.

(Sample Snippet)
Expand|Select|Wrap|Line Numbers
  1. ACnt=1
  2. ACell = Trim(Cells(ACnt, 2))
  3. BoolmyBld4 = True
  4. 'The following line speeds up lengthy process. Don't forget to turn it back on
  5. Application.ScreenUpdating = False
  6. Worksheets(1).Select
  7. Cells(1, 1).Select
  8. Do Until ACell = "Quit2"
  9. ACell="Quit2"
  10. loop
  11. Application.ScreenUpdating = True
  12.  
  13.  
Let us know how it goes.
Sep 6 '07 #9

Denburt
Expert 100+
P: 1,356
I can't imagine what you may be doing wrong one quick fix may be to check and see if Excel is still running after you close the first instance then close that instance. It appears you are opening two instances somehow.

Set xlApp = GetObject(, "Excel.Application")
xlApp.quit
Sep 6 '07 #10

P: 40
This allows me to open the Excel workbook, something I couldn't do before; however, it still doesn't close the process down, which causes further problems when using Excel later.

I'm pasting in my whole VBA code, maybe there's some mistake I've made in there that I can't see.
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
  3. Private Sub MeetingScheduler_Click()
  4.  
  5. Dim appExcel As Excel.Application
  6. Dim wbk As Excel.Workbook
  7. Dim wks As Excel.Worksheet
  8. Dim dDate As Date
  9. Dim sLanguage As String
  10. Dim str As String
  11. Dim db As dao.Database
  12. Dim rst As Recordset
  13. Dim starget As String
  14. Dim iTargetColumn As Integer
  15. Dim iTargetRow As Integer
  16. Dim i As Integer
  17. Dim rRange As Range
  18. Dim sTargetName As String
  19. Dim iWkday As Integer
  20. Dim iRangeName As Integer
  21. Dim sRangeName As String
  22.  
  23. DoCmd.Hourglass True
  24.  
  25. If Me.Language.Value = 1 Then sLanguage = "Arabic"
  26. If Me.Language.Value = 2 Then sLanguage = "Bengali"
  27. If Me.Language.Value = 3 Then sLanguage = "Chinese"
  28. If Me.Language.Value = 4 Then sLanguage = "HaitianCreole"
  29. If Me.Language.Value = 5 Then sLanguage = "Korean"
  30. If Me.Language.Value = 6 Then sLanguage = "Russian"
  31. If Me.Language.Value = 7 Then sLanguage = "Spanish"
  32. If Me.Language.Value = 8 Then sLanguage = "Urdu"
  33.  
  34. str = "SELECT tblLanguage.LanguageID, tblLanguage.Language, tblName.NameID, qryFullName.Name "
  35. str = str & "FROM (tblName INNER JOIN qryFullName ON tblName.NameID = qryFullName.NameID) INNER JOIN tblLanguage ON tblName.LanguageTeam = tblLanguage.LanguageID "
  36. str = str & "WHERE (((tblLanguage.LanguageID)=" & Me.Language.Value & "));"
  37.  
  38. Set db = CurrentDb
  39. Set rst = db.OpenRecordset(str)
  40. rst.MoveLast
  41. rst.MoveFirst
  42.  
  43. starget = "L:\Translation Unit\LanguageSpreadsheets\" & sLanguage
  44. Set appExcel = CreateObject("Excel.Application")
  45. Set wbk = appExcel.Workbooks.Open(starget)
  46. appExcel.Visible = False
  47. appExcel.ScreenUpdating = False
  48.  
  49. Range("A1").Activate
  50.  
  51. iRangeName = 1 ' Names the range, enabling easy location and deletion of old meetings.
  52.  
  53. Do Until rst.EOF
  54.     sTargetName = rst.Fields("Name").Value 'Select the translator's name.
  55.  
  56.     'Sets start day of week and date of meeting.
  57.     iWkday = Abs(Weekday(Date) - Me.DayOption.Value)
  58.     If Weekday(Date) > Me.DayOption.Value Then dDate = Date + (7 - iWkday)
  59.     If Weekday(Date) < Me.DayOption.Value Then dDate = Date + iWkday
  60.  
  61.     For dDate = dDate To (DateAdd("d", 21, dDate)) Step 7 ' this line loops the code to add meeting date every week, to change number of meetings, change middle number, each multiple of 7 is one weekly meeting.
  62.         iTargetColumn = Selection.Cells.Find(what:=dDate, after:=ActiveCell, lookin:=xlFormulas, _
  63.                     lookat:=xlPart, searchorder:=xlByRows, SearchDirection:=xlNext).Column
  64.  
  65.         sRangeName = "Meeting" & iRangeName
  66.  
  67.         'Deletes previous meetings from calendar.
  68.         If NamedRangeExists(sRangeName) = True Then
  69.             Range(sRangeName).Select
  70.             Selection.Formula = ""
  71.             Selection.Interior.Color = RGB(255, 255, 255)
  72.             Selection.Borders(xlEdgeLeft).LineStyle = xlNone
  73.             Selection.Borders(xlEdgeRight).LineStyle = xlNone
  74.             Selection.Borders(xlEdgeTop).LineStyle = xlNone
  75.             Selection.Borders(xlEdgeBottom).LineStyle = xlNone
  76.             Selection.ClearComments
  77.             Names(sRangeName).Delete
  78.         End If
  79.  
  80.         i = 3           'set up the insert row counter
  81.         iTargetRow = 3  'set up the row to start with
  82.  
  83.         If Cells(3, 1).Formula = "" Then 'If first cell in row blank, add TR name to create the row.
  84.         Cells(3, 1).Formula = sTargetName
  85.         End If
  86.  
  87.         ' add new meeting
  88.         Do
  89.             If sTargetName = Cells(iTargetRow, 1).Formula Then  'Checks if this row is for projects of the given translator
  90.                 i = iTargetRow
  91.                 If Cells(iTargetRow, iTargetColumn).Formula = "" Then
  92.                     Call TaskFormatter(iTargetRow, iTargetColumn, sRangeName)
  93.                     Exit Do
  94.                 Else
  95.                     iTargetRow = iTargetRow + 1
  96.                 End If
  97.             ElseIf Cells(iTargetRow, 1).Formula = "" Then 'If this row is blank.
  98.                 Cells(i + 1, 1).Select
  99.                 Selection.EntireRow.Insert
  100.                 Selection.EntireRow.Select
  101.                 Selection.ClearFormats
  102.                 Cells(i + 1, 1).Formula = sTargetName
  103.                 Call TaskFormatter(i + 1, iTargetColumn, sRangeName)
  104.                 Exit Do
  105.             Else
  106.                 iTargetRow = iTargetRow + 1 'If row is not blank or has translator's name, move down to next row and check again.
  107.             End If
  108.         Loop
  109.         iRangeName = iRangeName + 1
  110.     Next
  111. rst.MoveNext 'go to next translator in language team
  112. iRangeName = iRangeName - 4 + 100 'reset the range name # and add 100, each additional person will begin with a different digit.
  113. Loop
  114.  
  115. 'Close down and cleanup everything
  116. Call wbk.Save
  117. Call wbk.Close
  118. appExcel.Visible = True
  119. appExcel.ScreenUpdating = True
  120. Call appExcel.Quit
  121. Set rst = Nothing
  122. Set db = Nothing
  123. Set wks = Nothing
  124. Set wbk = Nothing
  125. Set appExcel = Nothing
  126. 'b/c Excel process doesn't close, must be opened and closed a second time.
  127. Set appExcel = GetObject(, "Excel.Application")
  128. Call appExcel.Quit
  129. Set appExcel = Nothing
  130. DoCmd.Hourglass False
  131.  
  132. MsgBox ("Meetings have been scheduled.")
  133.  
  134. End Sub
  135. _____________________________
  136. Private Sub TaskFormatter(iTargetRow As Integer, iTargetColumn As Integer, sRangeName As String)
  137. Dim cComment As Comment
  138. Dim rRange As Range
  139. 'Dim i As Integer
  140. Dim sDay As String
  141. Dim stime As String
  142.  
  143. If Me.DayOption.Value = 2 Then sDay = "Monday"
  144. If Me.DayOption.Value = 3 Then sDay = "Tuesday"
  145. If Me.DayOption.Value = 4 Then sDay = "Wednesday"
  146. If Me.DayOption.Value = 5 Then sDay = "Thursday"
  147. If Me.DayOption.Value = 6 Then sDay = "Friday"
  148.  
  149. If Me.TimeOption.Value = 1 Then stime = "10 am-11 am"
  150. If Me.TimeOption.Value = 2 Then stime = "11 am-12 pm"
  151. If Me.TimeOption.Value = 3 Then stime = "12 pm-1 pm"
  152. If Me.TimeOption.Value = 4 Then stime = "1 pm-2 pm"
  153. If Me.TimeOption.Value = 5 Then stime = "2 pm-3 pm"
  154. If Me.TimeOption.Value = 6 Then stime = "3 pm-4 pm"
  155. If Me.TimeOption.Value = 7 Then stime = "4 pm-5 pm"
  156.  
  157. 'Set name of range
  158. Set rRange = Cells(iTargetRow, iTargetColumn)
  159. rRange.Select
  160. rRange.Name = sRangeName
  161.  
  162. 'color the cells
  163. rRange.Select
  164. Selection.BorderAround LineStyle:=xlContinuous
  165. Selection.Font.Name = "Arial Narrow"
  166. Selection.Font.Size = 9
  167. Selection.Interior.Color = RGB(0, 0, 0)
  168. Selection.Font.Color = RGB(255, 255, 255)
  169.  
  170. 'add cell content
  171. Cells(iTargetRow, iTargetColumn).Activate
  172. ActiveCell.Formula = "Meeting"
  173.  
  174. 'Add meeting info in comment
  175. Set cComment = ActiveCell.Comment
  176. If cComment Is Nothing Then
  177.     ActiveCell.AddComment Text:="Meeting on " & sDay & " from " & stime
  178. End If
  179.  
  180. End Sub
Sep 6 '07 #11

NeoPa
Expert Mod 15k+
P: 31,661
Robin,

I suggest that you create a 'debug' version of your code which has simply the relevant lines requiring testing.
Strip out all the code that is not relevant to the problem at hand and test that it still performs in the same way (exhibits the same problem). When you have this code then post it in here and we can look at it for you (If it is reproducible elsewhere that will obviously increase the help we can provide).

Obviously don't scrap your original. This is a test version only. Hopefully, when we find the problem, your original code can then be updated to include the properly working code.
Sep 6 '07 #12

P: 40
Will do, working on it.
Sep 6 '07 #13

P: 40
OK,

Here's the debug code, and a description of what's happening on my machine when I use it.

I run it from a command button on a blank form. After the code is run, the Excel process remains active in the Task Manager. When I go to open the Excel workbook "Test", it opens fine, and the word "Test" has been added in the first cell. However, after closing Excel, an Excel process is still active. When I run the code again, a second Excel process opens, and I get the run-time error 1004, "Method 'Range' of object '_Global' failed, highlighting "Range("A1").Activate" in my code.
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
  3. Public Sub Testit()
  4.  
  5. Dim appExcel As Excel.Application
  6. Dim wbk As Excel.Workbook
  7. Dim wks As Excel.Worksheet
  8. Dim starget As String
  9.  
  10. DoCmd.Hourglass True
  11.  
  12. starget = "C:\test"
  13. Set appExcel = CreateObject("Excel.Application")
  14. Set wbk = appExcel.Workbooks.Open(starget)
  15. appExcel.Visible = False
  16. appExcel.ScreenUpdating = False
  17.  
  18. Range("A1").Activate
  19.  
  20. Cells(1, 1).Activate
  21. ActiveCell.Formula = "Test"
  22.  
  23. Call wbk.Save
  24. Call wbk.Close
  25. appExcel.Visible = True
  26. appExcel.ScreenUpdating = True
  27. Call appExcel.Quit
  28.  
  29. Set wks = Nothing
  30. Set wbk = Nothing
  31. Set appExcel = Nothing
  32. DoCmd.Hourglass False
  33.  
  34. MsgBox ("Test complete.")
  35.  
  36.  
  37. End Sub
Sep 6 '07 #14

Denburt
Expert 100+
P: 1,356
O.K. enough of this thread sorry I was unable to test it sooner. I ran your code and made changes until it performed as expected I am sure all of the changes were not necessary but they are there now so if you would like you can change them back one by one till it breaks. But I think the problem was that you were not explicitly calling the application then the workbook when you opened your spreadsheet. I also made sure I specified the application when referring to cells etc. The following worked fine on this end how about on yours?


Expand|Select|Wrap|Line Numbers
  1. Public Sub Testit()
  2.  
  3. Dim appExcel As Excel.Application
  4. Dim wbk As Workbook
  5. Dim wks As Worksheet
  6. Dim starget As String
  7.  
  8. DoCmd.Hourglass True
  9.  
  10. starget = "C:\test"
  11. Set appExcel = CreateObject("Excel.Application")
  12. Set wbk = appExcel.Workbooks.Open(starget)
  13. appExcel.Visible = False
  14. 'appExcel.ScreenUpdating = False
  15.  
  16. appExcel.Range("A1").Activate
  17.  
  18. appExcel.Cells(1, 1).Activate
  19. appExcel.ActiveCell.Formula = "Test"
  20.  
  21. wbk.Save
  22. wbk.Close
  23. appExcel.Visible = True
  24. 'appExcel.ScreenUpdating = True
  25. appExcel.Quit
  26.  
  27. Set wks = Nothing
  28. Set wbk = Nothing
  29. Set appExcel = Nothing
  30. DoCmd.Hourglass False
  31.  
  32. MsgBox ("Test complete.")
  33.  
  34.  
  35. End Sub
Good luck and Happy Coding!
Sep 6 '07 #15

NeoPa
Expert Mod 15k+
P: 31,661
Will do, working on it.
Firstly, congratulations on doing that. It simplified the whole thing enormously. I expect it helped to focus your mind on the problem too.

Secondly, I can say that I was able to reproduce the problem you had with your code and this problem also certainly went away when running Denburt's.

I'm interested in finding out what, specifically, causes this behaviour so I may post again soon.
Sep 7 '07 #16

NeoPa
Expert Mod 15k+
P: 31,661
It seems it's to do with the order of your ...
Expand|Select|Wrap|Line Numbers
  1. .Visible = True
  2. .ScreenUpdating = True
...lines. When I changed them around to ...
Expand|Select|Wrap|Line Numbers
  1. .ScreenUpdating = True
  2. .Visible = True
...(as the reverse of ...
Expand|Select|Wrap|Line Numbers
  1. .Visible = False
  2. .ScreenUpdating = False
...) that worked fine. Notice how the onion is unwrapped here.

My final and working code was ...
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Public Sub TestIt()
  5.     Dim appExcel As Excel.Application
  6.     Dim wbk As Excel.Workbook
  7.     Dim wks As Excel.Worksheet
  8.     Dim sTarget As String
  9.  
  10.     Call DoCmd.Hourglass(True)
  11.     sTarget = "C:\Test.Xls"
  12.     Set appExcel = CreateObject("Excel.Application")
  13.     With appExcel
  14.         Set wbk = .Workbooks.Open(sTarget)
  15.         Set wks = wbk.ActiveSheet
  16.         .Visible = False
  17.         .ScreenUpdating = False
  18.  
  19.         wks.Range("A1").Activate
  20.         'Cells(1, 1).Activate
  21.         .ActiveCell.Formula = "TestIt"
  22.  
  23.         Call wbk.Save
  24.         Call wbk.Close
  25.         .ScreenUpdating = True
  26.         .Visible = True
  27.         Call .Quit
  28.     End With
  29.  
  30.     Set wks = Nothing
  31.     Set wbk = Nothing
  32.     Set appExcel = Nothing
  33.     DoCmd.Hourglass False
  34.     MsgBox ("Test complete.")
  35. End Sub
Sep 7 '07 #17

Denburt
Expert 100+
P: 1,356
Nicely done Neo and thanks, that will help me tremendously in the future as I code between the two constantly. I just finished a MAJOR macro in Excel I wanted to do it in Access and store the data but they want everything reformatted and totaled in Excel. I think the amount of data they want to rearrange will be to much for excel so that will be interesting. These tips will definitely add to that project.

I hope this helps you as well Robin and thanks for asking this question it will help prevent future hazards here at my workplace for sure.

Again thanks,
DB
Sep 7 '07 #18

P: 2
I can't imagine what you may be doing wrong one quick fix may be to check and see if Excel is still running after you close the first instance then close that instance. It appears you are opening two instances somehow.

Set xlApp = GetObject(, "Excel.Application")
xlApp.quit

In VB 6.0, how do you check and see if Excel.exe is already running? I would like to do this in my application but do not know the syntax. I want to check and see if Excel.exe is running before I start any of my own logic.
Sep 7 '07 #19

P: 40
Denburt and Neo:
Thanks, explictly calling appExcel for each command (or using With) worked great in the debug code, but in the original code as I put it piece-by piece back together, it looks like I'm stuck on the following line:
Expand|Select|Wrap|Line Numbers
  1. iTargetColumn = Selection.Cells.Find(what:=dDate, after:=ActiveCell, lookin:=xlFormulas, lookat:=xlPart, searchorder:=xlByRows, SearchDirection:=xlNext).Column
where iTargetColumn is an integer, and dDate is set as Date, to correspond with the first row of the workbook (which is consecutive dates). I put iTargetColumn in the code as:

Expand|Select|Wrap|Line Numbers
  1. with appExcel
  2. .Cells(1, iTargetColumn).Activate

When I put this in the debug code, this is where it is causing the Excel process to remain open. I'm not sure if within that line of code how to explicitly call appExcel.

Sorry to put you guys through the ringer! You're helping me out a ton.

Robin
Sep 7 '07 #20

Denburt
Expert 100+
P: 1,356
Robin have you tried using a range instead of a selection as in the help file?
Highlight the word "find" then hit F1 and you can see the full range of this topic.
Example
This example finds all cells in the range A1:A500 on worksheet one that contain the value 2 and changes it to 5.

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Sep 7 '07 #21

Denburt
Expert 100+
P: 1,356
In VB 6.0, how do you check and see if Excel.exe is already running? I would like to do this in my application but do not know the syntax. I want to check and see if Excel.exe is running before I start any of my own logic.

Lisa this should be a thread of it's own although I understand why you posted it here this is called hijacking a thread and is a big no-no.

To answer your question though I am pretty sure it is the same I did a few quick searches to be sure and found this thread. Hopefully it helps.

http://www.thescripts.com/forum/thread168253.html
Sep 7 '07 #22

NeoPa
Expert Mod 15k+
P: 31,661
Nicely done Neo and thanks, that will help me tremendously in the future as I code between the two constantly. I just finished a MAJOR macro in Excel I wanted to do it in Access and store the data but they want everything reformatted and totaled in Excel. I think the amount of data they want to rearrange will be to much for excel so that will be interesting. These tips will definitely add to that project.

I hope this helps you as well Robin and thanks for asking this question it will help prevent future hazards here at my workplace for sure.

Again thanks,
DB
Firstly, I need to catch up with all these posts since Friday as I was away.

Den,
I'm very pleased this was helpful. I do almost as much work in Excel as I do in Access so if you have any queries on that score, please feel free to ask (be sure to bring my attention to any thread as I don't get to go through them all as a matter of course any more I'm afraid).
I don't do much in App Automation mind, but Excel is an area of expertise you could say.

On to the other comments ;)
Sep 10 '07 #23

NeoPa
Expert Mod 15k+
P: 31,661
Denburt and Neo:
Thanks, explictly calling appExcel for each command (or using With) worked great in the debug code, but in the original code as I put it piece-by piece back together, it looks like I'm stuck on the following line:
Expand|Select|Wrap|Line Numbers
  1. iTargetColumn = Selection.Cells.Find(what:=dDate, after:=ActiveCell, lookin:=xlFormulas, lookat:=xlPart, searchorder:=xlByRows, SearchDirection:=xlNext).Column
where iTargetColumn is an integer, and dDate is set as Date, to correspond with the first row of the workbook (which is consecutive dates). I put iTargetColumn in the code as:

Expand|Select|Wrap|Line Numbers
  1. with appExcel
  2. .Cells(1, iTargetColumn).Activate

When I put this in the debug code, this is where it is causing the Excel process to remain open. I'm not sure if within that line of code how to explicitly call appExcel.

Sorry to put you guys through the ringer! You're helping me out a ton.

Robin
Robin,

You did such good work before in stripping out unnecessary code. Here we need some context to know what you're talking about (I certainly do). If DenBurt's last post doesn't get you sorted, you'll need to repost this info so that we (at least I) can understand what you're talking about.

Good luck.
Sep 10 '07 #24

Denburt
Expert 100+
P: 1,356
Robin,

You did such good work before in stripping out unnecessary code. Here we need some context to know what you're talking about (I certainly do). If DenBurt's last post doesn't get you sorted, you'll need to repost this info so that we (at least I) can understand what you're talking about.

Good luck.

Rereading this I thought of something that may be useful. This is off the top of my head so I apologize for any errors. Instead of selecting then finding formating etc. Use something like the following:
Expand|Select|Wrap|Line Numbers
  1. with appExcel 'you may need to specify a worksheet or workbook or both
  2.       iTargetColumn = .Cells.Find(what:=dDate, after:=appExcel.Cells(1,1), lookin:=xlFormulas, lookat:=xlPart, searchorder:=xlByRows, SearchDirection:=xlNext).Column
  3.  
Hope this helps some.
Sep 10 '07 #25

P: 40
Hi guys

I just got back to working on this, and I had to go through everything in my code to see if your suggestions would work with everything in it. Sorry it took a while.

So I started putting my code back together based off of the stripped down version. Denburt's suggestion helped, I changed the find method to search for Range, instead of cells. But as I put the code together, it looks like I'm having difficulty now when I try to loop through my different recordset selections.

Basically, I pull up a recordset that has 3-5 different names (see rst.Fields("Name") below). I run this code placing the word "Test" in a column corresponding to the date and row corresponding to the name. If the row is not named in the first column, the rst Name goes in there. When it runs through the code once, Excel seems to close fine. But once it loops through, the process doesn't close. Is somehow my loop not complete so that it's leaving something open?

Expand|Select|Wrap|Line Numbers
  1. starget = "C:\test"
  2. Set appExcel = CreateObject("Excel.Application")
  3. Set wbk = appExcel.Workbooks.Open(starget)
  4. Set wks = wbk.Worksheets(1)
  5. With appExcel
  6.     .ScreenUpdating = False
  7.     .Visible = False
  8.     .Range("A1").Activate
  9.  
  10. Do Until rst.EOF
  11.     sTargetName = rst.Fields("Name").Value
  12.  
  13.     iTargetColumn = wks.Range("A1:z1").Find(dDate).Column
  14.     iTargetRow = 3
  15.     Do
  16.         If sTargetName = .Cells(iTargetRow, 1).Formula Then
  17.             If .Cells(iTargetRow, iTargetColumn).Formula = "" Then
  18.                 .Cells(iTargetRow, iTargetColumn).Formula = "Test"
  19.                 Exit Do
  20.             Else
  21.                 iTargetRow = iTargetRow + 1
  22.             End If
  23.         ElseIf .Cells(iTargetRow, 1).Formula = "" Then
  24.             .Cells(iTargetRow, 1).Formula = sTargetName
  25.             .Cells(iTargetRow, iTargetColumn).Formula = "Test"
  26.             Exit Do
  27.         Else
  28.             iTargetRow = iTargetRow + 1
  29.         End If
  30.     Loop
  31. rst.MoveNext
  32. Loop
  33. End With
  34.  
  35. wbk.Save
  36. wbk.Close
  37. appExcel.Visible = True
  38. appExcel.ScreenUpdating = True
  39. appExcel.Quit
Sep 10 '07 #26

Denburt
Expert 100+
P: 1,356
See post 17 it appears that Neo managed to nail it down in that post....

O.K. read his post but simply reverse the following lines in your code in both places:

.ScreenUpdating = True
.Visible = True

Hope that helps. :)
Sep 10 '07 #27

P: 40
Yes! It works, wonderful. But...

...my original code calls a separate function in the Do, instead of:
Expand|Select|Wrap|Line Numbers
  1. .Cells(iTargetRow, iTargetColumn).Formula = "Test"
it does the following function, which places and formats the cell in the appropriate way. It's not a lot, so I could do without, but this same code structure is used at different places in the database, which perform more complex methods, and I have the same problem closing the excel process there. Any ideas here?
Expand|Select|Wrap|Line Numbers
  1. Public Function MeetingPlacer(wks As Worksheet, iTargetRow As Integer, iTargetColumn As Integer)
  2. wks.Cells(iTargetRow, iTargetColumn).Activate
  3. With ActiveCell
  4.     .BorderAround LineStyle:=xlContinuous
  5.     .Font.Size = 9
  6.     .Font.Name = "Arial Narrow"
  7.     .Font.Color = RGB(255, 255, 255)
  8.     .Interior.Color = RGB(0, 0, 0)
  9.     .Formula = "Meeting"
  10. End With
  11.  
  12. End Function
Sep 10 '07 #28

Denburt
Expert 100+
P: 1,356
Hey glad were moving along... :)

Lets start here:
wks As Worksheet O.K. where is the app? Get that and you should be good.
Sep 10 '07 #29

NeoPa
Expert Mod 15k+
P: 31,661
Yes! It works, wonderful. But...

...my original code calls a separate function in the Do, instead of:
Expand|Select|Wrap|Line Numbers
  1. .Cells(iTargetRow, iTargetColumn).Formula = "Test"
it does the following function, which places and formats the cell in the appropriate way. It's not a lot, so I could do without, but this same code structure is used at different places in the database, which perform more complex methods, and I have the same problem closing the excel process there. Any ideas here?
Expand|Select|Wrap|Line Numbers
  1. Public Function MeetingPlacer(wks As Worksheet, iTargetRow As Integer, iTargetColumn As Integer)
  2.   wks.Cells(iTargetRow, iTargetColumn).Activate
  3.   With ActiveCell
  4.     .BorderAround LineStyle:=xlContinuous
  5.     .Font.Size = 9
  6.     .Font.Name = "Arial Narrow"
  7.     .Font.Color = RGB(255, 255, 255)
  8.     .Interior.Color = RGB(0, 0, 0)
  9.     .Formula = "Meeting"
  10.   End With
  11. End Function
Robin,

You're doing a fairly good job of complying with our requests, which makes our job less of a strain. Could I ask you to remember to use the [CODE] tags in future when you post code. This saves us from redoing all your posts and will give us more time to help you and others like you with their real problems.

As to your ongoing problems, I would like to help more but I confess I still don't seem to have from you a clear indication of what's happening where. I will offer some suggestions, but without more clear indication of what's what I can't direct my answer very well.

Firstly, in case your problem is connected with the earlier issue, I considered taking out the :
Expand|Select|Wrap|Line Numbers
  1. ScreenUpdating = False
  2. and
  3. ScreenUpdating = True
... lines completely. If this doesn't effect the performance adversely then it should certainly not have any other effect. It's definitely code worth using within Excel normally, but if the application window is already hidden it may not have any effect.

As to your function, try using :
Expand|Select|Wrap|Line Numbers
  1. Public Sub MeetingPlacer(wks As Worksheet, _
  2.                          iTargetRow As Integer, _
  3.                          iTargetColumn As Integer)
  4.   With wks.Cells(iTargetRow, iTargetColumn)
  5.     'Call .Activate
  6.     Call .BorderAround(LineStyle:=xlContinuous)
  7.     .Font.Size = 9
  8.     .Font.Name = "Arial Narrow"
  9.     .Font.Color = RGB(255, 255, 255)
  10.     .Interior.Color = RGB(0, 0, 0)
  11.     .Formula = "Meeting"
  12.   End With
  13. End Sub
I've changed it from a Function to a Sub as you don't seem to be providing a return value anyway.
It doesn't activate the cell it's working on. If that's required simply uncomment line #5.
Sep 11 '07 #30

P: 40
Added appExcel as Excel.Application and wbk as Workbook to the sub function, but still no success. Do I need to Dim specifically Excel again here? I'm worried if I do that, it'll open another Excel process, instead of closing it.

Here's the code again:
Expand|Select|Wrap|Line Numbers
  1. 'Here's where I call the function
  2. Call MeetingPlacer(appExcel, wbk, wks, iTargetRow, iTargetColumn)
Expand|Select|Wrap|Line Numbers
  1. Public Function MeetingPlacer(appExcel As Excel.Application, wbk As Workbook, wks As Worksheet, iTargetRow As Integer, iTargetColumn As Integer)
  2. wks.Cells(iTargetRow, iTargetColumn).Activate
  3. With ActiveCell
  4.     .BorderAround LineStyle:=xlContinuous
  5.     .Font.Size = 9
  6.     .Font.Name = "Arial Narrow"
  7.     .Font.Color = RGB(255, 255, 255)
  8.     .Interior.Color = RGB(0, 0, 0)
  9.     .Formula = "Meeting"
  10. End With
Let me post again what the code is.
Hey glad were moving along... :)

Lets start here:
wks As Worksheet O.K. where is the app? Get that and you should be good.
Sep 11 '07 #31

P: 40
Sorry, I'm still new to the forum so I didn't realize how much editing to the messages I could do.

Great news as well: I think your suggestion Neo worked, which is the best news I've had in two weeks since I've been struggling with this issue. I guess the problem was with the use of "ActiveCell"?

I'm going to go through and add everything step by step back into the code, hopefully it'll come together. Let me just say you guys have been wonderful, I used to work with an experienced Access programmer who guided me through things like this, but he recently left, and if it wasn't for you all I'd be stuck.

I'll let you know how it all comes together.

Robin

Robin,

You're doing a fairly good job of complying with our requests, which makes our job less of a strain. Could I ask you to remember to use the [CODE] tags in future when you post code. This saves us from redoing all your posts and will give us more time to help you and others like you with their real problems.

As to your ongoing problems, I would like to help more but I confess I still don't seem to have from you a clear indication of what's happening where. I will offer some suggestions, but without more clear indication of what's what I can't direct my answer very well.

Firstly, in case your problem is connected with the earlier issue, I considered taking out the :
Expand|Select|Wrap|Line Numbers
  1. ScreenUpdating = False
  2. and
  3. ScreenUpdating = True
... lines completely. If this doesn't effect the performance adversely then it should certainly not have any other effect. It's definitely code worth using within Excel normally, but if the application window is already hidden it may not have any effect.

As to your function, try using :
Expand|Select|Wrap|Line Numbers
  1. Public Sub MeetingPlacer(wks As Worksheet, _
  2.                          iTargetRow As Integer, _
  3.                          iTargetColumn As Integer)
  4.   With wks.Cells(iTargetRow, iTargetColumn)
  5.     'Call .Activate
  6.     Call .BorderAround(LineStyle:=xlContinuous)
  7.     .Font.Size = 9
  8.     .Font.Name = "Arial Narrow"
  9.     .Font.Color = RGB(255, 255, 255)
  10.     .Interior.Color = RGB(0, 0, 0)
  11.     .Formula = "Meeting"
  12.   End With
  13. End Sub
I've changed it from a Function to a Sub as you don't seem to be providing a return value anyway.
It doesn't activate the cell it's working on. If that's required simply uncomment line #5.
Sep 11 '07 #32

NeoPa
Expert Mod 15k+
P: 31,661
Sorry, I'm still new to the forum so I didn't realize how much editing to the messages I could do.
...
I'll let you know how it all comes together.

Robin
Please do let us know how you get on Robin.

Also, I'm not trying to come down on you. Far from it. You're doing a good job so far. I just wanted to bring some other matters to your attention for future questions etc.
If you look on the page that comes up when you press to reply, you will see various options that are available to you to format the text and add links and all sorts of things. These are for your benefit so are worth a look.
Selecting text then clicking on one of the buttons (CODE {#}; Quote {"}; etc) will enclose the text selected within the tags you've selected.

Have fun.
Sep 11 '07 #33

Post your reply

Sign in to post your reply or Sign up for a free account.