cacha18cr
asked on
What does mean
I'm trying to take data from AS400 and using a VBA Macro in excel refresh a qry. But when the refreshment table excel gives me the following error
Attached macro
Sub Actualizar()
vBarco = Trim(Sheets("Menu").Range( "D10"))
vViaje = Trim(Sheets("Menu").Range( "D11"))
vLiner = Trim(Sheets("Menu").Range( "D12"))
vPais = Trim(Sheets("Menu").Range( "E13"))
Sheets("DTLTrace").Select
Range("A1").Select
With ActiveWorkbook.Connections ("BarcoVia jeDestino" ).ODBCConn ection
.BackgroundQuery = False
.CommandText = Array("SELECT FLTMRES.CNMCTE as Contenedor , FLTMMAN.CNMCTP as Prefijo, SCENEM as Embarque " _
, ", FLTMMAN.ORFCOD as Empacadora " _
, ", FLTMCNT.CNMFEH AS FechaLlegada " _
, ", FLTMCNT.CNMFEH AS HoraLlegada " _
, ", FLTMCNT.CNMLCH as MatriculaChasis, FLTMCNT.CNMSLL as SelloLlegada " _
, " FROM DOLDATTRC.FLTMRES FLTMRES,DOLDATTRC.FLTMMAN FLTMMAN,DOLDATTRC.FLTPPROD FLTPPROD,DOLDATTRC.FLTEEMB A FLTEEMBA," _
, " DOLDATTRC.FLTMCNT FLTMCNT, DOLDATTRC.FLTFORIG FLTFORIG, DOLDATTRC.FLTESHIP FLTESHIP, DOLDATTRC.FLTFFINC FLTFFINC, " _
, " DOLDATTRC.FLTESCHE FLTESCHE, DOLDATTRC.FLTFENTI FLTFENTI, DOLDATTRC.FLTMTDES FLTMTDES,DOLDATTRC.FLTEPOR T FLTEPORT" _
, " WHERE FLTMRES.PAFCOD = FLTMMAN.PAFCOD AND FLTMRES.FIFCOD = FLTMMAN.FIFCOD AND FLTMRES.ORFCOD = FLTMMAN.ORFCOD" _
, " AND FLTMRES.MAMDOC = FLTMMAN.MAMDOC AND FLTMRES.PAFCOD = FLTPPROD.PAFCOD AND FLTMRES.PRPNKN = FLTPPROD.PRPNKN " _
, " AND FLTMRES.SHECOD = FLTEEMBA.SHECOD AND FLTMRES.EMENVI = FLTEEMBA.EMENVI AND FLTMRES.PAFCOD = FLTFORIG.PAFCOD " _
, " AND FLTMRES.DEMORG = FLTFORIG.ORFCOD AND FLTMRES.SHECOD = FLTESHIP.SHECOD AND FLTMMAN.MAMPTO = FLTEPORT.POECOD " _
, " AND FLTMRES.PAFCOD = FLTMCNT.PAFCOD AND FLTMRES.CNMCTP = FLTMCNT.CNMCTP AND FLTMRES.CNMCTE = FLTMCNT.CNMCTE " _
, " AND FLTMRES.FIFCOD = FLTMCNT.FIFCOD AND FLTMRES.MAMDOC = FLTMCNT.CNMDOC AND FLTMRES.PAFCOD = FLTFFINC.PAFCOD " _
, " AND FLTMRES.DEMFIC = FLTFFINC.FIFCOD AND FLTMRES.SHECOD = FLTESCHE.SHECOD AND FLTMRES.EMENVI = FLTESCHE.EMENVI " _
, " AND FLTMRES.PAFCOD = FLTESCHE.SCEPAI AND FLTFFINC.PAFCOD = FLTFENTI.PAFCOD AND FLTFFINC.ENFCOD = FLTFENTI.ENFCOD " _
, " AND FLTMMAN.TDMCOD = FLTMTDES.TDMCOD " _
, " rs.PAFCOD = '" & vPais & "' AND FLTEEMBA.SHECOD = '" & vBarco & "' AND FLTEEMBA.EMENVE = '" & vViaje & "' AND FLTEEMBA.ELICOD = '" & vLiner & "' ")
.Refresh
End With
Attached macro
Sub Actualizar()
vBarco = Trim(Sheets("Menu").Range(
vViaje = Trim(Sheets("Menu").Range(
vLiner = Trim(Sheets("Menu").Range(
vPais = Trim(Sheets("Menu").Range(
Sheets("DTLTrace").Select
Range("A1").Select
With ActiveWorkbook.Connections
.BackgroundQuery = False
.CommandText = Array("SELECT FLTMRES.CNMCTE as Contenedor , FLTMMAN.CNMCTP as Prefijo, SCENEM as Embarque " _
, ", FLTMMAN.ORFCOD as Empacadora " _
, ", FLTMCNT.CNMFEH AS FechaLlegada " _
, ", FLTMCNT.CNMFEH AS HoraLlegada " _
, ", FLTMCNT.CNMLCH as MatriculaChasis, FLTMCNT.CNMSLL as SelloLlegada " _
, " FROM DOLDATTRC.FLTMRES FLTMRES,DOLDATTRC.FLTMMAN FLTMMAN,DOLDATTRC.FLTPPROD
, " DOLDATTRC.FLTMCNT FLTMCNT, DOLDATTRC.FLTFORIG FLTFORIG, DOLDATTRC.FLTESHIP FLTESHIP, DOLDATTRC.FLTFFINC FLTFFINC, " _
, " DOLDATTRC.FLTESCHE FLTESCHE, DOLDATTRC.FLTFENTI FLTFENTI, DOLDATTRC.FLTMTDES FLTMTDES,DOLDATTRC.FLTEPOR
, " WHERE FLTMRES.PAFCOD = FLTMMAN.PAFCOD AND FLTMRES.FIFCOD = FLTMMAN.FIFCOD AND FLTMRES.ORFCOD = FLTMMAN.ORFCOD" _
, " AND FLTMRES.MAMDOC = FLTMMAN.MAMDOC AND FLTMRES.PAFCOD = FLTPPROD.PAFCOD AND FLTMRES.PRPNKN = FLTPPROD.PRPNKN " _
, " AND FLTMRES.SHECOD = FLTEEMBA.SHECOD AND FLTMRES.EMENVI = FLTEEMBA.EMENVI AND FLTMRES.PAFCOD = FLTFORIG.PAFCOD " _
, " AND FLTMRES.DEMORG = FLTFORIG.ORFCOD AND FLTMRES.SHECOD = FLTESHIP.SHECOD AND FLTMMAN.MAMPTO = FLTEPORT.POECOD " _
, " AND FLTMRES.PAFCOD = FLTMCNT.PAFCOD AND FLTMRES.CNMCTP = FLTMCNT.CNMCTP AND FLTMRES.CNMCTE = FLTMCNT.CNMCTE " _
, " AND FLTMRES.FIFCOD = FLTMCNT.FIFCOD AND FLTMRES.MAMDOC = FLTMCNT.CNMDOC AND FLTMRES.PAFCOD = FLTFFINC.PAFCOD " _
, " AND FLTMRES.DEMFIC = FLTFFINC.FIFCOD AND FLTMRES.SHECOD = FLTESCHE.SHECOD AND FLTMRES.EMENVI = FLTESCHE.EMENVI " _
, " AND FLTMRES.PAFCOD = FLTESCHE.SCEPAI AND FLTFFINC.PAFCOD = FLTFENTI.PAFCOD AND FLTFFINC.ENFCOD = FLTFENTI.ENFCOD " _
, " AND FLTMMAN.TDMCOD = FLTMTDES.TDMCOD " _
, " rs.PAFCOD = '" & vPais & "' AND FLTEEMBA.SHECOD = '" & vBarco & "' AND FLTEEMBA.EMENVE = '" & vViaje & "' AND FLTEEMBA.ELICOD = '" & vLiner & "' ")
.Refresh
End With
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Wild guess here but the error message says that 'RS' is invalid and since the only place you have 'rs' is rs.PAFCOD, try changing that to FLTFENTI.PAFCOD or FLTFFINC.PAFCOD or whatever is appropriate.
ASKER
Thank you, very much!
FLTMMAN,DOLDATTRC.FLTPPROD
, " DOLDATTRC.FLTMCNT FLTMCNT, DOLDATTRC.FLTFORIG FLTFORIG,