Moving data from SQL Server to Excel

Hi,
I use this code to get the data from a SQL Server database and put it into a
spread sheet

' Get Geneneration Data
sSql = "Select Date,hh,Dartm1,Eildon1,Eildon2 from mdff_Generation_Xtab"
& _
" Where Date >='20051023' And Date < '20051030' Order By Date,hh"
rsADO.Open sSql, connMelbourne, adOpenStatic, adLockReadOnly

' Copy Data
rsADO.MoveFirst
Counter = 2
Do Until rsADO.EOF
Range("A" & Counter).Value = rsADO!Date
Range("B" & Counter).Value = rsADO!hh
Range("C" & Counter).Value = rsADO!Dartm1
Range("D" & Counter).Value = rsADO!Eildon1
Range("E" & Counter).Value = rsADO!Eildon2
Counter = Counter + 1
rsADO.MoveNext
Loop

I think there is a way of going straight to the spread sheet without going
through the load the record set copy to spread sheet route.

Any suggestions of a better way that does not involve so much work, there
are a lot of these to do?

Thanks MarkS

A couple of ideas:
1. Can you use SQL Server DTS packages to output straight to Excel?
2. You can replace the direct references to the fields with an index to the
fields collection and use the same value as an offset to column A, as in:

Do Until rsADO.EOF
For field = 0 to rsADO.Fields.count - 1
Range("A" & Counter).offset(0,field).Value =
rsADO.fields(field).value
Next
Counter = Counter + 1
rsADO.MoveNext
Loop

This way you can change just the SQL string and the rest should work fine.
(Not sure about the exact syntax of the ADO fields collection but I think
it's right. Check also that the collection index starts at 0.)

HTH

Barry


"MarkS" wrote:

> Hi,
> I use this code to get the data from a SQL Server database and put it into a
> spread sheet
>
> ' Get Geneneration Data
> sSql = "Select Date,hh,Dartm1,Eildon1,Eildon2 from mdff_Generation_Xtab"
> & _
> " Where Date >='20051023' And Date < '20051030' Order By Date,hh"
> rsADO.Open sSql, connMelbourne, adOpenStatic, adLockReadOnly
>
> ' Copy Data
> rsADO.MoveFirst
> Counter = 2
> Do Until rsADO.EOF
> Range("A" & Counter).Value = rsADO!Date
> Range("B" & Counter).Value = rsADO!hh
> Range("C" & Counter).Value = rsADO!Dartm1
> Range("D" & Counter).Value = rsADO!Eildon1
> Range("E" & Counter).Value = rsADO!Eildon2
> Counter = Counter + 1
> rsADO.MoveNext
> Loop
>
> I think there is a way of going straight to the spread sheet without going
> through the load the record set copy to spread sheet route.
>
> Any suggestions of a better way that does not involve so much work, there
> are a lot of these to do?
>
> Thanks MarkS

Hi Mark.

Use Range("A2").copyfromrecordset reADO
Regards
JY

"MarkS" <mscholes.remove@southernhydro.com.au> wrote in message
news:25154328-1E1A-4324-B845-6948E10181E5@microsoft.com...
> Hi,
> I use this code to get the data from a SQL Server database and put it into
a
> spread sheet
>
> ' Get Geneneration Data
> sSql = "Select Date,hh,Dartm1,Eildon1,Eildon2 from
mdff_Generation_Xtab"
> & _
> " Where Date >='20051023' And Date < '20051030' Order By Date,hh"
> rsADO.Open sSql, connMelbourne, adOpenStatic, adLockReadOnly
>
> ' Copy Data
> rsADO.MoveFirst
> Counter = 2
> Do Until rsADO.EOF
> Range("A" & Counter).Value = rsADO!Date
> Range("B" & Counter).Value = rsADO!hh
> Range("C" & Counter).Value = rsADO!Dartm1
> Range("D" & Counter).Value = rsADO!Eildon1
> Range("E" & Counter).Value = rsADO!Eildon2
> Counter = Counter + 1
> rsADO.MoveNext
> Loop
>
> I think there is a way of going straight to the spread sheet without going
> through the load the record set copy to spread sheet route.
>
> Any suggestions of a better way that does not involve so much work, there
> are a lot of these to do?
>
> Thanks MarkS

Nice solution.

Never seen that before and yet it's been around for ages.

Just shows how sophisticated Excel really is!!

Thanks

Barry



"Jean-Yves" wrote:

> Hi Mark.
>
> Use Range("A2").copyfromrecordset reADO
> Regards
> JY
>
> "MarkS" <mscholes.remove@southernhydro.com.au> wrote in message
> news:25154328-1E1A-4324-B845-6948E10181E5@microsoft.com...
> > Hi,
> > I use this code to get the data from a SQL Server database and put it into
> a
> > spread sheet
> >
> > ' Get Geneneration Data
> > sSql = "Select Date,hh,Dartm1,Eildon1,Eildon2 from
> mdff_Generation_Xtab"
> > & _
> > " Where Date >='20051023' And Date < '20051030' Order By Date,hh"
> > rsADO.Open sSql, connMelbourne, adOpenStatic, adLockReadOnly
> >
> > ' Copy Data
> > rsADO.MoveFirst
> > Counter = 2
> > Do Until rsADO.EOF
> > Range("A" & Counter).Value = rsADO!Date
> > Range("B" & Counter).Value = rsADO!hh
> > Range("C" & Counter).Value = rsADO!Dartm1
> > Range("D" & Counter).Value = rsADO!Eildon1
> > Range("E" & Counter).Value = rsADO!Eildon2
> > Counter = Counter + 1
> > rsADO.MoveNext
> > Loop
> >
> > I think there is a way of going straight to the spread sheet without going
> > through the load the record set copy to spread sheet route.
> >
> > Any suggestions of a better way that does not involve so much work, there
> > are a lot of these to do?
> >
> > Thanks MarkS
>
>
>