Comments

# Mike H said on July 9, 2008 4:31 PM:

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.

# clintc said on July 9, 2008 5:42 PM:

Mike--we fixed this in this version for import.

# zup said on July 9, 2008 6:22 PM:

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?

# clintc said on July 9, 2008 6:58 PM:

Zup,

The site is set to disable comments after 1 week because we had so many problems with porn posts filling up posts that we weren't actively monitoring.

The three column tool bars is pretty easy using ribbon XML. You just don't use specify large icons. There are third party tools for generating Ribbon XML available. You can see past posts about tools by clicking on the ribbon item in the tag cloud.

The screen shots in the designing apps simply cropped out the ribbon. Sorry to mislead you. It is possible to turn off the ribbon and just have the office button chrome.

# Ken Hockley said on July 9, 2008 11:57 PM:

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

New Comments to this post are disabled