Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 38
Default Set Cell format using VB.NET

Hi,

I want to open a exsiting CSV file and set the Data Format of column A (Date
and time) to Date (m/d/yyyy) and column width to certain number. I can open
the file but how to set the format? Thanks very much.

My code:

Dim excel As Microsoft.Office.Interop.Excel.Application
Dim wb As Microsoft.Office.Interop.Excel.Workbook
excel = New Microsoft.Office.Interop.Excel.Application
wb = excel.Workbooks.Open(FileName)
excel.Visible = True
wb.Activate()

  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Set Cell format using VB.NET

[php]
  1. Declare the necessary variables and open the CSV file:
    Formula:
    Dim excel As Microsoft.Office.Interop.Excel.Application
    Dim wb 
    As Microsoft.Office.Interop.Excel.Workbook
    Dim ws 
    As Microsoft.Office.Interop.Excel.Worksheet
    Dim range 
    As Microsoft.Office.Interop.Excel.Range

    excel 
    = New Microsoft.Office.Interop.Excel.Application
    wb 
    excel.Workbooks.Open(FileName)
    ws wb.ActiveSheet
    range 
    ws.Range("A:A"
  2. Set the column width:
    Formula:
    range.ColumnWidth 15 
  3. Set the date format:
    Formula:
    range.NumberFormat "m/d/yyyy" 
  4. Save the changes to the workbook, close it, and quit Excel:
    Formula:
    wb.Save()
    wb.Close()
    excel.Quit() 

Note that this code assumes that the CSV file has been opened in Excel and saved as an Excel workbook (.xlsx). If you need to open and format a true CSV file, you may need to use a different approach.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,365
Default Set Cell format using VB.NET

I believe this will do the trick for you - just add these lines of code to
what you already have displayed here. You'll need to change the sheet name
and column affected based on what's actually in your workbook, along with the
proper width value in the first line.

wb.Worksheets("Sheet1").Columns("B:B").ColumnWidth = 15
wb.Worksheets("Sheet1").Columns("B:B").NumberForma t = "m/d/yyyy;@"


"Hugh" wrote:

Hi,

I want to open a exsiting CSV file and set the Data Format of column A (Date
and time) to Date (m/d/yyyy) and column width to certain number. I can open
the file but how to set the format? Thanks very much.

My code:

Dim excel As Microsoft.Office.Interop.Excel.Application
Dim wb As Microsoft.Office.Interop.Excel.Workbook
excel = New Microsoft.Office.Interop.Excel.Application
wb = excel.Workbooks.Open(FileName)
excel.Visible = True
wb.Activate()

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 38
Default Set Cell format using VB.NET

Hi JLanthan,

Thank you very much for your help. Your code works fine. I was stuck at
syntex of numeric format. It looks you are the expert. Can I ask one more
trick? The column width can not be saved and went back to default width
after the file reopen. Any trick? Thank you again.

"JLatham" wrote:

I believe this will do the trick for you - just add these lines of code to
what you already have displayed here. You'll need to change the sheet name
and column affected based on what's actually in your workbook, along with the
proper width value in the first line.

wb.Worksheets("Sheet1").Columns("B:B").ColumnWidth = 15
wb.Worksheets("Sheet1").Columns("B:B").NumberForma t = "m/d/yyyy;@"


"Hugh" wrote:

Hi,

I want to open a exsiting CSV file and set the Data Format of column A (Date
and time) to Date (m/d/yyyy) and column width to certain number. I can open
the file but how to set the format? Thanks very much.

My code:

Dim excel As Microsoft.Office.Interop.Excel.Application
Dim wb As Microsoft.Office.Interop.Excel.Workbook
excel = New Microsoft.Office.Interop.Excel.Application
wb = excel.Workbooks.Open(FileName)
excel.Visible = True
wb.Activate()

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,365
Default Set Cell format using VB.NET

Arrrgh... serious FatFinger exercise on the keyboard at my end if you see a
partial response!

I'll try again. The reason it's not saving the width, I think, is because
it's a .CSV file and not a .XLS file. In reality a .CSV file is an ASCII
text file and it has no format storing ability. You'd need to SAVE AS and
choose to save it as an Excel Workbook to preserve the formatting.

The easiest way to accomplish this could be by adding this line of code
after the formatting is done:
wb.SaveAs Filename:=wb.FullName & ".xls", FileFormat:=xlNormal

if it balks at xlNormal, try using a value of -4143, as:
wb.SaveAs Filename:=wb.FullName & ".xls", FileFormat:=-4143

I say this is easiest instead of stripping off the .csv and replacing it
with .xls. The filename you'll end up with would be something like
myFile.csv.xls if it started out as myFile.csv
The .FullName property returns the entire drive/path/filename.xls string, so
the copy of the file would be saved to the same location that the .csv file
is at.

That may be undesirable, and you may want to save it in a location you
designate. You could deal with that by creating a String variable and
assigning it to the path and name you want. Something like this:

Dim newLocation As String
newLocation = "X:\MySavedCSVFiles\" & wb.Name & ".xls"
so if your file started out as myFile.csv it would end up saved in
X:\MySavedCSVFiles as a file named myFile.csv.xls
the .Name property just returns the name of the workbook without drive/path
info.

Hope that helps.


"Hugh" wrote:

Hi JLanthan,

Thank you very much for your help. Your code works fine. I was stuck at
syntex of numeric format. It looks you are the expert. Can I ask one more
trick? The column width can not be saved and went back to default width
after the file reopen. Any trick? Thank you again.

"JLatham" wrote:

I believe this will do the trick for you - just add these lines of code to
what you already have displayed here. You'll need to change the sheet name
and column affected based on what's actually in your workbook, along with the
proper width value in the first line.

wb.Worksheets("Sheet1").Columns("B:B").ColumnWidth = 15
wb.Worksheets("Sheet1").Columns("B:B").NumberForma t = "m/d/yyyy;@"


"Hugh" wrote:

Hi,

I want to open a exsiting CSV file and set the Data Format of column A (Date
and time) to Date (m/d/yyyy) and column width to certain number. I can open
the file but how to set the format? Thanks very much.

My code:

Dim excel As Microsoft.Office.Interop.Excel.Application
Dim wb As Microsoft.Office.Interop.Excel.Workbook
excel = New Microsoft.Office.Interop.Excel.Application
wb = excel.Workbooks.Open(FileName)
excel.Visible = True
wb.Activate()



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default Set Cell format using VB.NET

In what format did you save the file? If you saved as CSV, remember that CSV
is just comma separated text, so has no formatting data. If you want to
save things like column width, save as xls.
--
David Biddulph

"Hugh" wrote in message
...
Hi JLanthan,

Thank you very much for your help. Your code works fine. I was stuck at
syntex of numeric format. It looks you are the expert. Can I ask one
more
trick? The column width can not be saved and went back to default width
after the file reopen. Any trick? Thank you again.

"JLatham" wrote:

I believe this will do the trick for you - just add these lines of code
to
what you already have displayed here. You'll need to change the sheet
name
and column affected based on what's actually in your workbook, along with
the
proper width value in the first line.

wb.Worksheets("Sheet1").Columns("B:B").ColumnWidth = 15
wb.Worksheets("Sheet1").Columns("B:B").NumberForma t = "m/d/yyyy;@"


"Hugh" wrote:

Hi,

I want to open a exsiting CSV file and set the Data Format of column A
(Date
and time) to Date (m/d/yyyy) and column width to certain number. I can
open
the file but how to set the format? Thanks very much.

My code:

Dim excel As Microsoft.Office.Interop.Excel.Application
Dim wb As Microsoft.Office.Interop.Excel.Workbook
excel = New Microsoft.Office.Interop.Excel.Application
wb = excel.Workbooks.Open(FileName)
excel.Visible = True
wb.Activate()



  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 38
Default Set Cell format using VB.NET

Thanks again, Latha. I could see the width was changed by the code and could
not be saved. I suspected the reason was csv file but was not sure. Thanks
for your great help.

"JLatham" wrote:

Arrrgh... serious FatFinger exercise on the keyboard at my end if you see a
partial response!

I'll try again. The reason it's not saving the width, I think, is because
it's a .CSV file and not a .XLS file. In reality a .CSV file is an ASCII
text file and it has no format storing ability. You'd need to SAVE AS and
choose to save it as an Excel Workbook to preserve the formatting.

The easiest way to accomplish this could be by adding this line of code
after the formatting is done:
wb.SaveAs Filename:=wb.FullName & ".xls", FileFormat:=xlNormal

if it balks at xlNormal, try using a value of -4143, as:
wb.SaveAs Filename:=wb.FullName & ".xls", FileFormat:=-4143

I say this is easiest instead of stripping off the .csv and replacing it
with .xls. The filename you'll end up with would be something like
myFile.csv.xls if it started out as myFile.csv
The .FullName property returns the entire drive/path/filename.xls string, so
the copy of the file would be saved to the same location that the .csv file
is at.

That may be undesirable, and you may want to save it in a location you
designate. You could deal with that by creating a String variable and
assigning it to the path and name you want. Something like this:

Dim newLocation As String
newLocation = "X:\MySavedCSVFiles\" & wb.Name & ".xls"
so if your file started out as myFile.csv it would end up saved in
X:\MySavedCSVFiles as a file named myFile.csv.xls
the .Name property just returns the name of the workbook without drive/path
info.

Hope that helps.


"Hugh" wrote:

Hi JLanthan,

Thank you very much for your help. Your code works fine. I was stuck at
syntex of numeric format. It looks you are the expert. Can I ask one more
trick? The column width can not be saved and went back to default width
after the file reopen. Any trick? Thank you again.

"JLatham" wrote:

I believe this will do the trick for you - just add these lines of code to
what you already have displayed here. You'll need to change the sheet name
and column affected based on what's actually in your workbook, along with the
proper width value in the first line.

wb.Worksheets("Sheet1").Columns("B:B").ColumnWidth = 15
wb.Worksheets("Sheet1").Columns("B:B").NumberForma t = "m/d/yyyy;@"


"Hugh" wrote:

Hi,

I want to open a exsiting CSV file and set the Data Format of column A (Date
and time) to Date (m/d/yyyy) and column width to certain number. I can open
the file but how to set the format? Thanks very much.

My code:

Dim excel As Microsoft.Office.Interop.Excel.Application
Dim wb As Microsoft.Office.Interop.Excel.Workbook
excel = New Microsoft.Office.Interop.Excel.Application
wb = excel.Workbooks.Open(FileName)
excel.Visible = True
wb.Activate()

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 38
Default Set Cell format using VB.NET

Hi Daved,

I guess that I have to sacrifice the column width stuff because I want to
keep it in csv format.

Thanks again.

"David Biddulph" wrote:

In what format did you save the file? If you saved as CSV, remember that CSV
is just comma separated text, so has no formatting data. If you want to
save things like column width, save as xls.
--
David Biddulph

"Hugh" wrote in message
...
Hi JLanthan,

Thank you very much for your help. Your code works fine. I was stuck at
syntex of numeric format. It looks you are the expert. Can I ask one
more
trick? The column width can not be saved and went back to default width
after the file reopen. Any trick? Thank you again.

"JLatham" wrote:

I believe this will do the trick for you - just add these lines of code
to
what you already have displayed here. You'll need to change the sheet
name
and column affected based on what's actually in your workbook, along with
the
proper width value in the first line.

wb.Worksheets("Sheet1").Columns("B:B").ColumnWidth = 15
wb.Worksheets("Sheet1").Columns("B:B").NumberForma t = "m/d/yyyy;@"


"Hugh" wrote:

Hi,

I want to open a exsiting CSV file and set the Data Format of column A
(Date
and time) to Date (m/d/yyyy) and column width to certain number. I can
open
the file but how to set the format? Thanks very much.

My code:

Dim excel As Microsoft.Office.Interop.Excel.Application
Dim wb As Microsoft.Office.Interop.Excel.Workbook
excel = New Microsoft.Office.Interop.Excel.Application
wb = excel.Workbooks.Open(FileName)
excel.Visible = True
wb.Activate()




  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,231
Default Set Cell format using VB.NET

"David Biddulph" <groups [at] biddulph.org.uk wrote...
In what format did you save the file? If you saved as CSV, remember
that CSV is just comma separated text, so has no formatting data.
If you want to save things like column width, save as xls.

....

Picky: the OP could save the file in SLK file format, which does save
formatting information but is also plain text though structured.
  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default How about adding spaces as filler to make fields evenlength

If you need 15 char long values in CSV, you can add SPACEs as FILLER to your EXCEL Cell Value, then save it as CSV.

Unfortunately you have to loop through each row to make the changes. High level logic is like :-

For Each MyRow as ExcelRow in MySheet.Rows
MyRow(Col).Value &= " " 'u put 15 spaces
MyRow(Col).Value = MyRow(Col).Value.substring(0, 15)
Next

The above code will give you LEFT JUSTIFIED 15 char long CSV Field. If you want RIGHT JUSTIFIED Text in CSV, then CODE will be like (for the filler only)

MyRow(Col).Value = MyRow(Col).Value.Reverse()
MyRow(Col).Value &= " "
MyRow(Col).Value = MyRow(Col).Value.substring(0, 15)
MyRow(Col).Value = MyRow(Col).Value.Reverse()

Looping and string operation will take toll on performance - depending on how many rows you have and computer's power. Thnaks you.


  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default format excel worksheet in vb.net

if you need more help on format worksheet through vb.net , just refer this.

http://vb.net-informations.com/excel...age_format.htm

bolton


  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Change Column data format as text of excel file in c#

Hi

I want to change Column data format as text of excel file in c#



Hug wrote:

Set Cell format using VB.NET
14-Feb-08

Hi,

I want to open a exsiting CSV file and set the Data Format of column A (Date
and time) to Date (m/d/yyyy) and column width to certain number. I can open
the file but how to set the format? Thanks very much.

My code:

Dim excel As Microsoft.Office.Interop.Excel.Application
Dim wb As Microsoft.Office.Interop.Excel.Workbook
excel = New Microsoft.Office.Interop.Excel.Application
wb = excel.Workbooks.Open(FileName)
excel.Visible = True
wb.Activate()

Previous Posts In This Thread:

On Thursday, February 14, 2008 12:07 PM
Hug wrote:

Set Cell format using VB.NET
Hi,

I want to open a exsiting CSV file and set the Data Format of column A (Date
and time) to Date (m/d/yyyy) and column width to certain number. I can open
the file but how to set the format? Thanks very much.

My code:

Dim excel As Microsoft.Office.Interop.Excel.Application
Dim wb As Microsoft.Office.Interop.Excel.Workbook
excel = New Microsoft.Office.Interop.Excel.Application
wb = excel.Workbooks.Open(FileName)
excel.Visible = True
wb.Activate()

On Thursday, February 14, 2008 12:58 PM
HelpFrom wrote:

I believe this will do the trick for you - just add these lines of code to
I believe this will do the trick for you - just add these lines of code to
what you already have displayed here. You'll need to change the sheet name
and column affected based on what's actually in your workbook, along with the
proper width value in the first line.

wb.Worksheets("Sheet1").Columns("B:B").ColumnWidth = 15
wb.Worksheets("Sheet1").Columns("B:B").NumberForma t = "m/d/yyyy;@"


"Hugh" wrote:

On Thursday, February 14, 2008 2:46 PM
Hug wrote:

Hi JLanthan,Thank you very much for your help. Your code works fine.
Hi JLanthan,

Thank you very much for your help. Your code works fine. I was stuck at
syntex of numeric format. It looks you are the expert. Can I ask one more
trick? The column width can not be saved and went back to default width
after the file reopen. Any trick? Thank you again.

"JLatham" wrote:

On Thursday, February 14, 2008 3:17 PM
HelpFrom wrote:

Arrrgh...
Arrrgh... serious FatFinger exercise on the keyboard at my end if you see a
partial response!

I'll try again. The reason it's not saving the width, I think, is because
it's a .CSV file and not a .XLS file. In reality a .CSV file is an ASCII
text file and it has no format storing ability. You'd need to SAVE AS and
choose to save it as an Excel Workbook to preserve the formatting.

The easiest way to accomplish this could be by adding this line of code
after the formatting is done:
wb.SaveAs Filename:=wb.FullName & ".xls", FileFormat:=xlNormal

if it balks at xlNormal, try using a value of -4143, as:
wb.SaveAs Filename:=wb.FullName & ".xls", FileFormat:=-4143

I say this is easiest instead of stripping off the .csv and replacing it
with .xls. The filename you'll end up with would be something like
myFile.csv.xls if it started out as myFile.csv
The .FullName property returns the entire drive/path/filename.xls string, so
the copy of the file would be saved to the same location that the .csv file
is at.

That may be undesirable, and you may want to save it in a location you
designate. You could deal with that by creating a String variable and
assigning it to the path and name you want. Something like this:

Dim newLocation As String
newLocation = "X:\MySavedCSVFiles\" & wb.Name & ".xls"
so if your file started out as myFile.csv it would end up saved in
X:\MySavedCSVFiles as a file named myFile.csv.xls
the .Name property just returns the name of the workbook without drive/path
info.

Hope that helps.


"Hugh" wrote:

On Thursday, February 14, 2008 3:27 PM
David Biddulph wrote:

In what format did you save the file?
In what format did you save the file? If you saved as CSV, remember that CSV
is just comma separated text, so has no formatting data. If you want to
save things like column width, save as xls.
--
David Biddulph

"Hugh" wrote in message
...

On Thursday, February 14, 2008 4:09 PM
Hug wrote:

Thanks again, Latha.
Thanks again, Latha. I could see the width was changed by the code and could
not be saved. I suspected the reason was csv file but was not sure. Thanks
for your great help.

"JLatham" wrote:

On Thursday, February 14, 2008 4:10 PM
Hug wrote:

Hi Daved,I guess that I have to sacrifice the column width stuff because I
Hi Daved,

I guess that I have to sacrifice the column width stuff because I want to
keep it in csv format.

Thanks again.

"David Biddulph" wrote:

On Friday, February 15, 2008 3:35 AM
Harlan Grove wrote:

"David Biddulph" <groups [at] biddulph.org.uk wrote......
"David Biddulph" <groups [at] biddulph.org.uk wrote...
....

Picky: the OP could save the file in SLK file format, which does save
formatting information but is also plain text though structured.

On Wednesday, October 01, 2008 12:27 PM
Mehdi Anis wrote:

How about adding spaces as filler to make fields even length
If you need 15 char long values in CSV, you can add SPACEs as FILLER to your EXCEL Cell Value, then save it as CSV.

Unfortunately you have to loop through each row to make the changes. High level logic is like :-

For Each MyRow as ExcelRow in MySheet.Rows
MyRow(Col).Value &= " " 'u put 15 spaces
MyRow(Col).Value = MyRow(Col).Value.substring(0, 15)
Next

The above code will give you LEFT JUSTIFIED 15 char long CSV Field. If you want RIGHT JUSTIFIED Text in CSV, then CODE will be like (for the filler only)

MyRow(Col).Value = MyRow(Col).Value.Reverse()
MyRow(Col).Value &= " "
MyRow(Col).Value = MyRow(Col).Value.substring(0, 15)
MyRow(Col).Value = MyRow(Col).Value.Reverse()

Looping and string operation will take toll on performance - depending on how many rows you have and computer's power. Thnaks you.

On Thursday, October 09, 2008 8:17 AM
bolton ss wrote:

format excel worksheet in vb.net
if you need more help on format worksheet through vb.net , just refer this.

http://vb.net-informations.com/excel...age_format.htm

bolton


Submitted via EggHeadCafe - Software Developer Portal of Choice
SharePoint - Managing Unused or Archive sites automatically
http://www.eggheadcafe.com/tutorials...aging-unu.aspx
  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Set Cell format using VB.NET

On Thursday, February 14, 2008 8:58:03 PM UTC+3, JLatham wrote:
I believe this will do the trick for you - just add these lines of code to
what you already have displayed here. You'll need to change the sheet name
and column affected based on what's actually in your workbook, along with the
proper width value in the first line.

wb.Worksheets("Sheet1").Columns("B:B").ColumnWidth = 15
wb.Worksheets("Sheet1").Columns("B:B").NumberForma t = "m/d/yyyy;@"


"Hugh" wrote:

Hi,

I want to open a exsiting CSV file and set the Data Format of column A (Date
and time) to Date (m/d/yyyy) and column width to certain number. I can open
the file but how to set the format? Thanks very much.

My code:

Dim excel As Microsoft.Office.Interop.Excel.Application
Dim wb As Microsoft.Office.Interop.Excel.Workbook
excel = New Microsoft.Office.Interop.Excel.Application
wb = excel.Workbooks.Open(FileName)
excel.Visible = True
wb.Activate()


Thank You ....It works!!!!!!!!
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Lock Cell Format - Allow copy and paste of data without format change Chris12InKC Excel Worksheet Functions 2 May 9th 23 07:42 PM
Can cell format come from and change with reference cell format jclouse Excel Discussion (Misc queries) 1 November 29th 06 04:20 AM
how do i format a cell based on format of a range of cells? Chris Hardick Excel Discussion (Misc queries) 2 April 3rd 06 08:54 AM
How do I copy data in single cell format to a merged cell format Paul Excel Discussion (Misc queries) 1 June 27th 05 11:00 AM


All times are GMT +1. The time now is 11:52 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"