Easy Question I hope...

[JShaper]

I use the following code generated by the record macro button. I have edited the SQL statment to fit my current need but have hit a road block in that it only runs once per time I open Excel. I wish to import a few different tables into a few different pages. The second time I run it i get the 1004 run-time error at the .refresh statement. Can you please tell me what i am doing wrong?

Sub TEMP()
 With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DRIVER={Oracle in OraClient10g_home1};SERVER=RPTCCB;UID=JSHAPER;DBQ=RPTCCB;DBA=W;APA=T;EXC=F;XSM=Default;FEN=T;QTO=T;FRC=10;FDL" _
        ), Array( _
        "=10;LOB=T;RST=T;BTD=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=Me;CSR=F;FWC=F;FBS=60000;TLO=O;" _
        )), Destination:=Range("A1"))
        .CommandText = "SELECT SC_ACCESS_CNTL.USR_GRP_ID, SC_ACCESS_CNTL.APP_SVC_ID, SC_ACCESS_CNTL.ACCESS_MODE, SC_USR_GRP_PROF.EXPIRATION_DT FROM CRYSTAL.SC_ACCESS_CNTL SC_ACCESS_CNTL, CRYSTAL.SC_USR_GRP_PROF SC_USR_GRP_PROF  WHERE SC_ACCESS_CNTL.APP_SVC_ID = SC_USR_GRP_PROF.APP_SVC_ID AND SC_ACCESS_CNTL.USR_GRP_ID = SC_USR_GRP_PROF.USR_GRP_ID"
        .Name = "Q32009SOD"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceConnectionFile = "C:\Documents and Settings\jshaper\Application Data\Microsoft\Queries\Query from RPTCCB.dqy"
        .Refresh BackgroundQuery:=False
    End With
End Sub

Thanks in advance for your help...

 

[rdwray]

Try stepping through the code until you get the error. Place a "On Error Goto whereever" before the error and keep trying to get the answer. I think you will fiind it on the first line of code, maybe...

 

[JShaper]

its odd, cause its at the refresh statment i get that, but if i close and save. When i reopen i can run the script again one time then the second time i run it it gives me 1004 error at that same line and i can do this 10 times by closing saving and opening then running script but it has to close out excel not just the project.

Can anyone help me?

 

[rdwray]

It sounds like it is trying to connect to the database a second time and it doesn't like it. Use the On Error to bypass it and keep going.

 

[JShaper]

That allows the script to finish but nothing happens the second time through...