What am I doing wrong in my array?

[Eric]

I can't figure out what I'm doing wrongf in my array.
Every way I try it I get a compile error.

Option Compare Database
Option Explicit
Option Base 1

Public objExcel As New Excel.Application

Public Sub AuditSummary()
On Error GoTo ErrorHandler

Const NO_OF_ARRAYS = 8
Dim MyMonth, ArrayX(NO_OF_ARRAYS) As String, lngX As Long
Dim x As Integer, i As Integer

ArrayX = Array("Code Legibility", "Case/Roll
Closure", "Case Roll Condition", "Skid
Identification", "Skid Condition", "Damaged
Product", "Packaging Contamination", "Core Label")
x = 7

For i = 1 To NO_OF_ARRAYS
With objExcel
lngX = DLookup
("[#Checked]", "[qryReport2]", "[AttributeName] = '& ArrayX
(i)& '")
.Visible = True
'Calling the blank templete
.Workbooks.Open ("C:\QAAudit\FilmReports.xls")
.Application.ActiveSheet.Cells(x, 3).Select
.Application.ActiveCell.Value = lngX
x = x + 1
End With
Next i


MyMonth = Month(Date)

Select Case MyMonth
Case 1 To 5
'objExcel.ActiveWorkbook.SaveAs
("C:\QAAudit\LeolaFilmReportsSpring2004.xls")
Case Else
'objExcel.ActiveWorkbook.SaveAs
("C:\QAAudit\LeolaFilmReportsFall2004.xls")
End Select

objExcel.Quit
Set objExcel = Nothing

Exit Sub

ErrorHandler:
MsgBox Err.Number & " " & Err.Description
Err.Clear
objExcel.Quit
Set objExcel = Nothing

End Sub

 

[Marshall Barton]

I can't figure out what I'm doing wrongf in my array.
Every way I try it I get a compile error.

Option Compare Database
Option Explicit
Option Base 1

Public objExcel As New Excel.Application

Public Sub AuditSummary()
On Error GoTo ErrorHandler

Const NO_OF_ARRAYS = 8
Dim MyMonth, ArrayX(NO_OF_ARRAYS) As String, lngX As Long
Dim x As Integer, i As Integer

ArrayX = Array("Code Legibility", "Case/Roll
Closure", "Case Roll Condition", "Skid
Identification", "Skid Condition", "Damaged
Product", "Packaging Contamination", "Core Label")
x = 7

For i = 1 To NO_OF_ARRAYS
With objExcel
lngX = DLookup
("[#Checked]", "[qryReport2]", "[AttributeName] = '& ArrayX
(i)& '")

I can't unravel all that code, but you do have the quotes in
the DLookup wrong.

DLookup("[#Checked]", "[qryReport2]",
"[AttributeName] = '" & ArrayX(i) & "'")

 

[TC]

Eric, here's a tip. When you get an error, say what line the error occurs
on! Then many readers will see the problem instantly, & reply accordingly.
But if you do not say what line the error occurs on, you are expecting
people to wade through the whole thing, line by line. This is way
inefficient, & many reads will just not do that.

HTH,
TC