PDA

View Full Version : Sleeper: Can't run second times in VB code!



yihong
07-26-2005, 08:58 PM
:( Dear friends,

I used VB (excel.application) to open two excel file (One called AAA.XLS is standard code, another called BBB.XLS will get code from AAA.XLS), after finishing, I use .Quit to exit both of excel file. Then again to open another excel file CCC.XLS to get AAA.XLS code, but failed. I suspected excel or workbook or temp file didn't acutually close. Below is part of my codeing, when second time run to
destXL.Cells(iRowDest, 31) = Application.VLookup(findString, rang1, 2, False), will failed. Here destxl is the example BBB.XLS or CCC.XLS, backxl likes AAA.XLS.
I tried many times, but all failed. Anyone can help. Thanks so much!



Set backXL = New excel.Application 'SANKYU AND WYETH CODE
Set destXL = New excel.Application 'NEED ADD WYETH CODE
destXL.Workbooks.Open strDestF
destXL.Cells(1, 31) = "WYETH"
backXL.Workbooks.Open strBackF
'Do While backXL.Cells(backCount, 1) <> ""
' backCount = backCount + 1 'get how many wyeth code in template
'Loop
Do While destXL.Cells(iRowDest, 30) <> ""
Dim findString As String
Dim rang1 As Range
Set rang1 = backXL.Range("A2:B10000")
findString = destXL.Cells(iRowDest, 30)
destXL.Cells(iRowDest, 31) = Application.VLookup(findString, rang1, 2, False)
iRowDest = iRowDest + 1
sourceCount = iRowDest
Loop
destXL.Cells(sourceCount, 25) = "=SUM(Y2:Y" & sourceCount - 1 & ")"
lbxFile.Refresh
Kill (strSourceF)
MsgBox "Finish to add Wyeth Code!", vbOKOnly, "Finish"
'clean up and exit
Set oWS = Nothing
If Not oWB Is Nothing Then oWB.Close
Set oWB = Nothing
destXL.Quit
backXL.Quit
'Kill (strBackF)
btnExport.Enabled = False
Set destXL = Nothing
Set backXL = Nothing
Application.Quit
btnExport.Enabled = False
btnConfirm.Enabled = False
excel.Application.Quit
'Application.ActiveWindow.Close
'Unload Me
Exit Sub

Bob Phillips
07-27-2005, 02:34 AM
That can't be all of the code, as some of the variables are not initialised, so it is hard to say.

But three things hits me.
You don't need to have two instances of Excel started, one should be sufficient, you can open 2,3,... workbooks in the same instance.
You quit the other instances before setting them to nothing, the latter should be sufficient.
And you don't clear all objects at the end, such as Rang1.

yihong
07-27-2005, 02:49 AM
Hi,

Thank you very much for your help!
I try to define again:

'Dim destXL As excel.Application
'Dim backXL As excel.Application
Dim appXL As Excel.Application
Dim destXL As Excel.Workbooks
Dim backXL As Excel.Workbooks

Set appXL = New Excel.Application
Set backXL = appXL.Workbooks
Set destXL = appXL.Workbooks


but destxl.cell(1,31) can't be run

how to clear range1?

Regards

Bob Phillips
07-27-2005, 02:55 AM
Hi,

Thank you very much for your help!
I try to define again:

Dim destXL As excel.Application
Dim backXL As excel.Application
Dim appXL As Excel.Application
Dim destXL As Excel.Workbooks
Dim backXL As Excel.Workbooks
Set appXL = New Excel.Application
Set backXL = appXL.Workbooks
Set destXL = appXL.Workbooks


but destxl.cell(1,31) can't be run

how to clear range1?

Regards

You would still need to open the workbooks


Set backXL = appXL.Workbooks.Open(strBackF)
Set destXL = appXL.Workbooks.Open(strDestF)

Killian
07-27-2005, 03:33 AM
yes... it's difficult to see what's supposed to be happening with partial code but IMHO you do need to get the structure of the objects (excel app and workbooks) organised.
Does this help?


Sub ProcessExcelFiles()
' !!!SET A REFERENCE TO THE EXCEL OBJECT MODEL!!!
Dim xlApp As New Excel.Application
Dim backXL As Excel.Workbook
Dim destXL As Excel.Workbook
' set object references to each workbook
Set backXL = xlApp.Workbooks.Open(strBackF)
Set destXL = xlApp.Workbooks.Open(strDestF)
' do some stuff with your workbooks
Do While destXL.Cells(iRowDest, 30) <> ""
' when working with a workbook object, you should also
' refer to the worksheet you want you operate with
' e.g.
' Do While destXL.Worksheets("Sheet1").Cells(iRowDest, 30).Text <> ""
Loop
' finished with the files
' close files without saving and quit Excel
backXL.Close False
destXL.Close False
xlApp.Quit
Set backXL = Nothing
Set destXL = Nothing
Set xlApp = Nothing
End Sub

yihong
07-27-2005, 05:13 PM
yes... it's difficult to see what's supposed to be happening with partial code but IMHO you do need to get the structure of the objects (excel app and workbooks) organised.
Does this help?Sub ProcessExcelFiles()
' !!!SET A REFERENCE TO THE EXCEL OBJECT MODEL!!!

Dim xlApp As New Excel.Application
Dim backXL As Excel.Workbook
Dim destXL As Excel.Workbook

' set object references to each workbook
Set backXL = xlApp.Workbooks.Open(strBackF)
Set destXL = xlApp.Workbooks.Open(strDestF)


' do some stuff with your workbooks
Do While destXL.Cells(iRowDest, 30) <> ""
' when working with a workbook object, you should also
' refer to the worksheet you want you operate with
' e.g.
' Do While destXL.Worksheets("Sheet1").Cells(iRowDest, 30).Text <> ""
Loop


' finished with the files
' close files without saving and quit Excel
backXL.Close False
destXL.Close False
xlApp.Quit

Set backXL = Nothing
Set destXL = Nothing
Set xlApp = Nothing

End Sub

yihong
07-27-2005, 05:24 PM
Hi, Killian

Thank you for your help. I tried to edit code again. see below is my code:


Option Explicit
Dim strSourceP As String
Dim strDestP As String
Dim strBackP As String
Dim strOldP As String
Dim strSourceF As String
Dim strDestF As String
Dim strBackF As String
Dim strOldF As String
Dim strOldFO As String
Dim curDT As String
Dim strWorkbook As String
Dim appXL As Excel.Application
Dim destWB As Excel.Workbook
Dim backWB As Excel.Workbook
Dim destWS As Excel.Worksheet
Dim backWS As Excel.Worksheet
Dim oWS As Excel.Worksheet
Dim msg As String
Dim sourceCount As Integer

Private Sub btnCancel_Click()
Dim objform As Form
For Each objform In Forms
Unload objform
Next
End Sub

Private Sub btnConfirm_Click()
Dim answer As String
On Error GoTo errCloseFile
curDT = Format(Now, "ddmmyyhhmm")
strSourceF = lbxFile.Path + "\" + lbxFile.FileName
'frmConfirm.Show
answer = MsgBox("The file '" & lbxFile.FileName & "' choice is ok?", _
vbOKCancel, "Choice OK")
If answer <> 1 Then
strSourceF = ""
Else
' set if file open
strDestF = strDestP + Left(lbxFile.FileName, _
Len(lbxFile.FileName) - 4) & curDT & ".XLS"
strOldF = strOldP + lbxFile.FileName
FileCopy strSourceF, strOldP + lbxFile.FileName
FileCopy strSourceF, strDestF
btnExport.Enabled = True
'FileCopy strOldFO, strBackF
End If
Exit Sub
errCloseFile:
MsgBox "Please close your file", vbOKOnly, "Close File"
End Sub

Private Sub btnExport_Click()
Dim i As Integer
Dim temp1 As String
Dim backCount As Integer
Dim iRowDest As Integer
'On Error GoTo errCodeNot
i = 2
backCount = 2
iRowDest = 2
Set appXL = New Excel.Application
Set destWB = appXL.Workbooks.Open(strDestF)
Set backWB = appXL.Workbooks.Open(strBackF)
Set destWS = destWB.Worksheets("DETAIL")
Set backWS = backWB.Worksheets("WYETH")
destWS.Cells(1, 31) = "Wyeth"
Do While destWS.Cells(iRowDest, 30) <> ""
Dim findString As String
Dim rang1 As Range
Set rang1 = backWS.Range("A2:B10000")
findString = destWS.Cells(iRowDest, 30)
destWS.Cells(iRowDest, 31) = Application.VLookup(findString, rang1, 2, False)
iRowDest = iRowDest + 1
sourceCount = iRowDest
Loop
destWS.Cells(sourceCount, 25) = "=SUM(Y2:Y" & sourceCount - 1 & ")"
lbxFile.Refresh
Kill (strSourceF)
MsgBox "Finish to add Wyeth Code!", vbOKOnly, "Finish"
backWB.Close False
destWB.Close False
appXL.Quit
Set backWB = Nothing
Set destWB = Nothing
Set appXL = Nothing
'clean up and exit
'Set oWS = Nothing
'If Not oWB Is Nothing Then oWB.Close
' Set oWB = Nothing
' 'destXL.Quit
' 'backXL.Quit
' 'Kill (strBackF)
' btnExport.Enabled = False
' Set destXL = Nothing
' Set backXL = Nothing
' Application.Quit
' btnExport.Enabled = False
' btnConfirm.Enabled = False
' Excel.Application.Quit
'backXL.SaveWorkspace (strBackF)
'destXL.SaveWorkspace (strDestF)
'Exit Sub
'errCodeNot:
' MsgBox Err.Description
' 'MsgBox "The WYETH Location code '" & _
destXL.Cells(iRowDest, 30) & "' can not be found, Pls check it"
' btnExport.Enabled = False
' lbxFile.Refresh
' destXL.Workbooks.Close
' Kill (strDestF)
' Kill (strOldF)
' Set oWS = Nothing
' If Not oWB Is Nothing Then oWB.Close
' Set oWB = Nothing
' 'backXL.Quit
' 'excel.Application.ActiveWorkbook.Close(savechanges:=False, _
FileName:=strBackF) = False
' 'destXL.Quit
' btnExport.Enabled = False
' btnConfirm.Enabled = False
' Set destXL = Nothing
' Set backXL = Nothing
' Application.Quit
' Excel.Workbooks.Close
' Excel.Workbooks.Application.Quit
End Sub

Private Sub cmdFresh_Click()
lbxFile.Refresh
btnConfirm.Enabled = True
End Sub

Private Sub Form_Load()
On Local Error GoTo ErrorHandle
'lbxFile.Path = "c:\wyeth1\import"
lbxFile.Path = "c:\wyeth\import"
strSourceP = "c:\wyeth\import\"
strDestP = "c:\wyeth\export\"
strOldP = "c:\wyeth\old\"
lblImEx = "Im"
strBackP = "C:\wyeth\backup\"
lblFilename.Caption = ""
strBackF = "c:\wyeth\backup\wyeth.xls"
'strOldFO = "c:\wyeth\old\wyeth.xls"
'FileCopy strOldFO, strBackF
btnConfirm.Enabled = True
btnExport.Enabled = False
Exit Sub
ErrorHandle:
MsgBox Err.Description & Err.Number
MsgBox "Please create the fold C:\WYETH\IMPORT, _
C:\WYETH\EXPORT, C:\WYETH\BACKUP under C: Driver and press the <<Fresh>> Button"
btnConfirm.Enabled = False
btnExport.Enabled = False
cmdFresh.Enabled = False
End Sub

Private Sub lbxFile_Click()
With Clipboard
.Clear
.SetText lbxFile.FileName, vbCFText
End With
lblFilename.Caption = lbxFile.FileName
End Sub


First time run this applicaiton is well. But the second time run this application,
When run to destWS.Cells(iRowDest, 31) = Application.VLookup(findString, rang1, 2, False)
come the error message: Run-time error '1004'
Application-defined or object-defined error. The same problem is as before.

Hope to get your continue help!
Best Regards

mark007
07-27-2005, 06:12 PM
Your main problem of the multiple instances is here:

Application.VLookup(..)

As you haven't referenced your open application (appXL) VB rather annoyingly creates a hidden reference to XL and opens abother instance in memory. If you go to the task manager and check the running processes you will see this. Therfore change the code to:

appXL.VLookup(..)

and all should be well. This may well fix the RTE you are getting too but if not let me know.

Although only in your comments I also see the code:

Excel.Application.Quit

This is again bad as it is not closing the instance you are creating. You need:

xlApp.Quit

Hope that helps. For more info on automating Excel from VB6 and avoiding the pitfalls of phantom links you could ceck out the tutorial on my site by my good friend MikeR:

http://www.thecodenet.com/articles.php?id=14

:)

