Exporting to Microsoft Excel

  • Thread starter Thread starter Charles Evans
  • Start date Start date
C

Charles Evans

Hi there,

Currently I have a piece of code that exports data to an Excel spreadsheet.
This is all good and well except for the fact that, even though the data
being exported is numeric, Excel prefixes the data with an apostrophe and
this makes the data completely useless when it comes to formulas within
Excel. After some searching I found that the way to overcome this problem
is to create the worksheet in Excel using CREATE TABLE syntax and to pass
through the data types for each of the columns. Wooohoooo....or so i
thought.

In the original application the spreadsheet is created from a template
stored in a resource which applies formatting to the column headers and
other parts of the spreadsheet. Now, because I am creating the sheet from
scratch, it doesn't have any formatting and I can't figure out how to format
a sheet without using Excel Automation. I was hoping some bright spark out
there might have some ideas of how i can get around this problem without the
need for Excel Automation (yuck!). I originally thought of using a macro
for when the sheet is created but then there is the security issue with end
users and macros.

Any suggestions or pointers would be greatly appreciated.

ciao,
-c.
 
¤ Hi there,
¤
¤ Currently I have a piece of code that exports data to an Excel spreadsheet.
¤ This is all good and well except for the fact that, even though the data
¤ being exported is numeric, Excel prefixes the data with an apostrophe and
¤ this makes the data completely useless when it comes to formulas within
¤ Excel. After some searching I found that the way to overcome this problem
¤ is to create the worksheet in Excel using CREATE TABLE syntax and to pass
¤ through the data types for each of the columns. Wooohoooo....or so i
¤ thought.
¤
¤ In the original application the spreadsheet is created from a template
¤ stored in a resource which applies formatting to the column headers and
¤ other parts of the spreadsheet. Now, because I am creating the sheet from
¤ scratch, it doesn't have any formatting and I can't figure out how to format
¤ a sheet without using Excel Automation. I was hoping some bright spark out
¤ there might have some ideas of how i can get around this problem without the
¤ need for Excel Automation (yuck!). I originally thought of using a macro
¤ for when the sheet is created but then there is the security issue with end
¤ users and macros.
¤
¤ Any suggestions or pointers would be greatly appreciated.

Wish I had a better answer for you but the formatting functionality is specific to Excel and is only
exposed through the automation interface.

If you can't use automation you may need to work from a template that has already been pre-formatted
via Excel. Of course if you're working with dynamic data structures this may not be a suitable
solution.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Okay....here's a tip for anyone who may ever need to do something like this
in the future. If you define a named range in Excel you can work with it
like a table through ADO. To change the types of each of the columns you
need to drop the existing table and create another with the same name. The
new table is created in exactly the same place so any formatting can
actually be done beforehand. Hooray!

My inital thoughts were that you could only create sheets as tables but this
new found knowledge solved my problem.
 
Back
Top