Single column that is link to a external data range in Excel 2

[diehippy]

Hi,

I have created an OBCD link from Excel to Access and have created a query in SQL. This has brought up a data set that I need into Excel, what I am also trying do allow a user to add information to a column that corresponds to the data that has been produced from access, which is not connected to the external query but when the data is updated stays in line with data as new data is added from the query. If you can help I would very much appreciated

I am using Excel 2002.

Many Thanks

 

[combo]

If you can't extend your query data (in ms query), you can use vba:

Private Sub Workbook_Open()
With Sheet1 ' or other proper sheet's code name
    Set .qT = .QueryTables(1)
End With
End Sub

Public WithEvents qT As QueryTable

Private Sub qT_AfterRefresh(ByVal Success As Boolean)
If Success Then
    ' code to copy/delete formulas
    ' .ResultRange returns reference to output range
End If
End Sub

combo

 

[diehippy]

Hi Combo,

Thank you for the reply, I have no experience of VBA, so I am not sure what to put in the commented out part in the second piece of code

' code to copy/delete formulas
' .ResultRange returns reference to output range

I am probably being rather thick but any help will be much appreciated

Many Thanks

 

[SkipVought]

Try playing with the Data > Import External Data > Data Range Properties - If number of rows in the data range changes upon refresh: option.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[diehippy]

Hi Skip,

Thanks for the reply, but it doesn't seem to work. Text written in a column out side of the query does not move with the query when it is refreshed

Many Thanks

 

[SkipVought]

"Text written in a column out side of the query does not move"

Are you referring to an ADJACENT COLUMN, and not ROWS below the query in the SAME COLUMN(s) as the query?

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[SkipVought]

If that's the case, there's not way to keep rows of adjacent data in synch with the querytable.

On a separate sheet, list the KEY column(s) data fom your query. This will be relatively static. If new KEY VALUES are added, you'll have to also add them here. Next to the KEY VALUE(s) enter your annotations.

Now you have the Querytable on, let's say, Sheet1 and your annotations in Sheet2.

On Sheet3, do an outer join: all KEYS from Sheet1 joined to KEYS from Sheet2 where they match.



Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}