Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Sep 2005
    Posts
    240

    Unanswered: export to columns in excel

    Hi folks,

    I hope someone can help me with this.
    I have been asked to try and export some of our data to excel.

    Now, I have got the basic code to create the workbook and recordset to export the data.
    But rather than just export the data, I would like to be able to place fields in the recordset into a specific column on the workbook.

    I have been playing around with the Range property without much success (see code below), I get the error:

    Run-time error '430':

    Class does not support Automation or does not support expected interface
    Code:
    Dim xlApp As Excel.Application
    Dim xlSheet As Excel.Worksheet
    Dim xlWorkbook As Excel.Workbook
    Dim xlRng As Excel.Range
    Dim rs As DAO.Recordset
    Dim sSQL As String
    Dim strSheetName As String
    
    sSQL = " SELECT " & gstrTableName2 & ".ID_Number AS [Student ID], " & gstrTableName2 & ".Name," & _
             " " & gstrTableName2 & ".CalcPA AS [Predicted Attainment], " & gstrTableName2 & ".CalcGrade AS Grade" & _
             " FROM " & gstrTableName2 & ""
    strSheetName = "VA & DTT"
    
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    Set xlWorkbook = xlApp.Workbooks.Add
    Set rs = Application.CurrentDb.OpenRecordset(sSQL)
    
    Set xlSheet = xlWorkbook.Sheets(1)
    For lvlColumn = 0 To rs.Fields.Count - 1
        xlSheet.Cells(1, lvlColumn + 1).Value = _
        rs.Fields(lvlColumn).Name
    Next
    
    'Change the font to bold for the header row
    xlSheet.Range(xlSheet.Cells(1, 1), _
    xlSheet.Cells(1, rs.Fields.Count)).Font.Bold = True
    
    'Add a border to header row cells
    With xlSheet.Range(xlSheet.Cells(1, 1), _
    xlSheet.Cells(1, rs.Fields.Count)).Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With xlSheet.Range(xlSheet.Cells(1, 1), _
    xlSheet.Cells(1, rs.Fields.Count)).Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With xlSheet.Range(xlSheet.Cells(1, 1), _
    xlSheet.Cells(1, rs.Fields.Count)).Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With xlSheet.Range(xlSheet.Cells(1, 1), _
    xlSheet.Cells(1, rs.Fields.Count)).Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    
    With xlSheet
        .Range("A2").CopyFromRecordset rs.Fields("Student ID")
        .Range("B2").CopyFromRecordset rs.Fields("Name")
        .Range("C2").CopyFromRecordset rs.Fields("Predicted Attainment")
        .Range("E2").CopyFromRecordset rs.Fields("Grade")
        .Name = strSheetName
    End With
    Thanks

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Try using
    .Range("A2").Value=rs![Student ID]

    I always use .Formula instead of .Value, but .Value should work.
    Inspiration Through Fermentation

  3. #3
    Join Date
    Sep 2005
    Posts
    240
    Thanks, it only seems to work for one row.

    Is there no way to get it to fill the column down, or am I going to have to loop though the recordset as well?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Copyfromrecordset dumps the whole lot - you can't (AFAIK) do a column at a time. Let me know if I am wrong - I haven't checked the documentation.

    If this isn't possible then I would look at the problem the other way round - dump the recordset into excel and then move the columns around to where you want them.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    That's probably the best way to do it. Dump all the data into columns A-D,
    and then -using VBA- insert a column between C & D and populate the formula.

    I'll have to look more closely at the .copyfromrecordset. I always just loop through the recordset, but I'm dealing with small amounts of data so it doesn't matter.
    Inspiration Through Fermentation

  6. #6
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    As pootle says, it copy the whole recordset, so is this what you are trying to do
    Code:
    With xlSheet
        .Range("A2").CopyFromRecordset rs
        .Name = strSheetName
    End With
    If you want the field names as well, you will need to iterate through the fields names along row 1, ie rs(i).Name

    I always use .Formula instead of .Value, but .Value should work
    Why use Formula when assigning a number or text?
    I use neither, just Rang("A2") = whatever (Value is the default).

    Tend to only use Formula if I need to find out what the formula is in a cell.

    HTH

    MTB

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by RedNeckGeek
    I'll have to look more closely at the .copyfromrecordset. I always just loop through the recordset, but I'm dealing with small amounts of data so it doesn't matter.
    Yeah - that worked great for me until I needed to do big dumps of data. You really don't want to write 10MB of data a cell at a time I can tell you!
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Quote Originally Posted by MikeTheBike

    ...
    Why use Formula when assigning a number or text?
    I use neither, just Rang("A2") = whatever (Value is the default).
    ...
    I admit to (in the past) a general laziness in that area. Once I found something that worked, I tended to stop looking for a better way. That's one of the reasons I hang around here, now.
    Inspiration Through Fermentation

  9. #9
    Join Date
    Sep 2005
    Posts
    240
    Thanks folks.
    I've successfully created code to iterate through the recordset.

    Also does anyone know if there is a reference somewhere for the various excel object properties?

    The intellisense doesn't show me anything for the excel object.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Look in Excel help or on the web.

    You should be getting intellisense though.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    I am a little puzzled why you do not get any object/properties from itellisense as you are early binding (or you would get compile error with Excel constants). Silly question, I assume it is turned on?

    I see pootle beat me to it. Not for the first time!

    MTB

  12. #12
    Join Date
    Sep 2005
    Posts
    240
    Aha, just found it here.
    Under the 'References' link.

    Quote Originally Posted by MikeTheBike
    Silly question, I assume it is turned on?
    By turned on, I assume its the 'Auto Syntax Check' property in the options.
    If so, then yes it is.

    I think some intellisense works, just not all of it.
    I noticed it when trying to add a border or change the font to bold.
    The range collection is picked up in intellisense, but the properties for it aren't.

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah yes - I know what you mean now. If I remember correctly if you do not but in the code for an item in the collection you get intellisense. You can then "backfill" your code. Make sense?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Sep 2005
    Posts
    240
    Quote Originally Posted by pootle flump
    You can then "backfill" your code. Make sense?
    Nope.
    But one other way I am able to get intellisense is by using the with statement e.g:

    Code:
    'Change the font to bold for the header row
    With xlSheet.Range(xlSheet.Cells(1, 1), _
    xlSheet.Cells(1, 6))
        .Font.Bold = True
    End With
    Last edited by KevCB226; 07-27-07 at 06:46.

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Heh.

    If you type:
    Code:
    xlSheet.Range.
    do you get intellisense?
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •