Excel opens readonly (sadly enough :)

[Ronny Sigo]

Hello all,
As one of a few ways to let a user bring in data into my database I have
made 3 excel files.
Once the data are in the excel files my program checks if a required value
(Taal (Dutch) meaning Language) has been brought in. This value can only
have one of two values : N (for Nederlands - Dutch) and E (for English)
If a language in the excel has not been put in, the prog displays a warning.
After clicking the OK button of the msgbox the prog opens the excel file in
question, so allowing the user to correct the program. All works fine exept
for the fact that -when trying to save the corrected excel file I get a
warning that the file is (or has been opened) readonly. As you can see
below, I tried to solve the problem assigning the value FALSE to the
readonly property of the excelfile.
Set ObjXLBook = ObjXL.Workbooks.Open("MyPath\MyFile1.XLS", , False)
However I keep on getting the same readonly message. Can anybody tell me
what I am doing wrong here?
Many thanks...
Ronny
Dim ObjXL As Excel.Application
Dim ObjXLBook As Excel.Workbook
Dim ObjXLSheet As Excel.Worksheet
Set ObjXL = CreateObject("Excel.Application")
Dim teller As Integer, nAantalImport As Integer, sXlsName As String
Dim NoGo As Boolean
NoGo = True
teller = 1
Do While teller < 4 'LOOP TO DO THE SAME OPERATION ON THE 3 EXCEL FILES
nRow = 3
Select Case teller
Case 1
Set ObjXLBook = ObjXL.Workbooks.Open("MyPath\MyFile1.XLS", ,
False)
sXlsName = "Targets"
Case 2
Set ObjXLBook = ObjXL.Workbooks.Open("MyPath\MyFile2.XLS", ,
False)
sXlsName = "TargetMerchants"
Case 3
Set ObjXLBook = ObjXL.Workbooks.Open("MyPath\MyFile3.XLS", ,
False)
sXlsName = "TargetAffinityGroups"
End Select
Set ObjXLSheet = ObjXLBook.Worksheets("Main")

Do While Len(Nz(Trim(ObjXL.Cells(nRow, nEmailadres).Value))) > 1
If Nz(Trim(ObjXL.Cells(nRow, nTaal).Value)) = "N" Or _ 'CHECK FOR
THE EXISTANCE OF THE LANGUAGE IN THE EXCEL FILE
Nz(Trim(ObjXL.Cells(nRow, nTaal).Value)) = "E" Then
NoGo = False
Else
DoCmd.Hourglass False
NoGo = True
MsgBox ("In het Excel bestand m.b.t. tot de " & sXlsName & _
vbCrLf & "werd een taal niet correct ingevuld.. Gelieve dit
probleem" & vbCrLf & _
"eerst te corrigeren.")
Exit Do
Set rst = Nothing
Set dbsMybase = Nothing
Set ObjXLSheet = Nothing
Set ObjXLBook = Nothing
Set ObjXL = Nothing
End If
nRow = nRow + 1
Loop
If NoGo = True Then
Set ObjXL = CreateObject("Excel.Application")
Select Case teller 'REOPEN THE EXCEL FILE TO LET THE USER
CORRECT THE PROBLEM
Case 1
Set ObjXLBook = ObjXL.Workbooks.Open("MyPath\MyFile1.XLS", ,
False)
Case 2
Set ObjXLBook = ObjXL.Workbooks.Open("MyPath\MyFile2.XLS", ,
False)
Case 3
Set ObjXLBook = ObjXL.Workbooks.Open("MyPath\MyFile3.XLS", ,
False)
End Select
Set ObjXLSheet = ObjXLBook.Worksheets("Main")
ObjXL.Visible = True
Exit Do
End If
teller = teller + 1
Loop

[MacDermott]

Your Excel workbook is still open from your first try at reading the data.
You must explicitly close the workbook
ObjXLBook.Close
before you can re-open it.
You also need to quit Excel
ObjXL.Quit
Otherwise, if you look in your task manager, you'll see multiple copies of
Excel running.
After about half a dozen, your application will refuse to open any more
copies.
Setting these objects = Nothing is not enough.

On the other hand, why bother to close and re-open everything?
You can save all that for after your
If NoGo = True Then
...
End If
and do it just once.

HTH
- Turtle
"Ronny Sigo" <ro********@skyynet.be> wrote in message
news:40***********************@news.skynet.be...

Hello all,
As one of a few ways to let a user bring in data into my database I have
made 3 excel files.
Once the data are in the excel files my program checks if a required value
(Taal (Dutch) meaning Language) has been brought in. This value can only
have one of two values : N (for Nederlands - Dutch) and E (for English)
If a language in the excel has not been put in, the prog displays a warning. After clicking the OK button of the msgbox the prog opens the excel file in question, so allowing the user to correct the program. All works fine exept for the fact that -when trying to save the corrected excel file I get a
warning that the file is (or has been opened) readonly. As you can see
below, I tried to solve the problem assigning the value FALSE to the
readonly property of the excelfile.
Set ObjXLBook = ObjXL.Workbooks.Open("MyPath\MyFile1.XLS", , False)
However I keep on getting the same readonly message. Can anybody tell me
what I am doing wrong here?
Many thanks...
Ronny
Dim ObjXL As Excel.Application
Dim ObjXLBook As Excel.Workbook
Dim ObjXLSheet As Excel.Worksheet
Set ObjXL = CreateObject("Excel.Application")
Dim teller As Integer, nAantalImport As Integer, sXlsName As String
Dim NoGo As Boolean
NoGo = True
teller = 1
Do While teller < 4 'LOOP TO DO THE SAME OPERATION ON THE 3 EXCEL FILES
nRow = 3
Select Case teller
Case 1
Set ObjXLBook = ObjXL.Workbooks.Open("MyPath\MyFile1.XLS", ,
False)
sXlsName = "Targets"
Case 2
Set ObjXLBook = ObjXL.Workbooks.Open("MyPath\MyFile2.XLS", ,
False)
sXlsName = "TargetMerchants"
Case 3
Set ObjXLBook = ObjXL.Workbooks.Open("MyPath\MyFile3.XLS", ,
False)
sXlsName = "TargetAffinityGroups"
End Select
Set ObjXLSheet = ObjXLBook.Worksheets("Main")

Do While Len(Nz(Trim(ObjXL.Cells(nRow, nEmailadres).Value))) > 1
If Nz(Trim(ObjXL.Cells(nRow, nTaal).Value)) = "N" Or _ 'CHECK FOR
THE EXISTANCE OF THE LANGUAGE IN THE EXCEL FILE
Nz(Trim(ObjXL.Cells(nRow, nTaal).Value)) = "E" Then
NoGo = False
Else
DoCmd.Hourglass False
NoGo = True
MsgBox ("In het Excel bestand m.b.t. tot de " & sXlsName & _
vbCrLf & "werd een taal niet correct ingevuld.. Gelieve dit
probleem" & vbCrLf & _
"eerst te corrigeren.")
Exit Do
Set rst = Nothing
Set dbsMybase = Nothing
Set ObjXLSheet = Nothing
Set ObjXLBook = Nothing
Set ObjXL = Nothing
End If
nRow = nRow + 1
Loop
If NoGo = True Then
Set ObjXL = CreateObject("Excel.Application")
Select Case teller 'REOPEN THE EXCEL FILE TO LET THE USER
CORRECT THE PROBLEM
Case 1
Set ObjXLBook = ObjXL.Workbooks.Open("MyPath\MyFile1.XLS", , False)
Case 2
Set ObjXLBook = ObjXL.Workbooks.Open("MyPath\MyFile2.XLS", , False)
Case 3
Set ObjXLBook = ObjXL.Workbooks.Open("MyPath\MyFile3.XLS", , False)
End Select
Set ObjXLSheet = ObjXLBook.Worksheets("Main")
ObjXL.Visible = True
Exit Do
End If
teller = teller + 1
Loop

[Ronny Sigo]

Thank you very much, you saved my day :)

Ronny

"MacDermott" <ma********@nospam.com> schreef in bericht
news:%Y*****************@newsread3.news.atl.earthl ink.net...

