Results 1 to 10 of 10
  1. #1
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670

    VBA To Export Recordset To Excel

    So I thought I was getting advanced here and writing my own procedure to export a recordset to Excel. But it seems that I am only building what would be the query string to generate a query that would house the data that I want to export.

    However, I am building the query string with the actual data, so it is more of a CSV list that I want to export, oh I am running in circles here!



    What I am wanting to do, and I hope I am not far off from it - is to populate row 1 with header information - check that is easy for me to do - then starting in row 2 cell A start populating the data from my recordset, and after writing each variable, then move over one column, so it would write like such

    A2, B2, C2, D2, E2, etc etc

    This is the syntax I had, but obviously I am screwing the pooch here. Can someone get me on the right track of exporting this recordset to Excel instead of building a long CSV String with it?

    Code:
    	strB = locale & ", "
    
    
    	With rsSub
    		If Not .EOF And Not .BOF Then
    			.MoveLast
    			.MoveFirst
    			Do While Not .EOF
    				purchaseItem = .Fields(1).Value
    				itemDesc = .Fields(2).Value
    				numpurchase = .Fields(3).Value
    					
    					If sConcat <> "" Then
    						sConcat = sConcat & ", " & purchaseItem & ", " & itemDesc & ", " & numpurchase & ", "
    					Else
    						sConcat = purchaseItem & ", " & itemDesc & ", " & numpurchase
    					End If
    	
    			 .MoveNext
    		  Loop
    	   End If
    	End With
    		
    		rsSub.Close
    		
    		strB = strB & Left(sConcat, Len(sConcat) - 1)

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'm not seeing the part where you try to write to the spreadsheet file? Methinks this needs looping, as in select cell A1 and write the field(n).Name, then move to the next field as well as the next spreadsheet column and repeat. Then start a new outer loop for copying over the Access recordset field values beginning with record 1, with an inner loop to move from field to field. When you reach rs.fields.count -1 in the loop counter (or whatever the hard coded number is) you exit the inner loop and movenext in the outer loop. All this is predicated on the assumption you're using Automation to write the values. Did you consider TransferSpreadsheet? And why not use a template as opposed to writing spreadsheet column names?

    Maybe it's just your habit, but if you don't need the recordset count, there's no need to movelast then movefirst.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,819
    Ken Snell site has lots of great examples, including CopyFromRecordset method http://www.accessmvp.com/KDSnell/EXC...m#WriteRstFile
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    @Micron & @June07 - thank you for that valuable insight - I know have my recordset exporting to Excel!!!!

    I have one issue remaining tho, and that is that the data is output to columns A and B ONLY. I want my data to be output to row 2 and ONLY row 2 - moving to the right, so the data should be A2, B2, C2, D2 etc and so forth

    What would I need to tweak in this syntax so it goes horizontal instaead of vertical?

    Code:
    Do While Not rsSub.EOF
         colNo = 1
         rowNo = rowNo + 1
         For Each fld In rsSub.Fields
           xcelApp.Cells(rowNo, colNo) = fld.Value
         colNo = colNo + 1
       Next fld
       rsSub.MoveNext
    Loop

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,819
    Sounds like a CROSSTAB query.

    Provide sample data. How many possible records do you want to pivot?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    Quote Originally Posted by June7 View Post
    Sounds like a CROSSTAB query.

    Provide sample data. How many possible records do you want to pivot?
    It depends on how many records are on the imported spreadsheet. In this instance it is 850.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I think the syntax is .Offset(row,column) so (0,1) if the object is a Range and you want to move 1 column to the right. -1 for left. I have used this as xlc.Offset(0,1) to move one column. xlc is a variable for a range, which Excel considers a single cell as a valid Range object. You could easily google how to move across columns or try the Offset method using your Cells reference or Range(A1).Offset.... Range would require the parent object references, which is probably the worksheet at least. Sorry, it's been a while.

    In future, consider creating variables for more of the Excel side objects to make them easier to manipulate, such as

    Dim xls As Object, xlc As Object, xlw As Object = sheet, cell and workbook
    Dim rnge1 As String, rnge2 As String = named ranges
    Dim cellStart As String = a variable to calculate then work with the A1 if no headers, or A2 if headers are present
    and the starting range was set via Set xlc = xls.Range(cellStart)
    Stuff like that. Of course, you don't have to use variables if positions are fixed.
    Last edited by Micron; 11-13-2017 at 09:36 PM. Reason: spelin

  8. #8
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    @Micron - good advice on the using variables and setting the references. That was also included in the link by @June07 -

    I am closer with this syntax, however it writes all of the first field then all of the second instead of first, second, first, second like I need


    'Do While rsSub.EOF = False
    ' For lngColumn = 0 To rsSub.Fields.Count - 1
    ' xlc.Offset(0, lngColumn).Value = rsSub.Fields(lngColumn).Value
    ' Next lngColumn
    ' rsSub.MoveNext
    ' Set xlc = xlc.Offset(0, 1)
    'Loop

  9. #9
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    More code might help. Don't know what xlc is to begin with. Looks like you'd write at xlc then offset 1 column, write again, offset 1 column, write again for as many fields as there are. Then you move to new record and offset but from where? Would have thought you need to offset 1,0 after moving to new record, not 0,1 .

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,819
    So you want to pivot two fields of 850 records to end up with one row of 1700 columns?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 05-17-2017, 10:45 AM
  2. Replies: 3
    Last Post: 04-07-2016, 04:36 AM
  3. Replies: 3
    Last Post: 03-05-2015, 04:15 PM
  4. Export Recordset to Excel
    By bimcompu in forum Programming
    Replies: 1
    Last Post: 01-08-2014, 05:53 PM
  5. Export Data w/FSO - Open object recordset failed
    By JayZoll in forum Programming
    Replies: 1
    Last Post: 11-12-2012, 01:38 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums