link 2 spreadsheets via VBA

[cutestuff]

hello to all the Excel geniuses out here!

Hopefully one of you can help me out.

I have 2 excel spreadsheets.
The first one: gets its data from a query. This spreadsheet will only ever have one row of information (that keeps changing based on the query).

The second spreadsheet: is a MASTER sheet that contains all the appended info from the first one. What I'm trying to get it to do is to link this to the first spreadsheet and when a user clicks on "Refresh", it will append the values from the first spreadsheet to the next available row .

I have the following code:

----------------------------
Private Sub Refresh_Click()
ActiveWorkbook.Sheets("Sheet1").Activate

Range("A1").Select

Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If

Loop Until IsEmpty(ActiveCell) = True

'ActiveCell.Value = [test.xls]![123]!A2
'ActiveCell.Offset(0, 1) =

End Sub
-----------------------

This works (as in it goes to the next free row).

I can't get the syntax for assigning the value right though (see : 'ActiveCell.Value = [test.xls]![123]!A2 ).
I've tried a few combinations.
I can make it work if it's in the same spreadsheet, but not if they're 2 different ones.

Does this make sense?

Can anyone help?

Thanks in advance!

 

[SkipVought]

Hi,

This can easily be done using ADO. Here is some code that I have used. The CopyFromRecordset method is where you would append the data to your table.

You could either query the other sheet or change your process to query the source data directly, skipping the other sheet part of the process.

Sub AppendData()
    Dim sConn As String, sSQL As String
    Dim rst As ADODB.Recordset, cnn As ADODB.Connection
    Dim sPath As String, sDB As String
    Dim sh As Range, lRow As Long
    
    sPath = ActiveWorkbook.Path
    sDB = Split(ActiveWorkbook, ".")(0)
    
    Set cnn = New ADODB.Connection
    
    sConn = "Provider=MSDASQL.1;"
    sConn = sConn & "Persist Security Info=False;"
    sConn = sConn & "Extended Properties=""DSN=Excel Files;"
    sConn = sConn & "DBQ=" & sPath & "\" & sDB & ".xls;"
    sConn = sConn & "DefaultDir=" & sPath & ";"
    sConn = sConn & "DriverId=790;MaxBufferSize=2048;PageTimeout=5;"""
    
    cnn.Open sConn
    
    Set rst = New ADODB.Recordset
    
    For Each sh In [SheetName]
        
        sSQL = "SELECT A.PN"
        sSQL = sSQL & ", A.RQDATE"
        sSQL = sSQL & ", A.QTY"
        sSQL = sSQL & ", A.COST"
        sSQL = sSQL & ", A.NOMEN"
        sSQL = sSQL & ", A.`GROUP`"
        sSQL = sSQL & ", A.`Late Pieces`"
        sSQL = sSQL & ", A.BackLog "
        
        sSQL = sSQL & "FROM `" & sPath & "\" & sDB & "`.`" & sh.Value & "$` A "
        
        sSQL = sSQL & "WHERE (A.`Late Pieces`>0 OR A.BackLog>0) "
        sSQL = sSQL & "  AND (A.COE='DSC') "
        
        [Sql] = sSQL
        
        With rst
           .Open sSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText
           
            With wsData
               lRow = .UsedRange.Rows.Count + 1
               .Cells(lRow, 1).CopyFromRecordset rst
               .Range(.Cells(lRow, .UsedRange.Columns.Count), .Cells(.UsedRange.Rows.Count, .UsedRange.Columns.Count)).Value = sh.Value
            End With
        
           .Close
        End With
    Next
    cnn.Close
    
    Set rst = Nothing
    Set cnn = Nothing
End Sub

Skip,

_[red][/red]