Article: Top 10 reasons to use Access with Excel

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.

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

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?

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.

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 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