Formatting excel files in Visual basic

[memarianm]

Hi,

I would like to ask the experts here about a problem I have encountered in writing my program in Visual Basic. The problem is that my program calculates and generates tables of numbers that stores them in excel files. These results are to be formatted nicely and printed out by the program like it is giving out a report of it's calculations. But the problem arises on formatting these excel file's different cells. Assigning the formats to cells one by one in the projects code takes a heck of along time. I have tried using templates for these excel files but the problem is that in templates specific cells have formats but results of my program vary in every run, so consecutive tables with different sizes are created in the excel file. I would highly appreciate your opinion on this problem on actually what would be the best approach in formatting these excel files. The tables all have the same format but different sizes. e.g. the header of each table is merged and shaded etc.

Thank you

P.S please let me know of any needed clarifications on the problem.

 

[Damon Ostrander]

Hi memarianm,

I don't know what your code looks like, but especially if it is based on code that was generated by the macro recorder, it is likely to be very inefficient. I could make some specific recommendations if you would post a bit of the code. But even without that, here are a few things you many not have thought of:

1. cells do not have to be selected in order to set their properties. For example, instead of:

Range("A1").Select
Selection.NumberFormat = "@"

use:

Range("A1").NumberFormat = "@"

or just

[A1].NumberFormat = "@"

2. You do not have to loop through a range of cells in order to set their properties individually if you want to set them all the same. For example, to set NumberFormat to text for all cells in the range A1:Z10000

[A1:Z10000].NumberFormat = "@"

and you will find this to be much faster than setting these using a loop.

This even works with disjoint ranges. Say for example that Table 1 is range A1:G25 and Table 2 is range J1:P25. You could set the number format for both tables using

[A1:G25,J1:P25].NumberFormat = "@"

3. Give names to ranges that have common formatting. For example, if one part of Table 1 (a) has different formatting from another part of Table 1 (b), then you could name the two parts of the table, for example, Table1a and Table1b and set their formats as

[Table1a].NumberFormat = "@"
[Table1b].NumberFormat = "General"

Going further, suppose Table 2 also has the same two types of formatting, and you name the two parts Table2a and Table2b. Then you could set them using

[Table1a,Table2a].NumberFormat = "@"
[Table1b,Table2b].NumberFormat = "General"

which is similar to the disjoint range methodology mentioned in 2.

I hope this helps.

 

[Joe Was]

Damon,
That was one of the best explanations on code passes I have seen. JSW

 

[memarianm]

Dear DAMON

First of all I would like to thank your close attention. I believe that my question was not clarified very well. What I meant by format was not actually Number formats but the appearance. For example the first table is stored in cells A1:I119 and has a format as follows: First row which contains 9 cells are all merged and gray with thick border around. second row font bold. other no format. Now as I previously mentioned there could be any number these tables that are stored one after another. for example second table is from A220:I449 (All tables are 219 * 9 cells). What I have tried to do in Visual basic was to copy the formats of the range for the first table which is preformatted to other tables' ranges in by pastespecial, but it keeps giving me errors that the class range fails. This error won't occur if the worksheet is visible but the program should do all this in the background and not show what is going on behind. Is pastespeical the right way? I also tried range1.Autofill destination:=range2 type:=3 but the error here would be that merged cells must be sized. I still think I'm on the wrong track. How would you format range of cells (As in their colors, merge, border) with Visual basic code in a time effective manner? Afterall what is required the program to do is to give a report consisting of tables that have a very basic format as I described.
This message was edited by memarianm on 2002-09-13 16:20

 

[Ivan F Moala]

On 2002-09-13 16:15, memarianm wrote:
Dear DAMON

