Dear all,
I have a problem with this code (since 1 month ago). There are problems with the MoveLast and MoveFirst function and I have been looking for it about 4 weeks ago and no solution found, so please if you can help me::
Sub ImportarModelos()
Dim strSource As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim consulta As String
Dim archivoAbrir As Excel.Workbook
Dim RUTAExcel As String
Dim strRespuesta As String
Dim posicionhoja As Integer
Dim rangocopiar As Range
Dim rangopegar As Range
Dim i As Long
Dim j As Long
Dim k As Long
Dim m As Long
Dim cantcarroc As Long
Dim matrizcarroc(150) As String
Dim ETYPE As String
Dim modelo As String
Static cantmodelos As String
Dim planta As String
Dim numMES1 As Integer
Dim numMES2 As Integer
Dim numMES3 As Integer
Dim numMES4 As Integer
Dim numMES5 As Integer
Dim numMES6 As Integer
Dim numMES7 As Integer
Dim numMES8 As Integer
Dim numMES9 As Integer
Dim numMES10 As Integer
Dim numMES11 As Integer
Dim numMES12 As Integer
Dim StartTime As Double
Dim MinutesElapsed As String
strRespuesta = MsgBox("La importación de Modelos eliminará todos los datos anteriores. ¿Desea continuar?", _
vbQuestion + vbYesNo, "Importación de Modelos")
If strRespuesta = vbNo Then
Exit Sub
End If
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False
'Colocar ACT y FC en la Plantilla
Call Etiquetar_FC_ACT
'Ruta Excel con rutas almacenadas (Excel formulario cargar ficheros)
RUTAExcel = RutaTool.Range("D2") & "" & RutaTool.Range("C2")
Set archivoAbrir = Workbooks.Open(RUTAExcel)
'Ruta bbdd
With archivoAbrir
strSource = ActiveSheet.Range("D6").Value & "" & ActiveSheet.Range("C6").Value
.Close
End With
Set dbs = DBEngine.OpenDatabase(strSource, False, False)
sFechaBotonModelos = "UPDATE Fechas_Modelos SET Fecha_BotonModelos= Now()"
dbs.Execute sFechaBotonModelos
'Aviso de ejecucion importar datos
Application.StatusBar = "Importando MODELOS desde Base de Datos"
marca = indice.Range("B10")
If marca = "TODAS" Then
consulta = "SELECT DISTINCT ModelosBD.E_CS " _
& "FROM ModelosBD " _
& "WHERE (((ModelosBD.Modelcode) In (SELECT Modelcode FROM [InvoicesAG]) " _
& "Or (ModelosBD.Modelcode) In (SELECT Modelcode FROM [SOYModel_NSCStock]) " _
& "Or (ModelosBD.Modelcode) In (SELECT Modelcode FROM [SOYModel_DealerStock]) " _
& "OR (ModelosBD.modelcode) in (SELECT Modelcode FROM WholesaleVO_CC) " _
& "OR (ModelosBD.modelcode) in (SELECT Modelcode FROM WholesaleVO_RAC) " _
& "Or (ModelosBD.modelcode) in (SELECT Modelcode FROM MatriculasCC) " _
& "Or (ModelosBD.modelcode) in (SELECT Modelcode FROM MatriculasRAC) " _
& "Or (ModelosBD.Modelcode) In (SELECT Modelcode FROM [WholesaleTOTAL]) " _
& "Or (ModelosBD.Modelcode) In (SELECT Modelcode FROM [IncomingORDERS]) " _
& "Or (ModelosBD.Modelcode) In (SELECT Modelcode FROM [RetailTOTAL])))"
Else
consulta = "SELECT DISTINCT ModelosBD.E_CS " _
& "FROM ModelosBD " _
& "WHERE (((ModelosBD.Brand)='" & marca & "') AND ((ModelosBD.Modelcode) In (SELECT Modelcode FROM [InvoicesAG]) " _
& "Or (ModelosBD.Modelcode) In (SELECT Modelcode FROM [SOYModel_NSCStock]) " _
& "Or (ModelosBD.Modelcode) In (SELECT Modelcode FROM [SOYModel_DealerStock]) " _
& "Or (ModelosBD.modelcode) in (SELECT Modelcode FROM WholesaleVO_CC) " _
& "Or (ModelosBD.modelcode) in (SELECT Modelcode FROM WholesaleVO_RAC) " _
& "Or (ModelosBD.modelcode) in (SELECT Modelcode FROM MatriculasCC) " _
& "Or (ModelosBD.modelcode) in (SELECT Modelcode FROM MatriculasRAC) " _
& "Or (ModelosBD.Modelcode) In (SELECT Modelcode FROM [WholesaleTOTAL]) " _
& "Or (ModelosBD.Modelcode) In (SELECT Modelcode FROM [IncomingORDERS]) " _
& "Or (ModelosBD.Modelcode) In (SELECT Modelcode FROM [RetailTOTAL])))"
End If
Set rst = dbs.OpenRecordset(consulta)
rst.MoveLast <---------- error
rst.MoveFirst <---------- error
cantcarroc = rst.RecordCount
For i = 1 To cantcarroc
matrizcarroc(i) = rst.Fields(0)
rst.MoveNext
Next i
'** para modelos, elimina todas las hojas y crea nueva por cada carrocería (para WH,RT e IO no elimina hojas)
canthojas = Worksheets.Count
If canthojas > 14 Then
For m = canthojas To 15 Step -1 'PARA CADA HOJA
Sheets(m).Delete
Next
End If 'canthojas >14
Sheets(3).Visible = True 'NuevaPlantilla
For m = 1 To cantcarroc
Sheets(3).Copy after:=Sheets(14) 'copia la hoja Plantilla (oculta)
Next m
Sheets(3).Visible = False
For i = 1 To cantcarroc