Results 1 to 4 of 4
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ACCESS TO EXCEL - HELPPLEASE (Excel / Access)

    I would appreciate your help with the following:

    Using 'MS-DOS Batch File' I convert several text files to excel files, using:

    COPY "Cocuments and SettingsExample Text File.txt" "Cocuments and SettingsExample Excel File.xls"

    Can this be done in the same way, converting microsoft access database (mdb) to excel format.

    The only way I know how currently, is to open the database file,copy the data and paste into excel. But there must be an easier way??

    Thanks

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

    Re: ACCESS TO EXCEL - HELPPLEASE (Excel / Access)

    You can't just convert an Access database to an Excel workbook - an Access database can hold tables, queries, forms, reports, macros and VBA code.

    You can export an entire table (or query) from Access to an Excel workbook, using File | Export..., or in VBA using DoCmd.TransferSpreadsheet.

    You can import an entire table (or query) into Excel from Access, using Data | Import External Data | New Database Query..., or in VBA using QueryTables.Add or CopyFromRecordset.

    You cannot do this directly in a batch file, but you could write VBscript that automates Acces or Excel.

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ACCESS TO EXCEL - HELPPLEASE (Excel / Access)

    Hi Hans, Thanks for your response....

    I use excel for most of my working day (such fun :-)).... However,I know nothing about Access, other than it being a source of data to me that I copy accross to xls. Can I check that I am understanding you correctly?

    I can, using a macro in excel - Data / Import External Data / Import Data from a mdb file ?? at the click of a button??

    Does that mean I can sack the old 'ctrl c' 'alt esv'.Is the end of the world nigh???

    :-)

    Many Thanks

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

    Re: ACCESS TO EXCEL - HELPPLEASE (Excel / Access)

    The menu command in Excel is Data | Get External Data | New Database Query...
    The VBA equivalent of this menu command is

    ActiveSheet.QueryTables.Add connection, targetcell

    Look up the Add method of the QueryTables collection in the VBA help.

    You don't need to open Access for this - you can do it all from Excel.

Posting Permissions

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