Microsoft Access Team Blog

One thing that bugs me about linking to an Excel sheet from Access is that, at least in versions previous to 2007, it doesn't let you change the default field definitions. If Access decides to treat a ZIP code field as numeric instead of text, it loses the leading zeroes. For that reason I usually end up exporting to tab delimited first, then using the Access text linker which lets you change the field format.

Any chance that questions posted to the recent 'ribbons' article will be answered here? Some of those generated interest but the post was closed for comments before any responses came back.

Why does this blog close comments so quickly anyways?

Well done

I use the automation export to Excel extensively and find it a very powerful tool - customers love it and it is so easy to do

The following small procedure dumps data to Excel without worrying about the nature of the table or query it is dumping from. If anyone requires formatted data to be dumped it is very easy to do within the procedure using the "type" of field you are exporting.

The code follows - feel free to copy and use

This is the code to call the procedure that outputs a table to an excel file

I use global variables so the procedure can be called from any form or code

(passing parameters would achieve the same goal)

In this example "t_temp_renewals_due" is a flat table of all insurance renewals due in a period

(it could be any flat table or query)

 'export the table data to an excel file

 report_heading = "Renewals due in the period : " & Format(Me!from_date, "dd mmm yyyy")

 report_heading = report_heading & "  to  " & Format(Me!to_date, "dd mmm yyyy")

 report_heading = report_heading & Chr(10) & "created on : " & Format(Now, "dd mmm yyyy  hh:mm")

 table_name = "t_temp_renewals_due"

 export_path = "c:\data\access\"

 template_path = "c:\templates\"

 write_to_excel_table

This is the actual procedure

(t_data_dump.xls  is a blank Excel workbook)

Public Sub write_to_excel_table()

 On Error GoTo e1

 'open an excel session

 Set xlapp = New Excel.Application

 Set xlworkbook = xlapp.Workbooks.Open(template_path & "t_data_dump.xls")

 Set xlworksheet = xlworkbook.Worksheets(1)   '("sheet_name")

 screen_pos = 1   'position on excel sheet to start writing

 hold_crit = "select * from " & table_name

 Set rs_report = curr_db.OpenRecordset(hold_crit)

 If rs_report.RecordCount > 0 Then

    'write the table name date etc

    xlworksheet.Range("a" & Format(screen_pos)) = report_heading

    screen_pos = screen_pos + 2

    'write the field headings

    num_of_fields = rs_report.Fields.Count

    curr_field = 0

    While curr_field < num_of_fields

      xlworksheet.Cells(screen_pos, curr_field + 1).Value = rs_report.Fields(curr_field).Name

      xlworksheet.Columns(curr_field + 1).ColumnWidth = 20

      curr_field = curr_field + 1

    Wend

    'now write the data

    rs_report.MoveLast

    num_of_recs = rs_report.RecordCount

    rs_report.MoveFirst

    curr_rec = 0

    While curr_rec < num_of_recs

      screen_pos = screen_pos + 1

      curr_field = 0

      While curr_field < num_of_fields

        xlworksheet.Cells(screen_pos, curr_field + 1).Value = rs_report.Fields(curr_field).Value

        curr_field = curr_field + 1

      Wend

      curr_rec = curr_rec + 1

      rs_report.MoveNext

    Wend

 End If

 rs_report.Close

 'remove the file if it exists

 hold = Dir(export_path & table_name & ".xls")

 If Len(hold) > 0 Then

    Kill export_path & table_name & ".xls"

 End If

 'close the file and clean up

 Set xlworksheet = Nothing

 xlworkbook.SaveAs export_path & table_name & ".xls"

 xlworkbook.Close False   'need a comma here if ver is prior to 2007  

 Set xlworkbook = Nothing

 Set xlapp = Nothing

 'crlf is a variant = chr(13) & chr(10)

 MsgBox "The Excel file has been created" & crlf & crlf & export_path & table_name & ".xls", 0, "Goldsoft system message"

 Exit Sub

e1:

 MsgBox Error$

 Err.Number = 0

 'Resume

End Sub