DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Access Table to Excel via VB

  1. #1
    Join Date
    Nov 2005

    Access Table to Excel via VB

    Hello all, new member and first time poster (along with teaching myself VB). I have the below code that works great, it runs a SQL query on an Access DB and then passes the data to an Excel spreadsheet. What I would like to know is how can I pass the data in a table that already exists in Access to Excel? In the long run I think it would save time and effort to run an append/update query in Access and pull the data from the table that the query updates due to possible query updates/changes that might need to be done in the future.

    I know there is a way to call the table instead of using “MyRecordset.Open MySQL” but I’m not having any luck finding the info.

    Thanks in Advance….

    Public Function ExcelTestFunction()

    Dim cnn As ADODB.Connection
    Set cnn = CurrentProject.Connection
    Dim MyRecordset As New ADODB.Recordset
    MyRecordset.ActiveConnection = cnn

    Dim MySQL As String
    MySQL = "SELECT Dates, ATM, Voice, Circuit,"
    MySQL = MySQL + " Router, Site, [Server-Branch], [Server-Back Office],"
    MySQL = MySQL + " Application , Component_Total, BellSouth, DeltaCom,"
    MySQL = MySQL + " EFMark, NCR, SBC, Regions, Other,"
    MySQL = MySQL + " Vendor_Total FROM Chart_Totals ORDER BY Chart_Totals.Dates;"

    MyRecordset.Open MySQL

    Dim MySheetPath As String
    MySheetPath = "C:\Documents and Settings\All Users\Desktop\CompVend.xls"

    Dim XL As Excel.Application
    Dim XLBook As Excel.Workbook
    Dim XLSheet As Excel.Worksheet

    Set XL = CreateObject("Excel.Application")
    Set XLBook = GetObject(MySheetPath)

    'Make Excel Visible
    XL.Visible = True
    XLBook.Windows(1).Visible = True

    Set XLSheet = XLBook.Worksheets(1)

    'copy record set
    XLSheet.Range("A2").CopyFromRecordset MyRecordset

    'clean up
    Set cnn = Nothing
    Set XL = Nothing
    Set XLBook = Nothing
    Set XLSheet = Nothing

    End Function

  2. #2
    Join Date
    Nov 2005


    Here is what I was able to get to work... "

    DoCmd.TransferSpreadsheet acExport, , "Chart_Totals", "C:\Documents and Settings\All Users\Desktop\CompVend.xls", True, "A1:R11"

    Hope this will help anyone out in the future...

  3. #3
    Join Date
    Dec 2005
    The second way is easiest, yes.... however... it is often not right for everybody...

    Here is a perfect way that always works... it may be slow on large db's... but, it's nice because you can always 'clean' it up through code if you'd like....

    '''''*****************CODE START******************'''''
    'This code was originally written by Dion Douglas
    'It is not to be altered or distributed,
    'except as part of an application.
    'You are free to use it in any application,
    'provided the copyright notice is left unchanged.
    'Code Courtesy of
    'Dion Douglas
    Private Sub MakeXLS(mytable As String)
    'Declare variables
    Dim rs As Recordset, xls
    Dim I As Integer, X As Integer
    Dim objActiveWkb
    'Start giving them values..
    Set rs = CurrentDb.OpenRecordset(mytable)
    Set xls = CreateObject("Excel.Application")

    'Make Excel visible.. it's always fun to watch =)
    xls.Application.Visible = True
    'add a new wkb
    'set up a new object

    Set objActiveWkb = xls.Application.ActiveWorkBook
    'Set up the 'field names' (the first row)
    For I = 0 To rs.Fields.Count - 1
    objActiveWkb.worksheets(1).cells(1, (I + 1)) = rs.Fields(I).NAME
    Next I
    'Now move the data over
    X = 2
    Do Until rs.EOF
    For I = 0 To rs.Fields.Count - 1
    objActiveWkb.worksheets(1).cells(X, (I + 1)) = rs(I)
    Next I
    X = X + 1
    'NOTE: You CAN always do formating while your looping... or you can format at the end...i.e.:
    objActiveWkb.worksheets(1).Range("1:1").interior.colorindex = 35
    objActiveWkb.worksheets(1).Range("1:1").border.colorindex = 1
    'You can also automate saving and such, but that is trully up to you...
    'close everything...
    Set objActiveWkb = Nothing
    Set rs = Nothing
    Set xls = Nothing

    End Sub

    '''''**************** CODE END ********************''''

    Then, simply call it whenever you please with:
    MakeXLS("your table name here")
    Last edited by T3HRed; 12-13-2005 at 03:21 PM.

Similar Threads

  1. Access and Excel using VB
    By John in forum VB Classic
    Replies: 2
    Last Post: 12-13-2005, 03:24 PM
  2. "insert into" vb to access 2000 table
    By mahesh in forum VB Classic
    Replies: 3
    Last Post: 05-29-2003, 03:29 PM
  3. "insert into" vb to access 2000 table
    By mahesh in forum VB Classic
    Replies: 1
    Last Post: 05-22-2003, 08:48 PM
  4. Access 97 to 2000 Conversion Error
    By Michelle in forum VB Classic
    Replies: 8
    Last Post: 04-19-2002, 05:01 PM
  5. Read an Access table into VB for password confirmation
    By Joel Layman in forum VB Classic
    Replies: 8
    Last Post: 04-12-2002, 10:57 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
HTML5 Development Center
Latest Articles
Questions? Contact us.
Web Development
Latest Tips
Open Source

   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center