populating excel cells with data from data sources

  • Thread starter Thread starter Craig
  • Start date Start date
C

Craig

I have a complex excel spreadsheet that is used for reporting purposes and
consolidates information from different data sources.

I have 2 data sources, one a SQL Server 2000 database, another is a sybase
SQL Anywhere 9 software.

Each database holds certain information about a unique identifier, let's say
a JobNumber.

I want to populate specific cells within my excel spreadsheet with
information from the data sources, but it's not rows and rows of
information, it is specific field values. What is the best way to achieve
this.

I was thinking of adding a button and using VBA code to retrieve data, but
not sure how this works. Can you point me in the right direction?

Thanks

/ Craig
 
Where will your actual query reside? MSQuery maybe. Do you want to do
it all in VBA or just use VBA to trigger the data to refresh?

i.e. do you want to create a report written in VBA and return the data
to a number of single cells, or just use vba to refresh the data that's
already there?

The latter is by far the easiest, something like

Sub RefreshMyData()
Dim myRange as Range
Set myRange = Range("B54") 'wherever you have data
myRange.QueryTable.Refresh
End Sub

or perhaps:

Sub RefreshMyData()
Dim i as integer
For i = 1 to 13 'however many separate data queries you have
Sheets("My Sheet").Querytables(i).Refresh
Next i
End Sub

Whereas creating the queries in VBA is a whole load more work!
 
Back
Top