Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Export lots of data to Excel (2003)

    I am responsible for sending reports out on a regular basis.

    End users, for some reason, want Excel, though their reports contain more than 65,000 rows of data.

    Does anyone have a QUICK automated way to split the data to tabs in a workbook?

    I have a way, but it is OH SO SLOW.

    Thanks!
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

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

    Re: Export lots of data to Excel (2003)

    You can use the following code from Excel to import a table or query. You must set a reference to the Microsoft DAO 3.6 Object Library.

    Sub ImportFromAccess()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim wsh As Worksheet
    Set dbs = DBEngine.OpenDatabase("C:AccessMyDatabase.mdb")
    Set rst = dbs.OpenRecordset(Name:="tblMyTable", Type:=dbOpenForwardOnly)
    Do While Not rst.EOF
    Set wsh = Worksheets.Add
    wsh.Range("A1").CopyFromRecordset Data:=rst, MaxRows:=65536
    Loop
    rst.Close
    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing
    End Sub

    Note: this won't work for Access reports.

  3. #3
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export lots of data to Excel (2003)

    After I posted this, I continued looking and found this code. I modified it to split the results into different workbooks based on a given field in the query/table. If a given set of data > 65k rows, it splits it into 2 tabs.

    WOW was it faster than my method.

    I hope this helps someone else.
    Attached Files Attached Files
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

  4. #4
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export lots of data to Excel (2003)

    Hans, I noticed your post after I posted mine.

    Yours looks like it would work, but I am needing to push from Access to Excel rather than pull data into Excel from Access.

    Thanks for the code, though. I will keep that for sure!
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

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

    Re: Export lots of data to Excel (2003)

    The code you found has a few extra bells and whistles, but it uses basically the same method as the code that I posted. It uses Automation to start Excel and import the data from Access into Excel. You could adapt my code to be used that way too. But since you already have a solution, there is no need for that.

  6. #6
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export lots of data to Excel (2003)

    It sure does...I did not notice that. <img src=/S/anigrin.gif border=0 alt=anigrin width=19 height=19>
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

Posting Permissions

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