Hi all,

I'm new to this forum and almost new to VB. I will be very grateful if you can bring some help with a problem I am experiencing.

I'm trying to get data from one excel spreadshhet into another by using ADO. I run my macro from a sheet named "Captura" in a given workbook, and it should copy some data from a sheet called "Data" in a file named "rex-2007.xls" located in another computer ("Server").

The first time I run the macro, I get the error

"Subíndice fuera del intervalo"

(spanish version of excel; literal translation: Subindex out of interval/range)

If at the VB dialog box I choose to debug, the following code line is yellow highlighted:

    ActiveWorkbook.Worksheets("captura").Cells(6, 1).CopyFromRecordset rst

(remaining code at the end of the post)

At this point, the source file rex-2007.xls remains open in my computer. If, without closing the source file, I run the macro again from the destination worksheet, it runs properly and I get the data. If I close the source file and try the macro again, I encounter the same error and situation.

Again, I will be very grateful for any insight about this problem.


My code is the following (some processing lines at the end have been comented to speed testing):

Sub getrecordsbyado()

' Microsoft ADO 2.7

    Application.Goto Reference:="captura_borrar"
    Selection.ClearContents

    Dim cnt As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim strDB As String
    Dim strCommand As String
    Dim recCount As Long

    strDB = "\\server\data\2007\rex-2007.xls"

    cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & strDB & ";Extended Properties=Excel 8.0;"

    strCommand = "SELECT data, solicitante,mostra,sai FROM [dat$] WHERE MS=""X"""

    rst.Open strCommand, cnt

    ActiveWorkbook.Worksheets("captura").Cells(6, 1).CopyFromRecordset rst

    rst.Close
    cnt.Close
    Set rst = Nothing
    Set cnt = Nothing


  ' Application.Goto Reference:="captura_col1"
   ' Selection.FormulaArray = "=VALUE(LEFT(RC[5],4))"
    'Selection.Copy
    'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    ' :=False, Transpose:=False
    'Application.Goto Reference:="captura_col2"
    'Selection.FormulaR1C1 = "=VALUE(RIGHT(RC[4],5))"
    'Selection.Copy
    'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
     ' :=False, Transpose:=False
    'Application.Goto Reference:="captura_trim"
    'Selection.ClearContents


End Sub

Sorry...

I think I was a bit confused when posting this topic, and I failed to locate the most adequate forum. I have now opened a post regarding this same problem on the MS Office/Excel VBA forum. Moderator, please feel free to delete the topic from the VB/Beginning VB 6 forum if you find it appropiate. I want to apologize for the inconveniences caused.