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; the function of the macro is getting some data from a sheet called "Data" in a second file named "rex-2007.xls". This second file is located on another computer (named "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 (client) computer. If, without closing the source file, I run the macro again from the destination worksheet, it runs properly and I get the data with no errors at all. 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

My suggestion is to open the workbook in your code then close it again when you are done. This should fix your problem.

Personally I'd open the workbook then copy directly from the source to the target instead.

You could also link to the data directly instead, but I usually shy away from having linked workbooks.

Thank you for your suggestion, allenm; of course it will work. However, from the ADO literature I consulted, I expected to be able to access the data without opening the workbook, and I would like more such an option...but only if it works. Anyway, thank you again.