Automating Excel Data Import

[phbrady]

Hi,

I have a .net app which exports data from a database to text files. I could modify the app to write the data out directly to Excel but the first column is a 16-character string which ends in a number and for some reason excel has always changed the last digit to a zero. If I import the data into excel manually, it doesn't change the last digit, but I have to specify the field as a text field at the time of import.

Basically, I need to get the data from a SQL database to Excel with proper formatting. My question is:
1. How can I automate the process of kicking off the excel data import using dot net? I would also need to automate the process of formatting all the cells as text.

*** OR ***

2. Is there a way I can keep Excel from assuming the data in the 16-character column is numeric if I write the data to excel directly using dot net?

Thanks in advance!

 

[jebenson]

Te following code will set the NumberFormat for a specified range of cells to Text:

Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet

oExcel = New Excel.Application

oBook = oExcel.Workbooks.Add

oSheet = oBook.Worksheets.Add

oSheet.Range("A1:A2").NumberFormat = "@"

oBook.SaveAs("C:\Temp\Book1.xls")

oSheet = Nothing
oBook = Nothing
oExcel.Quit()
oExcel = Nothing

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day! Ye has a choice: talk like a pira

 

[phbrady]

Perfect!

Thanks,
P

 

[mxo]

how to select a field from a grid

Hi all
please help me i have got a grid with 2 colums and 3 row and i would like to get the field value and do the calculations on it.

help will be highly appreciates

 

[phbrady]

Are you referring to an ASP.Net DataGrid control?