Output to Excel

[Broodmdh]

I am trying to send groups of strings to Excel, and I am having a tough time with the formatting. For some reason, when I attempt to assign color to any of the cells, the user gets prompted to save changes to the book. Why does this happen? How do I make changes to entire columns (horizontal alignment etc)? How do I set the 'category' for individual cells?

Thanks for the help.

[HanneSThEGreaT]

To prevent the Message Asking if you want to save to display, Save your worksheet first, then close
XlBook.save
XlBook.close(false)

Just remember as well to set all the object references to nothing!!

For example:xlsheet=nothing
XlBook=nothing
Xl.quit
Xl=nothing
What do you mean by Category ¿

[RobDog888]

If you make any changes to your workbook such as formatting, you will be prompted to save your workbook.

Category, do you mean a Named Range?

[Broodmdh]

By Category, I was referring to the type of field that was assigned to the cell (general, number, currency etc). I was thinking that this would be an easy way to format individual cells.

[HanneSThEGreaT]

For the 'Category' have a look at this:

http://www.codeguru.com/forum/showpost.php?p=947334&postcount=2

Note the NumberFormat property! :wave:

[Broodmdh]

Do all formatting changes force you to save the workbook? Even changes made specfically by referencing the sheet, and not the book? When I do make formatting changes, and then save the book when prompted to, none of the data appears in the book. When I remove the formatting changes, everything works properly. Do I somehow have to link the sheet to the book, aside from:

xlBook = xlApp.Workbooks.Add
xlSheet = xlBook.Worksheets.Item("Sheet1")


Is the code to work with excel in VB .NET the same as regular vba code?

[HanneSThEGreaT]

Do all formatting changes force you to save the workbook? Even changes made specfically by referencing the sheet, and not the book?
Yes, becuase the Sheet is part of the book.

When I do make formatting changes, and then save the book when prompted to, none of the data appears in the book. When I remove the formatting changes, everything works properly.
Can you include your full code ¿

Do I somehow have to link the sheet to the book, aside from:

xlBook = xlApp.Workbooks.Add
xlSheet = xlBook.Worksheets.Item("Sheet1")
No, as I've said the Sheet is already part of the workbook

[Gizmo001]

I have been working with Excel outputs from .Net for last 2-3 years and never had the problem of being prompted to save the workbook when I am changing formats within a sheet. My suggestion is first check if this happens when you are working in Excel directly. If it does, it is some default setting within the Excel loaded on your PC that is causing it. If it does not, I have no clue. Good luck.

[Gizmo001]

Originally Posted by Broodmdh
When I do make formatting changes, and then save the book when prompted to, none of the data appears in the book. When I remove the formatting changes, everything works properly.

Add XlApp.visible=true so that you can view the sheet while execution is on.

Remove the XlBook.save or any other save statements. When your Excel formatting is complete, save it directly as the book is open and visible to you.

This way you can track what is happening. Good luck.