• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 432
  • Last Modified:

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.


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

Open in new window


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

Open in new window


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

Open in new window

0
PeterBaileyUk
Asked:
PeterBaileyUk
1 Solution
 
Saqib Husain, SyedEngineerCommented:
Can you specify which line gives the error?

Can you upload a sample file?
0
 
PeterBaileyUkAuthor Commented:
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
0
 
SteveCommented:
When you set the variable wks maybe adding Thisworkbook. will help...

Such as:

Set wks = ThisWorkbook.Sheets("2012 Data feed")

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
PeterBaileyUkAuthor Commented:
ok i will try it.
0
 
Martin LissRetired ProgrammerCommented:
I've downloaded your files. How do i reproduce the error?
0
 
Martin LissRetired ProgrammerCommented:
You are unnecessarily closing the database. Remove these two lines


db.Close
Set db = Nothing


from these routines

GetTotalNewCodesCategoryCarsSMMT2012
GetTotalNewCodesCategoryCarsCW2012
GetTotalNewCodesCategoryBikesSMMT2012
0
 
PeterBaileyUkAuthor Commented:
all working fine now, thank you was the close db issue.
0
 
PeterBaileyUkAuthor Commented:
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
0
 
Martin LissRetired ProgrammerCommented:
Please provide the URL for the question.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now