First of all I would like to thank your close attention. I believe that my question was not clarified very well. What I meant by format was not actually Number formats but the appearance. For example the first table is stored in cells A1:I119 and has a format as follows: First row which contains 9 cells are all merged and gray with thick border around. second row font bold. other no format. Now as I previously mentioned there could be any number these tables that are stored one after another. for example second table is from A220:I449 (All tables are 219 * 9 cells). What I have tried to do in Visual basic was to copy the formats of the range for the first table which is preformatted to other tables' ranges in by pastespecial, but it keeps giving me errors that the class range fails. This error won't occur if the worksheet is visible but the program should do all this in the background and not show what is going on behind. Is pastespeical the right way? I also tried range1.Autofill destination:=range2 type:=3 but the error here would be that merged cells must be sized. I still think I'm on the wrong track. How would you format range of cells (As in their colors, merge, border) with Visual basic code in a time effective manner? Afterall what is required the program to do is to give a report consisting of tables that have a very basic format as I described.
This message was edited by memarianm on 2002-09-13 16:20

Hi memarianm
The basics of what Damon gave still applies.
It would still be usefull to see the code that errors out. As your range is static prgming this would be easy. You say you get an error when copying format over, since your data area is static this would be the best way... The error you have discribed suggests that you have not referenced the range correctly....post your code.

 

[memarianm]

'Consider the following code to apply formats from the first table to the 9 successive tables

Dim i As Integer
Dim j As Object

Set j = GetObject(App.Path & "test.xls")
With j.sheets(3)

.range(.cells(1, 1), .cells(219, 9)).Copy

For i = 1 To 9
.range(.cells(220 * i, 1), .cells(220 * i + 219, 9)).Pastespecial &HFFFFEFE6
Next i

End With

 

[memarianm]

Sorry it's "test.xls" not "\test.xls"

 

[Ivan F Moala]

On 2002-09-13 18:45, memarianm wrote:
'Consider the following code to apply formats from the first table to the 9 successive tables

Dim i As Integer
Dim j As Object

Set j = GetObject(App.Path & "test.xls")
With j.sheets(3)

.range(.cells(1, 1), .cells(219, 9)).Copy

For i = 1 To 9
.range(.cells(220 * i, 1), .cells(220 * i + 219, 9)).Pastespecial &HFFFFEFE6
Next i

End With

If you have set your code up to reference the Excel object library (early binding) then
you will be able to get the proper syntax for the PasteSpecial ie Pastespecial &HFFFFEFE6 is not valid for Excel.
Try setting up your reference to the excel olb file....if you have intellisence set then typing in .pastespecial should give you options for this command.

Depending on the Lib file here is the commands xl2000

Pastes a Range from the Clipboard into the specified range.

Syntax

expression.PasteSpecial(Paste, Operation, SkipBlanks, Transpose)

expression Required. An expression that returns a Range object.

Paste Optional Variant. The part of the range to be pasted. Can be one of the following XlPasteType constants: xlPasteAll, xlPasteFormulas, xlPasteValues, xlPasteFormats, xlPasteNotes, or xlPasteAllExceptBorders. The default value is xlPasteAll.

Operation Optional Variant. The paste operation. Can be one of the following XlPasteSpecialOperation constants: xlPasteSpecialOperationNone, xlPasteSpecialOperationAdd, xlPasteSpecialOperationSubtract, xlPasteSpecialOperationMultiply, or xlPasteSpecialOperationDivide. The default value is xlPasteSpecialOperationNone.

SkipBlanks Optional Variant. True to have blank cells in the range on the Clipboard not be pasted into the destination range. The default value is False.

Transpose Optional Variant. True to transpose rows and columns when the range is pasted.The default value is False.

 

[memarianm]

still can't get it to work, please rewrite the provided code with your approach, so it will open such file (test.xls) and format it. Suppose Microsoft Excel Library 10 is added in the refrences.

 

[Ivan F Moala]

<pre/>
Sub Tester()
Dim i As Integer
Dim j As Object

Set j = GetObject(App.Path & "test.xls")

With j.Sheets(3)
.Range(.Cells(1, 1), .Cells(219, 9)).Copy
For i = 1 To 9
.Range(.Cells(220 * i, 1), .Cells(220 * i + 219, 9)).PasteSpecial Paste:=xlFormats
Next i
End With

End Sub
</pre>