Copy specific fields from recordset to excel specific columns

[teasugar]

hi there,

i looked around the internet and this forum to find a solution but was unable to do so. so here is the question:

i make this SQL based recordset and then want to copy specific field data into a specific excel column ( say for example i have this field called Sender_Name in the recordset, i want to copy all names from this field into a specific column in excel).

i tried :

xlws.columns(1)=rst.fields(0)

where xlst would be the excel worksheet i am copying to, and the rst = recordset. but the code would return only the first record in the field and copy all that in column one of excel all the way...say the first record in the recordset in field(0) had the value = "john"...it would only copy John to column 1 but not the rest of the names....


any help would be appreciated.

thank you

 

[pbaldy]

Look at CopyFromRecordset in Excel VBA help, or you'll need to loop through your recordset and insert one value at a time (using a variable for the row that you increment each pass).

 

[teasugar]

thanks for the reply

the sad thing with copyfromrecordset, i have tried it before, is that it doesn't support Automation. e.g:

xlsheet.columns(1).copyfromrecordset recordset.fields(0)

i get an error telling me you can't Automate it, ie, i can't tell the procedure to only copy that specific field. unless there is another way or something

and with looping, can you show me a sample code.

thanks again

 

[DCrake]

Create the recordset with only one field in it then refer to that in your CopyFromRecordset command.

.CopyFromRecordset Rs

 

[teasugar]

@ dcrake:

lol i thought about that once myself, but that just kills the whole purpose of 'automation' and programming. i'm nearly doing everything myself and it becomes really inconvenient ...the true thing is i want to find a code that will prevent me from doing that, couz with it other problems could rise, where you could have in the final spreadsheet, unmatching records...i'm thinking

but anyhow thanks for the suggestion mate


still waiting to get some help on the loop idea.

 

[boblarson]

the sad thing with copyfromrecordset, i have tried it before, is that it doesn't support Automation.

Umm, yes it does. But you have to use it in the correct way. Your code below is completely incorrect

xlsheet.columns(1).copyfromrecordset recordset.fields(0)

is NOT the way to do it. The correct way will only take the entire recordset and put it into the worksheet. The correct way is (using Excel COM code from Access):

Dim rst As DAO.Recordset
Dim objXL As Object
 
Set rst = CurrentDb.OpenRecordset ("Select SingleFieldNameHere FROM TableOrQueryNameHere WHERE SomeField = " & Me!SomeFieldOnTheForm)
 
Set objXL = CreateObject("Excel.Application")
 
With objXL
.Visible = True
.Workbooks.Open("C:\YourPathAndFileName.xls")
[B][COLOR=red].Activesheet.Range("C2").CopyFromRecordset rst[/COLOR][/B]
 
.ActiveWorkbook.Close True
.Quit
End With
 
rst.Close
Set objXL = Nothing
Set rst = Nothing

 

[teasugar]

thanks bob, what you showing me is what dcrake is suggesting. looks like thats my only way - making about 30 recordset for the massive no. fields i have

 

[DCrake]

Which is exactly what I advocated in my previous post Bob.

 

[boblarson]

@ dcrake:

lol i thought about that once myself, but that just kills the whole purpose of 'automation' and programming.

No, not really because you can set up a function which does the actual export based on parameters you pass. So you, in essence need to set it up differently than you might expect but that doesn't mean that automation is not there.

So, how do you define which columns you want from your recordset going into which columns in the Excel file? If you aren't doing a bulk output then CopyFromRecordset isn't necessarily the right tool for the job.

i'm nearly doing everything myself and it becomes really inconvenient ...the true thing is i want to find a code that will prevent me from doing that, couz with it other problems could rise, where you could have in the final spreadsheet, unmatching records...i'm thinking

That's too broad of a statement for reality. So, my question would still be - how do you define which columns of an existing recordset go to which columns of the Excel spreadsheet. So, you have to define it somewhere, so you can define it by parameters you pass a function.

As for looping it would simply be something like this:

Dim objXL As Object
Dim xlWB As Object
Dim xlWS As Object
Dim rst As DAO.Recordset
Dim lngCount As Long
 
Set objXL = CreateObject("Excel.Application")
objXL.Visible = True
 
Set xlWB = objXL.Workbooks.Open("C:\YourFileAndPathToExistingFile.xls")
 
Set xlWS = xlWB.Worksheets("SheetNameHere")
 
Set rst = CurrentDb.OpenRecordset("Select * FROM TableOrQueryNameHere")
 
lngCount = 1
 
With rst
   Do Until .EOF
      xlWS.Range("A" & lngCount).Value = rst!FieldNameHere
      xlWS.Range("C" & lngCount).Value = rst!Field2NameHere
      xlWS.Range("F" & lngCount).Value = rst!Field3NameHere
     ' and so on
      .MoveNext
      lngCount = lngCount + 1
   Loop
 
rst.Close
Set rst = Nothing
 
xlWB.Close True
ObjXL.Quit
 
Set ObjXL = Nothing

 

[boblarson]

Which is exactly what I advocated in my previous post Bob.

And I just expanded upon it with a full set of code to better show the syntax and usage.

 

[teasugar]

So, how do you define which columns you want from your recordset going into which columns in the Excel file? If you aren't doing a bulk output then CopyFromRecordset isn't necessarily the right tool for the job.

thats about half of my problem, and thats why you were seeing me using the recordset.field(x) method over in my first code you rejected...i wanna to refer to only speficic fields in the recordsets and copy that, but with copyfromrecordset you can't specify fields and thats why i meant it can't automate.

thanks for the loop, i'll look into it

 

[teasugar]

the loop did the job, i'm very thankful to the suggestion and to its expansion by bob. just with the code bob, you forgot a last 'end with' statement.

and when i wanted to refer to a specific cell i used this method:


to past [field1] starting from D3 and [field2] from E2, i did the following, correct me if there is a more appropriate method:


so

lngcount =3 ' changed it to my value
lng = 2 'introduced another variable already above
With rst
   Do Until .EOF
      xlWS.Range("D" & lngCount).Value = rst!field1
      xlWS.Range("E" & lng).Value = rst!field2
     ' and so on
      .MoveNext
      lngCount = lngCount + 1
   Loop
End With

 

[boblarson]

I was working on a sample on how to do it all with copy from recordset but it is really complex. So, I'll just say good that you have the loop going and we'll let it go at that.

 

[hansmoleman]

Little bit of a timewarp but if someone else wanders to this post there are additional parameters that can be input to the 'CopyFromRecordset' method.

The first one is unknown, the others are described below:

outputrange.CopyFromRecordset rs, {max rows}, {max columns}
 
i.e. outputrange.CopyFromRecordset rs, 4, 1

I believe the first column is zero so in the example above the first 2 fields of the first 4 records would be output; useful if the data you need is in the first/second field.