Results 1 to 15 of 15
  1. #1
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Export data to Excel (2000)

    <P ID="edit" class=small>(Edited by WendellB on 19-Apr-04 12:58. To activate post links and mskb article)</P>Hi,
    I would like to export data to Excel but am having trouble getting my end result. I've been trying to combine <post#=300788>post 300788</post#> & <post#=287626>post 287626</post#> from here and a few others as well as <!mskb=210148>Microsoft Knowledge Base Article 210148<!/mskb> to try and get my end result, but I'm not savvy enough with VBA yet to know what I'm doing wrong or even if what I want to do can be done. I've attached a snp shot of a report created in Access and that is what I would like my Excel export to look like. Meaning. At the top of the excel spreadsheet i have basic fields of data, name of machine, JON, etc. and the rest of the rows are the detail data (parts). Now I would also like each sheet to be the RecSource field and then the detail area of that sheet grouped by Component. Each export would be one Workbook.

    My first question is can it be done that I have a sort of "header area" at the top of the spreadsheet? When I export now, it just looks like datasheet style.
    My 2nd question is how? <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Thanks,
    Deb
    Attached Files Attached Files

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Export data to Excel (2000)

    Hi Deb,
    I think the approach you want to take is to create the workbook as suggested in the first post, and then use automation as suggested in the MSKB article to create the header and footer information. If this is your first foray into Automation, you might want to take a look at our Automation Tutorial - it has links to several other MSKB articles on the subject. Also note that there is a separate Automation Help file that can be downloaded from the Microsoft web site.
    Wendell

  3. #3
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export data to Excel (2000)

    Thanks Wendell,
    That's what I've started to do. Part of my problem is with the coding. I don't quite understand what all the terminology means because I'm learning VBA from these posts and haven't had time to learn it the correct way. So it's a lot of trial and error when something goes wrong. I'll give it a try and then post again if I have trouble. Thanks.

  4. #4
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export data to Excel (2000)

    I have a question already. In the examples it has the following code:
    Dim mysheet As Object, myfield As Variant, xlapp As Object
    Set xlapp = CreateObject("Excel.Application")
    Set mysheet = xlapp.workbooks.Open("mec3s-c801w2kusersgnm4973My DocumentsParts DatabasesTestExport.xls").sheets(1)

    myfield = Me!strMachineName
    This code did work, but I'm going to have about 11 fields to export data to. I'm assuming I would do something like the following:
    myfield1=Me!field1
    myfield2=Me!field2
    etc.
    My question is how do I define all of these in the Dim statement. Do I have to put all 11 in there?
    Thanks,
    Deb

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Export data to Excel (2000)

    Sorry for the delay - I got sidetracked. In general you do want to define (Dimension) the variables you are going to use, as it makes code more understandable. One of the challenges of Automation is you must know the object model for the application you are automating as well as the object model for Access (or whatever application you are using as the client). As may have already discovered, Automation can be pretty tedious when you have to do formatting as well as the population of the document. For that reason, we often use some sort of template to start with that already has column widths and cell formatting done - it saves lots of repetive code. But you can do it either way. Another trick in automating either Word or Excel is to use their macro recorder to capture what you want to do - it helps a good deal when you aren't all that familiar with the object model. It doesn't always generate the most elegant code, and sometimes it simply doesn't work when you try to use it with Automation, but it can be helpful.
    Wendell

  6. #6
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export data to Excel (2000)

    I have the following code:
    Private Sub cmdExportExcel_Click()
    Dim DB As DAO.Database, Rs As DAO.Recordset
    Dim mysheet As Object, xlApp As Object
    Dim i As Integer, j As Integer, RsSql As String
    Dim CurrentValue As Variant, CurrentField As Variant
    Dim Workbook As Object, Sheet As Object
    Set DB = DBEngine.Workspaces(0).Databases(0)

    RsSql = "SELECT * FROM [qryDPLPrintReport] WHERE [lngMachineID]=[Forms]![frmDPLEntry]![cboMachJON];"

    Set Rs = DB.OpenRecordset(RsSql, dbOpenDynaset)

    Set xlApp = CreateObject("Excel.Application")
    xlApp.workbooks.Add
    Set mysheet = xlApp.workbooks.Open("mec3s-c801w2kusersgnm4973My DocumentsParts DatabasesQuoteForm.xls").sheets(1)
    Set Sheet = xlApp.activeworkbook.sheets(1)

    mysheet.cells(3, 2).Value = Me!strMachineJON
    mysheet.cells(4, 2).Value = Me!strMachID
    mysheet.cells(5, 2).Value = Me!strMachineSerialNo
    mysheet.cells(4, 9).Value = Me!strMachineName
    mysheet.cells(5, 9).Value = Me!dtm4130CompletionDate
    mysheet.cells(11, 2).Value = Me!sfrmPartsSubform!lngRecSourceID
    mysheet.Application.windows("QuoteForm.xls").Visib le = True

    j = 1
    For i = 0 To Rs.Fields.Count - 1
    CurrentValue = Rs.Fields(i).Name
    Sheet.cells(j, i + 1).Value = CurrentValue
    Next i

    j = 2

    Do Until Rs.EOF
    For i = 0 To Rs.Fields.Count - 1
    CurrentField = Rs(i)
    Sheet.cells(j, i + 1).Value = CurrentField
    Next i
    Rs.MoveNext
    j = j = 1
    Loop

    mysheet.Application.activeworkbook.Save
    mysheet.Application.activeworkbook.Close
    xlApp.Quit

    Set mysheet = Nothing
    Set Sheet = Nothing
    Set xlApp = Nothing

    End Sub

    This code is on the OnClick event of a button on a form. I'm getting the following error message when I click the button "Run-time Error '3061', Too few parameters, Expected 1".
    I get this error whenever I change the following line in the above code: RsSql = "SELECT * FROM [qryDPLForm] WHERE [lngMachineID]=[Forms]![frmDPLEntry]![cboMachJON];", when I put [lngMachineID]=141 I do not get the error message. I'm not sure what the message means.

    Can you help with that error message.

    I'm also not getting my correct output. It's sorta working. I've attached the Excel file I'm exporting to. The mysheet portion of the code seems to be working because it fills in the cells accordingly to the Excel file. The part I'm having trouble with is I want my subforms recordset to print starting in row 13. I have a form based off qryDPLForm then a subform based off tblComponent and another subform based off tblParts. I want the tblComponent & tblParts data to export into cell A13. I'm assuming I need to make a query with those 2 tables, but I'm having trouble getting the code correct and getting the data to go into Excel where I want.

    Thanks,
    Deb
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Export data to Excel (2000)

    When you run a query in the Access interface, it "knows" about Access forms and things like that.
    If you open a recordset on a query in DAO, references to Access forms are not recognized. The Jet engine runs at a lower level.

    Try this instead:

    RsSql = "SELECT * FROM [qryDPLPrintReport] WHERE [lngMachineID]=" & [Forms]![frmDPLEntry]![cboMachJON]

    Note that the reference to the form has been placed outside the quotes, so VBA will evaluate the value of [Forms]![frmDPLEntry]![cboMachJON] and assemble an SQL string with this value.

  8. #8
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export data to Excel (2000)

    Thanks for responding.
    When I made that change now I get this error message: "Too few parameters. Expected 1" and when I click on Debug it highlights this line:
    Set Rs = DB.OpenRecordset(RsSql, dbOpenDynaset)

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Export data to Excel (2000)

    We would have to know more about qryDPLPrintReport and/or qryDPLForm (I'm not sure which of these two you are using.)

  10. #10
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export data to Excel (2000)

    I believe the data I want is in qryDPLPrintReport. What else do you need to know about it? It combines about 5 tables if memory serves me correct. I'm not in front of my pc where the database is. It's basically a combination of all the tables that help make up the main form and subforms I use for data entry to get the data I want to export. Oh, also, can I specify which cell I want the data to start repeating in?
    Thanks,
    Deb

  11. #11
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export data to Excel (2000)

    Here's a scaled version of the database with the form. frmDPLEntry. I don't know if it would help.
    Attached Files Attached Files

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Export data to Excel (2000)

    The criteria lngMachineID = [Forms]![frmDPLEntry]![cboMachJON] is still part of qryDPLPrintReport. As long as you have a reference to a form in your query, you can't open a recordset on it.

  13. #13
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export data to Excel (2000)

    And to do what I want I have to open a Recordset? Meaning to export the data to Excel. Any suggestions on work arounds?
    Thanks,
    Deb

  14. #14
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export data to Excel (2000)

    Thanks Wendell that worked.

    Deb

  15. #15
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Export data to Excel (2000)

    Yes, you do need to open a recordset, and you are close to having it work. The line in your code <font color=blue><font face="Georgia">
    RsSql = "SELECT * FROM [qryDPLPrintReport] WHERE [lngMachineID]=" & [Forms]![frmDPLEntry]![cboMachJON]</font face=georgia></font color=blue>
    already contains the criteria for the SQL statement so you can remove the criteria from your query - because you have a parameter in the query, the code is failing. As an alternative, you could put the entire SQL string into the line above, and not reference the query at all - that is usually the way I do this sort of thing.
    Wendell

Posting Permissions

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