Multi Dimensional Array 1

[MikeAuz1979]

Hi Using Access 2000 and excel 2003 I'm trying to write a sub that will basically copy and paste data from three access tables to three different excel tabs on a excel template. (Range A2 on all 3 tabs)

So the below is as far as I have got and I get the error 'Can't assign to array' on the 'RstArray = ' line when I try to first run the code.

Anyone have any ideas?

Thanks for any help!

Code Start-----
Sub test()

Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim RstArray(2, 3)

con.ConnectionString = CurrentProject.Connection
con.Open
Set rst.ActiveConnection = con

Set acapp = CreateObject("Excel.Application")
acapp.displayalerts = False
acapp.Workbooks.Open ("C:\Template.xlt")
acapp.Visible = True

RstArray = Array("tblAll", "AllData", "tblDefault", "DefaultData", "tblContractFinal", "ContractData")
For i = 0 To 2
rst.Source = "SELECT * from " & RstArray(i, 0)
rst.Open
With acapp.worksheets(TabArray(i, 1)).range("A2")
.CopyFromRecordset rst
End With
rst.Close
Next

Set con = Nothing
End Sub
Code End-----

 

[Remou]

You would have to do this the long way:

[tt]rstArray(0,0)="tblAll"[/tt]

I think you may have meant
Dim RstArray(2, 1)
That is, three rows and two columns.

For what you want to do, it may be easier to use Split and two arrays:

astrTables = Split("tblAll,tblDefault,tblContractFinal",",")
astrWS = Split("AllData,DefaultData,ContractData",",")
For i = 0 To 2
    rst.Source = "SELECT * from " & astrTables (i)
    rst.Open
    With acapp.worksheets(astrWS(i)).range("A2")
        .CopyFromRecordset rst
    End With
    rst.Close
Next

It is a good idea to use [ignore]

[/ignore] when posting. You will find more information in the Progress TGML link, below.

 

[CautionMP]

MikeAuz1979,
I agree with Remou that using [tt]Split()[/tt] is the way to go. Here is a single array variation on his code.

Dim RstArray()
...
RstArray = Split("tblAll AllData tblDefault DefaultData tblContractFinal ContractData")
For i = LBound(RstArray) To UBound(RstArray) Step 2
    rst.Source = "SELECT * from " & RstArray(i)
    rst.Open
    With acapp.worksheets(RstArray(i + 1)).range("A2")
        .CopyFromRecordset rst
    End With
    rst.Close
Next i
...

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[MikeAuz1979]

Hey Guys,
First thanks for answering but I get some errors when I try both of your methods.

Remou,
This has the strangest error, it works fine for the first loop but for the second it errors on '.CopyFromRecordset rst' with the error 'Subscript out of range' and when I check the current value of i it is 3. I thought maybe it was xl 2003 that was weirding out access so I removed it and installed xl 2000 instead but got the same result.

CautionMP,
I got an invalid data type error on the line RstArray = ...

Any ideas?

Thanks again for the help
Mike

 

[Remou]

That is certainly very strange. Please post the code as modified for your application. From the above, it seems that you are running in Excel, is that correct?

 

[MikeAuz1979]

Sure Remou,

My code is below and the code is running in access 2k but effectively copy and pastes to excel.

Sub test()

Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset


con.ConnectionString = CurrentProject.Connection
con.Open
Set rst.ActiveConnection = con

Set acapp = CreateObject("Excel.Application")
acapp.displayalerts = False
acapp.Workbooks.Open ("G:\Bsprice\BA Team Reporting Files\SimpleMargin\VICSimpleMargin.xlt")
acapp.Visible = True

strTables = Split("tblAll,tblDefault,tblContractFinal", ",")
StrWS = Split("AllData,DefaultData,ContractData", ",")
For i = 0 To 2
    rst.Source = "SELECT * from " & strTables(i)
    rst.Open
    With acapp.worksheets(StrWS(i)).range("A2")
        .CopyFromRecordset rst
    End With
    rst.Close
Next

Set con = Nothing
acapp.worksheets("Results").PivotTables("PivotTable2").RefreshTable
End Sub

 

[Remou]

I find I cannot use CopyFromRecordset with the way my version of Access is set-up at the moment, however, I do not get the error you have noted either. Perhaps you could try simply debug.printing the elements of each array and see if you get the same error?

 

[MikeAuz1979]

Remou,

It debug.printed all the table and tab names perfectly so I guess it's some kind of problem with the .copyfromrecordset, I thought maybe it was a timing thing but this error still happens when I step through the code also.

Any more ideas?

Thanks heaps for your help so far.
Mike

 

[Remou]

You could try a DAO recordset, that works for me with CopyFromRecordset:

Dim rs As DAO.Recordset

<...>

strtables = Split("tblAll,tblDefault,tblContractFinal", ",")
strWS = Split("AllData,DefaultData,ContractData", ",")
For i = 0 To 2
    Set rs = CurrentDb.OpenRecordset(strtables(i))
    
    With acapp.Worksheets(strWS(i)).Range("A2")
        .CopyFromRecordset rs
    End With
Next

 

[MikeAuz1979]

Finally Sucess!

Thanks for your help Remou, well worth a star.

It worked by using a multi-dimensional array and a DAO recordset.

Dim RstArray(1, 2)
Dim rs As DAO.Recordset


Set acapp = CreateObject("Excel.Application")
acapp.displayalerts = False
acapp.Workbooks.Open ("G:\Bsprice\BA Team Reporting Files\SimpleMargin\VICSimpleMargin.xlt")
acapp.Visible = True

RstArray(0, 0) = "tblAll"
RstArray(0, 1) = "tblDefault"
RstArray(0, 2) = "tblContractFinal"
RstArray(1, 0) = "AllData"
RstArray(1, 1) = "DefaultData"
RstArray(1, 2) = "ContractData"

For i = 0 To 2
    Set rst = CurrentDb.OpenRecordset(RstArray(0, i))
    With acapp.Worksheets(RstArray(1, i)).Range("A2")
        .CopyFromRecordset rst
    End With
    rst.Close
Next