PeterBaileyUk
asked on
Excel error code 91
My vba (courtesy of harfang, and much appreciated) has started tripping over after populating the months of July only (july is last data set).
It says error 91 I have f8 through and everything looks ok.
variables defines at top:
I call the subs from here:
It says error 91 I have f8 through and everything looks ok.
Sub GetTotalNewCodesCategoryOthersCW2012()
Set qdf = db.QueryDefs("qptotNewCodes 3 parameters")
Set RstCurrentYearMonth = db.OpenRecordset("SELECT Max(CWSMMTBuilds.BuildYearMonth) AS MaxOfBuildYearMonth FROM CWSMMTBuilds;", dbReadOnly)
'get current data month
With RstCurrentYearMonth
.MoveLast
CurrentDataMonth = .Fields("MaxofBuildYearMonth").Value
End With
datMonth = #1/1/2012#
Do While datMonth <= CurrentDataMonth
qdf.Parameters("BYM") = DateSerial(Format(datMonth, "yyyy"), Format(datMonth, "mm"), 1)
qdf.Parameters("[Yes=CW Codes/No=SMMT Codes]").Value = True
qdf.Parameters("[Enter Cars, Bikes, Lcv, Others]").Value = "Others"
With wks.Cells(12, Format(datMonth, "mm") + 1)
.Select
.Value = "...."
DoEvents
.Value = qdf.OpenRecordset!Nb
DoEvents
End With
datMonth = DateAdd("m", 1, datMonth)
Loop
Set RstCurrentYearMonth = Nothing
db.Close
Set db = Nothing
End Sub
variables defines at top:
Option Explicit
Const DATA_SOURCE = "N:\Data\smmt\CWSMMT2012.mdb"
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim qdfMatched As DAO.QueryDef
Dim intM As Integer
Dim wks As Worksheet
Dim RstCurrentYearMonth As DAO.Recordset
Dim CurrentDataMonth As Date
Dim datMonth As Date
I call the subs from here:
Sub GetTotalNewCodesSMMT2012()
Set wks = Sheets("2012 Data feed")
Set db = DAO.OpenDatabase(DATA_SOURCE)
Call GetTotalNewCodeCounts2012
Call GetTotalNewCodesCategorySMMT2012
Call GetTotalNewCodesCategoryCW2012
Call GetTotalNewCodesCategoryCarsSMMT2012
Call GetTotalNewCodesCategoryCarsCW2012
Call GetTotalNewCodesCategoryBikesSMMT2012
Call GetTotalNewCodesCategoryBikesCW2012
Call GetTotalNewCodesCategoryLCVSMMT2012
Call GetTotalNewCodesCategoryLCVCW2012
Call GetTotalNewCodesCategoryOthersSMMT2012
Call GetTotalNewCodesCategoryOthersCW2012
End Sub
ASKER
Ok I have my files on:N\data\smmt so change data source in spredsheet vba to your location.
The worksheet I am working on is 2012 datafeed
itll only return values for 2012-07 as I have cut the data down but two routines are called and you should see it halt on error 91 on the last july column
ee-testing.zip
The worksheet I am working on is 2012 datafeed
itll only return values for 2012-07 as I have cut the data down but two routines are called and you should see it halt on error 91 on the last july column
ee-testing.zip
When you set the variable wks maybe adding Thisworkbook. will help...
Such as:
Such as:
Set wks = ThisWorkbook.Sheets("2012 Data feed")
ASKER
ok i will try it.
I've downloaded your files. How do i reproduce the error?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
all working fine now, thank you was the close db issue.
ASKER
Just in case your interested I have another question open re the refreshing of cells, in the sheet just corrected the cell goes to ... then changes to the value but in the sheet overall stats the same code method does not work. heres the ref id:
ID: 38279161
might be another 500 to you
ID: 38279161
might be another 500 to you
Please provide the URL for the question.
Can you upload a sample file?