Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273

    Unanswered: Connection between Excel SPreadsheets

    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 " & _

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •