4

I have an Excel spreadsheet that is filled with a ton of data which is organized by column (a column for names, a column for occupation etc.). I have recently moved all of this data into Microsoft Access 2010 to work with, but I do require to move this data back and forth between Access and Excel. Since then, I have made a lot of changes to the data and I now want to export this data back from Access into Excel, with the data appearing in the same columns that they were imported in from. I have tried exporting the data back into Excel, but it doesn't look the same as the excel file that I originally exported the data from.

I was wondering if there is a way to export all of the data in Access back into Excel so that it is formatted exactly the same way it was before it was exported into Access

Sorry if this is a noobish question, I'm new to Access and don't have much experience with it yet.

Thanks

  • Did you try the other way around: Query the Access table(s) from (a copy of) your Excel file? -- How exactly is the exported Excel file different from what you expect / need? – Abecee Jul 7 '15 at 19:48
1

The first and easiest option is to select the "External Data" option on the top and select Excel from the Export Data options. This lets you customize the export location and if you want to keep the Access formatting. If you wanted a different format you would have to manually change the column order before exporting to achieve this. If your referring to it not looking the same as before in Excel because it was a table there is a quick fix. Use the keyboard shortcuts ctrl + Home > ctrl + A > ctrl + L to create a table object which formats it nicely.

You can save this manual export process and re run it manually or in a macro if you choose to in the future. This saves time with manually recreating the export each time.

A more advanced option would be creating a VBA module to do the exporting and formatting keyboard shortcut for you. I have the code you can copy and paste from Access to Excel Export Code. You could additionally customize this code to format the columns in the correct order. I would recommend looking into the fld.OrdinalPostion property to do this here Microsoft Field.OrdinalPostion

1

To export all the tables on Linux try this command:

mdb-tables -d ',' database.mdb | xargs -L1 -d',' -I{} bash -c 'mdb-export database.mdb "$1" >"$1".csv' -- {}

You can use mdbtools also into windows via WSL (Ubuntu on Windows or Debian on Windows): Then install it in console with:

sudo apt install mdbtools
0

Using Linux, there is a mdbtools packaged by Linux/Debian as the mdbtools

You can read about what it offers here, but pay special attention to

  • mdb-tables (to list the tables in the archive)
  • mdb-export to generate a CSV from the respective tables

It's pretty easy and straight forward to use.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.