access table data to excel

Question

Hi I have a problem I have got stuck on.

I want to export my Access table to an Excel file, currently, I am doing that using DoCmd.TransferSpreadsheet but I want some kind of formatting to be done on the exported data. Can I format that data I am sending to Excel or do I have to write a macro in Excel that will format that data after it has been exported from Access?

Answer 1

I don't really have any experience with this domain, but please note that Excel has a row limit that is way smaller than that of Access.

Answer 2

It might be easier to start with an Excel spreadsheet that is preconfigured with your formatting. It also might be easier to use VBA from within excel, and pull the data from Access (and format it) rather than push from Access. Excel VBA will be better prepared to do your Excel formatting for you. And one is about as easy as the other. If you're trying to do this with Macros alone, it's still probably easier with Excel macros than Access macros.

Answer 3

This Excel macro retrieves data from your MS Access database:

Sub Makro1()
''
Const sDB = "c:\db1.mdb"
Const sSQL = "SELECT * FROM Table1"

    With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DSN=MS Access-database;DBQ=" + sDB + ";FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
        , Destination:=Range("A1"))
        .CommandText = Array(sSQL)
        .Name = "Query1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

Answer 4

if you use DoCmd.TransferSpreadsheet and create an original and then edit it so that the formatting is correct, you can then run DoCmd.TransferSpreadsheet again and it will update the file with the values but keep the formatting.

However, if a human then edits the file by adding new tabs, or adding calculations, etc, then the DoCmd.TransferSpreadsheet will no longer work and will fail with an ugly error message. So what we do in our enviroment is DoCmd.TransferSpreadsheet to an original file with formatting, and follow that up by copying the file to the users desktop, and then opening that copy so the user doesn't disturb the original file.

This approach is a minimum code, clean, and easy to maintain solution. But it does require a extra "source" or original file to be hanging around. Works in Access 2007.