exporting a query/table to excel and formatting it automatically 1

[Davide77]

Do you have an idea on how to automatically export and format a table from access to excel. Formatting: change colors of specific cells, merge cells and this kind of things.
thanks

 

[xlbo]

Whilst this can be programmed from access (have a look at the transferspreadsheet method), it can also be done FROM excel even more easily, using Data>Get External data. Choose Access as your driver and follow the wizard...

Rgds, Geoff
[blue]Experience is something you don't get until just after you need it[/blue]
We want to help [red]you[/red] Help us by reading this FAQ 1st faq222-2244

 

[Davide77]

Hallo xlbo,
I looked the transferspreadsheet method but it doesn't seem to allow formatting of the output table...

 

[xlbo]

Nope - you'd have to do the transfer in code and then open up the excel object to format it - that's why I suggested having a look at pulling the data in FROM excel. If that is not an option, I can provide some sample code for working with the excel object

Rgds, Geoff
[blue]Experience is something you don't get until just after you need it[/blue]
We want to help [red]you[/red] Help us by reading this FAQ 1st faq222-2244

 

[Davide77]

xlbo, would be nice if you post some code that allow to handle excel tables from access..

thanks

 

[ggriffit]

There is some stuff at

http://www.greggriffiths.org/webdev/both/excel/

which covers creating Excel files from ASP, but the information and the links there should be a good grounding and some of the links will be useful.

 

[xlbo]

ok - this is from a similar thread - it exports the contents of a table to excel:

Sub Export_To_Excel()

Dim strSql As String
Dim dBase As DAO.Database
Dim rs As DAO.Recordset
Dim i As Integer
Dim uRow As Integer
Dim xlApp As Object
Dim Sht As Object
 
    DoCmd.SetWarnings False
    [COLOR=green] 'setup query to get all records from a table[/color]
    strSql = "SELECT * FROM [TWPW AGM];"

    Set dBase = CurrentDb()
    Set rs = dBase.OpenRecordset(strSql, dbOpenDynaset)
    [COLOR=green] 'Create Excel object[/color]
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Workbooks.Add
    xlApp.Visible = True
    Set Sht = xlApp.ActiveWorkbook.Sheets(1)
        uRow = 1
       With rs
        [COLOR=green] ' Loop through the field names[/color]
        For i = 0 To .Fields.Count - 1
            Sht.Cells(uRow, i + 1).Value = rs.Fields(i).Name
        Next i
        uRow = 2
       End With
        [COLOR=green] ' Loop through the records and copy them to worksheet.[/color]
        Do Until rs.EOF

            For i = 0 To rs.Fields.Count - 1
                Sht.Cells(uRow, i + 1).Value = rs(i)
            Next i
            rs.MoveNext
            uRow = uRow + 1

        Loop
        [COLOR=green] 'Here's where the formatting happens[/color]
        With Sht
           .Name = "Give the sheet a name"
              [COLOR=green] 'set cell format to general[/color]
           .Range("A1:A200").NumberFormat = "@"
              '[COLOR=green] 'make titles bold[/color]
           .Range("A1:F1").Font.Bold = True
        End With
[COLOR=green] 'Other stuff[/color]
ActiveWorkbook.SaveAs "Full Path & Name go here"
[COLOR=green] 'Clear variables[/color]
Set Sht = Nothing
xlApp.Quit
Set xlApp = Nothing
Set rs = Nothing
Set dBase = Nothing
End Sub

Rgds, Geoff
[blue]Experience is something you don't get until just after you need it[/blue]
We want to help [red]you[/red] Help us by reading this FAQ 1st faq222-2244

 

[Davide77]

thanks xlbo, this is going to help me