Your Excel workbook is still open from your first try at reading the data.
You must explicitly close the workbook
ObjXLBook.Close
before you can re-open it.
You also need to quit Excel
ObjXL.Quit
Otherwise, if you look in your task manager, you'll see multiple copies of
Excel running.
After about half a dozen, your application will refuse to open any more
copies.
Setting these objects = Nothing is not enough.

On the other hand, why bother to close and re-open everything?
You can save all that for after your
If NoGo = True Then
...
End If
and do it just once.

HTH
- Turtle
"Ronny Sigo" <ro********@skyynet.be> wrote in message
news:40***********************@news.skynet.be...

Hello all,
As one of a few ways to let a user bring in data into my database I have
made 3 excel files.
Once the data are in the excel files my program checks if a required value (Taal (Dutch) meaning Language) has been brought in. This value can only
have one of two values : N (for Nederlands - Dutch) and E (for English)
If a language in the excel has not been put in, the prog displays a warning.

After clicking the OK button of the msgbox the prog opens the excel file

in

question, so allowing the user to correct the program. All works fine

exept

for the fact that -when trying to save the corrected excel file I get a
warning that the file is (or has been opened) readonly. As you can see
below, I tried to solve the problem assigning the value FALSE to the
readonly property of the excelfile.
Set ObjXLBook = ObjXL.Workbooks.Open("MyPath\MyFile1.XLS", , False)
However I keep on getting the same readonly message. Can anybody tell me
what I am doing wrong here?
Many thanks...
Ronny
Dim ObjXL As Excel.Application
Dim ObjXLBook As Excel.Workbook
Dim ObjXLSheet As Excel.Worksheet
Set ObjXL = CreateObject("Excel.Application")
Dim teller As Integer, nAantalImport As Integer, sXlsName As String
Dim NoGo As Boolean
NoGo = True
teller = 1
Do While teller < 4 'LOOP TO DO THE SAME OPERATION ON THE 3 EXCEL FILES
nRow = 3
Select Case teller
Case 1
Set ObjXLBook = ObjXL.Workbooks.Open("MyPath\MyFile1.XLS", ,
False)
sXlsName = "Targets"
Case 2
Set ObjXLBook = ObjXL.Workbooks.Open("MyPath\MyFile2.XLS", ,
False)
sXlsName = "TargetMerchants"
Case 3
Set ObjXLBook = ObjXL.Workbooks.Open("MyPath\MyFile3.XLS", ,
False)
sXlsName = "TargetAffinityGroups"
End Select
Set ObjXLSheet = ObjXLBook.Worksheets("Main")

Do While Len(Nz(Trim(ObjXL.Cells(nRow, nEmailadres).Value))) > 1
If Nz(Trim(ObjXL.Cells(nRow, nTaal).Value)) = "N" Or _ 'CHECK FOR THE EXISTANCE OF THE LANGUAGE IN THE EXCEL FILE
Nz(Trim(ObjXL.Cells(nRow, nTaal).Value)) = "E" Then
NoGo = False
Else
DoCmd.Hourglass False
NoGo = True
MsgBox ("In het Excel bestand m.b.t. tot de " & sXlsName & _
vbCrLf & "werd een taal niet correct ingevuld.. Gelieve dit
probleem" & vbCrLf & _
"eerst te corrigeren.")
Exit Do
Set rst = Nothing
Set dbsMybase = Nothing
Set ObjXLSheet = Nothing
Set ObjXLBook = Nothing
Set ObjXL = Nothing
End If
nRow = nRow + 1
Loop
If NoGo = True Then
Set ObjXL = CreateObject("Excel.Application")
Select Case teller 'REOPEN THE EXCEL FILE TO LET THE USER
CORRECT THE PROBLEM
Case 1
Set ObjXLBook =

ObjXL.Workbooks.Open("MyPath\MyFile1.XLS", ,

False)
Case 2
Set ObjXLBook =

ObjXL.Workbooks.Open("MyPath\MyFile2.XLS", ,

False)
Case 3
Set ObjXLBook =

ObjXL.Workbooks.Open("MyPath\MyFile3.XLS", ,

False)
End Select
Set ObjXLSheet = ObjXLBook.Worksheets("Main")
ObjXL.Visible = True
Exit Do
End If
teller = teller + 1
Loop