Connection between Excel SPreadsheets

JezLisle · #1 (**permalink**) 06-05-07, 06:32

I have used this below to get data from Access to Excel, is it possible to create a similar thing where it connects to another Excel Spreadsheet when opening another?

What I am wanting to do is. When I open Excel Report A that it connects to Report B and copies a range of data and then pastes back in Report A on a relevant sheet. Once it has done this then closes the connection between to the 2 Reports. I dont want to use the Link option as that gives me a permenant link.

Thanks, Jez

Excel to Access code
Dim cnnDW As ADODB.Connection
Dim rsDW As ADODB.Recordset
Dim sQRY As String
Dim strDWFilePath, strCSVFilePath, strDestFilePath, strDestFileName As String

On Error GoTo Err:
strDWFilePath = "H:\NCHO\Housing Services\Data Warehouse\HSG Data Warehouse.mdb"
Set cnnDW = New ADODB.Connection
Set rsDW = New ADODB.Recordset
cnnDW.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDWFilePath & ";"
sQRY = "TRANSFORM Count([qryNoAccess(byAppt)].WRNumber) AS CountOfWRNumber " & _

MikeTheBike · #2 (**permalink**) 06-05-07, 08:34

Hi

Yes you can connect to Excel with ADO, this code seems to work

Code:

Sub TestExcel()
    Dim strCon As String
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
        
    strCon = "Provider=MSDASQL.1;" & _
                "Data Source=TestExcelConnection;Extended" & _
                "DBQ=H:\DBForum\TestDB.xls;" & _
                "DefaultDir=H:\DBForum"
    
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    
    cn.Open strCon 
        
    rs.Open "SELECT * FROM [Sheet1$]", cn
    
    Do Until rs.EOF
        MsgBox rs(2)
        rs.MoveNext
    Loop
End Sub

You can also specify a range name as the "Table" (including field names in the top row)

ie if the data you want is in the range named 'TableRange' then use this

rs.Open "SELECT * FROM TableRange", cn

You could also open the required workbook and copy anything you like into the 'current' workbook and close it afterwords.

If you turn ScreenUpdating off before and on again afterwards, you would be non the wiser.

If you do this, set a reference (object variable) to the 'current' workbook before opening the second book as this becomed the 'current' book when opened.

This is my normal way with spreadsheet, mainly because they usually (always!) have different name or are in different places or both.

HTH

MTB

Internet Services

Web Development

Digital Marketing

Consumer Tech