yihong
07-27-2005, 06:58 PM
Hi,

Really done it after change to appXL.vlookup. Thanks so much.

One more thing, when run below loop code, I want to show message: Now is adding the wyeth code, please waiting.... and also continue to show when this loop finish. " Finish add coding, pls click any key to continue". How to do it. Thank u.



Do while Do While destXL.Cells(iRowDest, 30) <> ""
Dim findString As String
Dim rang1 As Range
Set rang1 = backXL.Range("A2:B10000")
findString = destXL.Cells(iRowDest, 30)
destXL.Cells(iRowDest, 31) = Application.VLookup(findString, rang1, 2, False)
iRowDest = iRowDest + 1
sourceCount = iRowDest
Loop

Killian
07-28-2005, 04:18 AM
You appear to be running this code from a form (?)
So you could add a status message (Label) to the form that updates

yihong
08-21-2005, 07:14 PM
Hi,

Sorry to trouble you again. Actually, this application can be run now. But got one error message.

First time run below code, it is ok. But run second time, when to set destWS=destWB.Worksheets("Detail"), the system prompt error message
"EXCEL.exe has generated errors and will be closed by windows. You will need to restart the program. An error log is being created". User just click "ok" button, the process still can process. But, how to solve the Excel error message prompt problem.

Thank you!


Set destWB=APPXL.WORKBOOKS.open(startdestF)
SET backWB=appXL.Workbooks.open(staBackFS)
set destWS=destWB.Worksheets("Detail")
set backWS=backWB.Worksheets("Sankyu")



[QUOTE=mark007]Your main problem of the multiple instances is here:


Application.VLookup(..)