2 for the price of 1

[dazzerj]

Hello Clever Ones,

With the help of this forum I managed to create my first macro within excel (see below). I would like to enhance the export activity by selecting an email recipient with the generated xml file as an attachment. Is this possible within Excel, to invoke an email message?

My 2 for 1 is below the truncated code below (the second question may be simpler to answer, it's not a coding query just an excel behaviour).

I'm sure there is probably a more efficient way of achieving what I have here, but as a complete novice if you have suggestions for streamlining please give an explanation of any functions introduced.

Here is a description of what it is doing.

On sheet 1 I have a number of check box items. When each is checked the contents of 4 associated cells are written into sheet 2. This macro outputs the content of sheet 2 (omitting blank rows) into an xml file. Sweet.


Sub OutputXML()
Open "C:\Documents and Settings\Darren.Jones\Desktop\CusPk-test.xml" For Output As #1
Print #1, Sheets("Sheet2").Cells(1, 1) & Chr(13) & Sheets("Sheet2").Cells(2, 1) & Chr(13) & Sheets("Sheet2").Cells(3, 1) & Chr(13) & Sheets("Sheet2").Cells(4, 1) & Chr(13) & Sheets("Sheet2").Cells(5, 1) & Chr(13) & Sheets("Sheet2").Cells(6, 1) & Chr(13) & Sheets("Sheet2").Cells(7, 1) & Chr(13) & Sheets("Sheet2").Cells(8, 1) & Chr(13) & Sheets("Sheet2").Cells(9, 1) & Chr(13) & Sheets("Sheet2").Cells(10, 1) & Chr(13) & Sheets("Sheet2").Cells(11, 1) & Chr(13) & Sheets("Sheet2").Cells(12, 1) & Chr(13) & Sheets("Sheet2").Cells(13, 1) & Chr(13) & Sheets("Sheet2").Cells(14, 1) & Chr(13) & Sheets("Sheet2").Cells(15, 1) & Chr(13) & Sheets("Sheet2").Cells(16, 1) & Chr(13) & Sheets("Sheet2").Cells(17, 1) & Chr(13) & Sheets("Sheet2").Cells(18, 1) & Chr(13) & Sheets("Sheet2").Cells(19, 1)
If Len(Sheets("Sheet2").Cells(20, 1)) > 0 Then Print #1, Sheets("Sheet2").Cells(20, 1)
If Len(Sheets("Sheet2").Cells(21, 1)) > 0 Then Print #1, Sheets("Sheet2").Cells(21, 1)
If Len(Sheets("Sheet2").Cells(22, 1)) > 0 Then Print #1, Sheets("Sheet2").Cells(22, 1)
If Len(Sheets("Sheet2").Cells(23, 1)) > 0 Then Print #1, Sheets("Sheet2").Cells(23, 1)
If Len(Sheets("Sheet2").Cells(24, 1)) > 0 Then Print #1, Sheets("Sheet2").Cells(24, 1)
If Len(Sheets("Sheet2").Cells(25, 1)) > 0 Then Print #1, Sheets("Sheet2").Cells(25, 1)
If Len(Sheets("Sheet2").Cells(26, 1)) > 0 Then Print #1,
...

If Len(Sheets("Sheet2").Cells(90, 1)) > 0 Then Print #1, Sheets("Sheet2").Cells(90, 1)
If Len(Sheets("Sheet2").Cells(91, 1)) > 0 Then Print #1, Sheets("Sheet2").Cells(91, 1)
If Len(Sheets("Sheet2").Cells(92, 1)) > 0 Then Print #1, Sheets("Sheet2").Cells(92, 1)
If Len(Sheets("Sheet2").Cells(93, 1)) > 0 Then Print #1, Sheets("Sheet2").Cells(93, 1)
Close
End Sub


Here's the second question.

In the spreadsheet sheet 1 that this macro resides in, I have 4 output lines associated with check box items, of which here are currently 18. Totalling 72 lines of output. In sheet 2 I have used the IF function to fill in the content of a cell e.g. =IF(Sheet1!A5,Sheet1!D3,""). This function works perfectly for all rows in Sheet 2 upto Row 69 at which point it stops performing and the content of the cell always shows the formula and not the result. If I move the formula to the next column it starts to return the correct result. No matter what I try though it will not give the result any longer in any row beyond 69 in column 1!

Is there a limit to how many formulas can exist in a single column? I am perplexed.

All assistance greatly appreciated.